Eduardo wrote:
You seem to think that the problem occurs when schema changes right
in the middle of sqlite3_prepare call. This is not the case. The
problem is that a prepared statement may sit around for a long time
before it is actually used in a sqlite3_step call. At this point the
engine may find out that the schema now differs from what it was
when the statement was prepared, so the internal data structures are
no longer valid. How do you propose to handle this situation, other
than re-prepare? Would you prefer disabling any and all schema
modifications for as long as there exists a single prepared
statement?
Instead of re-prepare the affected transaction/s, don't allow any
transaction prepare if other is changing the schema.
Sorry for being dense, but I don't understand what you are saying. What
do you mean by "prepare a transaction"? Transactions are not prepared,
statements are. sqlite3_prepare is not part of a transaction, does not
initiate a transaction, and is not affected by any ongoing transaction
in any way, shape or form. The first call to sqlite3_step on a prepared
statement initiates the transaction (assuming there is no explicitly
started transaction in place on the database handle). SQLITE_SCHEMA
error arises when another process or thread changes the schema between
sqlite3_prepare (or sqlite3_reset) and sqlite3_step.
If your usage pattern is something like
begin transaction
prepare
step
step
...
finalize
commit
then you don't need to worry about SQLITE_SCHEMA under the existing
engine. The whole issue becomes moot. Of course you are losing one of
the benefits of prepared statements - time saving due to elimination of
unnecessary parsing and execution planning.
Igor Tandetnik
