Is there a way to see the result of applying arguments to a query in sqlite?

Question

I'm using sqlite3 in Go and for debugging purposes would like to see the result of applying arguments to the query with the replacements, i.e., with ? replaced by the actual argument. Is this possible?

Currently, I have code like the following and the FTS5 query printed looks alright:

SELECT ownerid FROM __ftsindex WHERE ownertable=? AND 
__ftsindex MATCH ? AND ( __ftsindex MATCH ? ) LIMIT ?;

It does not return an error, but it also doesn't find what it should find, and I'm at a loss how to further debug this:

for i := range trails {
    if i == 0 {
        s += `( __ftsindex MATCH ?`
    } else {
        s += ` OR __ftsindex MATCH ?`
    }
}
s += " )"
query = fmt.Sprintf(`SELECT ownerid FROM __ftsindex WHERE ownertable=? AND __ftsindex MATCH ? AND %s LIMIT ?;`, s)
fmt.Println(query)
args := make([]interface{}, len(trails)+3)
args[0] = cat.Table()
args[1] = searchTerm
for i := range trails {
    args[i+2] = trails[i]
}
args[len(args)-1] = limit
rows, err := model.DB.Base().Query(query, args...)

Probably not, because the arguments don't actually get replaced. They are passed, along with the name of the prepared query, to the database engine. — Jonathan Hall, Jul 05 '19 at 10:32
Oh, bummer (but it makes sense). I've got an idea, though. I'll construct the query with fmt.Sprintf for debugging and then use proper ? arguments again once I've found the error. — Eric '3ToedSloth', Jul 05 '19 at 10:42
[This](https://www.sqlite.org/debugging.html) might be useful, especially if sqlite is compiled with debugging enabled. — Jeff Holt, Jul 05 '19 at 15:47
Thanks! I've found the problem, it was actually just the tokenization. I expected queries like "/home/mnt/*" to work but "/" is not a token character by default. Putting everything in quotes worked. — Eric '3ToedSloth', Jul 05 '19 at 16:47

score 1 · Accepted Answer · answered Jul 05 '19 at 15:41

If you are not getting the results from an SQL query that you expect, there may be rogue bytes in your input arguments.

I would suggest logging each individual argument using %+q:

for i, arg := range sqlArgs {
    if _, ok := arg.(string); ok {
        log.Printf("arg %d) %+q\n", i+1, arg) // catches any 'invisible' bytes or visually similar characters
    } else {
        log.Printf("arg %d) `%v`\n", i+1, arg)
    }
}

For example: https://play.golang.org/p/mDsnIjMAVwP

P.S. See this excellent go blog post on how to render obscure string encodings and byte-slices.

Is there a way to see the result of applying arguments to a query in sqlite?

1 Answers1