The only way for a database connection to know if the schema
has changed is to open and read the database file. Sqlite_prepare()
tries to avoid reading the database file in order to reduce
contention, though, so it is unlikely to discover a database
change. The change is only discovered when sqlite3_step()
is run.
Having sqlite3_prepare() avoid opening and reading the database
file is a desirable optimization since it makes sqlite3_prepare()
run faster in the common case where no schema change occurs.
A fair enough optimisation.
The only way I know of to force sqlite3_prepare() to check
the database schema is to close and reopen the database
connection. But doing so will dramatically increase the
amount of time it takes to prepare each statement.
When the step fails (with SQLITE_ERROR not SQLITE_SCHEMA) the finalize
is called and it returns SQLITE_SCHEMA. (If sqlite3_step could return
SQLITE_SCHEMA the condition would be easier to detect). Having finalized
the previous statement, the next statement prepared and stepped works
perfectly. Does this mean that sqlite3_finalize is closing and reopening
the database under the hood? If so, does this mean that sqlite3_finalize
is very expensive? If not, then there is something that sqlite3_finalize
does that updates the connection's view of the schema. Would it possible
to do this prior to the prepare? (should one wish to do so and knowing
that there is an overhead involved)
Your best approach is to modify your code so that you are
prepared to deal with SQLITE_SCHEMA errors returned by
sqlite3_step. You'll need this anyway for the rare case
when the schema changes in between sqlite3_prepare and
sqlite3_step.
And, presumably, the less rare case where subsequent steps are called to
retrieve data. Indeed, this is easy to cope with from a users
perspective, an active query is terminated due to a schema change. It
just seems a little strange (to us) that a schema change by another
thread/process that may have completed "ages" ago means that the next
query we attempt fails.
We can program for this in that if the first sqlite3_step of a prepared
statement fails then, like sqlite3_exec, we can re-try the prepare and
step. If it isn't the first step then we can terminate the select and
somehow indicate that the schema has changed during the select.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
