- Some other process changes the database schema
- Your connection executes ATTACH, DETACH, or VACUUM
- Your connection calls sqlite3_set_authorizer()
- (There might be other reasons that do not come immediately to mind...)
If the cause of the statements expiring is some other process, then our database connection might not know right away that the statements have expired. It will not find out until you next try to access the database, the schema change is noticed, and SQLITE_SCHEMA is returned by sqlite3_reset() or sqlite3_finalize().
-
Note: beginning with SQLite version 3.3.9, there is a new API
for preparing statements:
sqlite3_prepare_v2()
(and its companion function
sqlite3_prepare16_v2())
should be used in place of the older sqlite3_prepare() and
sqlite3_prepare16() functions. When the newer _v2 functions are
used, arrangements are made to automatically detect SQLITE_SCHEMA
errors, reprepare the statement, and rerun the sqlite3_step().
If you use the new _v2() functions instead of the legacy functions,
you really should not have to worry about anything on this wiki
page.
So you must always be prepared to deal with SQLITE_SCHEMA errors. sqlite3_expired() will tell you about known expirations but not about expirations that have not yet been discovered.
The way the TCL language bindings deal with this is instructive and worth reviewing. In the TCL bindings, the original SQL text of each statement is held with the sqlite3_stmt* pointer. If sqlite3_step() returns SQLITE_ERROR and a subsequent sqlite3_reset() returns SQLITE_SCHEMA, then the statement is reprepared and the sqlite3_step() is automatically retried. Thus a TCL programmer need not have any knowledge of SQLITE_SCHEMA - such errors are handled transparently and automatically by the bindings. The TCL bindings also keep a cache of recently used SQL statements. So a TCL programmer need not have any knowledge of sqlite3_prepare() either. There is no direct interface to sqlite3_prepare() from within TCL. To use SQLite from TCL, you simply give it SQL text to be evaluated. The bindings automatically detect if the SQL matches a statement in cache and reuse the existing sqlite3_stmt if it does.
Bottom line: TCL programmers get all the speed advantages of using prepared statements but they never see SQLITE_SCHEMA errors or expired statements and they need not even know what a prepared statement is, much less how to operate sqlite3_prepare(). This frees programmer brain-cycles to be used in solving the problem at hand rather than fussing with the SQL interface. Other language bindings and wrappers would do well to emulate this design.