问题描述:

In the other question I learned how to properly bind parameters to prepared SQL statements. However, I found out that sqlite3_step() returns with SQLITE_DONE if I don't bind all parameters. I would rather consider this to be an error. Furthermore, I do not know if this is the source of error in my code, hence my questions aim at understanding how to debug the behavior of SQLite during the binding process:

  1. How can I determine which parameters are not set?
  2. Can I somehow see the statement with bound parameters inserted, for debugging purposes?
  3. Why does SQLite not respond with SQLITE_NOT_ALL_PARAMETERS_SET or SQLITE_ERROR if not all parameters are set?

网友答案:

If you add an 'explain' before your query, itll 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 reference, tells us that it checks if your var ?2 is an int, and if not, it jumps to instruction 9: Close, followed by a halt.

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

网友答案:
  1. Any parameters which you did not explicit set with sqlite3_bind_*() have the default value NULL.

    This implies that all parameters are always set.

  2. There is no function to read parameter values; it is assumed that you know the values because you've set them yourself.

网友答案:

If binding succeeds but sqlite3_step() does not, check that the text or blob you bind is not yet destroyed. The alternative is to use SQLITE_TRANSIENT as last parameter in sqlite3_bind_text(). SQLITE_TRANSIENT makes a copy of what is bound.

相关阅读:
Top