Clever Geek Handbook

How to debug binding parameters in SQLite3?

In another question, I learned how to properly bind parameters to prepared SQL statements. However, I found that sqlite3_step () returns with SQLITE_DONE if I don't bind all the parameters. I would rather regard this as a bug. Also, I don't know if this is the source of bugs in my code, so my questions are about understanding how to debug SQLite behavior during the binding process:

  • How do I determine which parameters are not set?
  • Is there any way to see an instruction with bound parameters for debugging purposes?
  • Why is SQLite not responding with SQLITE_NOT_ALL_PARAMETERS_SET or SQLITE_ERROR if not all parameters are set?
0


source to share


3 answers


If you add an "explain" before your query, it will output the query plan. In your case:

addr    opcode      p1  p2  p3      p4          p5
0       Init        0   11  0                   00  NULL
1       OpenRead    0   2   0       2           00  NULL
2       Variable    2   1   0       ?2          00  NULL
3       MustBeInt   1   9   0                   00  NULL
4       NotExists   0   9   1                   00  NULL
5       Column      0   1   2                   00  NULL
6       Ne          3   9   2       (BINARY)    52  NULL
7       Copy        1   4   0                   00  NULL
8       ResultRow   4   1   0                   00  NULL
9       Close       0   0   0                   00  NULL
10      Halt        0   0   0                   00  NULL
11      Transaction 0   0   7       0           01  NULL
12      TableLock   0   2   0       Names       00  NULL
13      Variable    1   3   0       ?1          00  NULL
14      Goto        0   1   0                   00  NULL


Using https://www.sqlite.org/opcode.html as a reference, we'll let you know that it checks if your var is? 2 int, and if not, it goes to instruction 9: Close and then stop.

ps: I used DB Browser for SQLite to grab your execution plan, the next step (for you) is to find out if you can query this? 2 should be an int or how should it handle the failure case.

+1


source


  • Any parameters that you don't explicitly specify with sqlite3_bind_*()

    have a default of NULL.

    This means that all parameters are always set.

  • There is no function for reading parameter values; it is assumed that you know the values ​​because you set them yourself.



+1


source


If the binding succeeds, but sqlite3_step () does not, check that the text or blob you are binding has not yet been destroyed. An alternative is to use SQLITE_TRANSIENT as the last parameter to sqlite3_bind_text (). SQLITE_TRANSIENT makes a copy of what's related.

0


source






More articles:


All Articles