Andrew Shakinovsky wrote:
Thanks. That works great. I would agree with Dennis that this
functionality should belong in the API, however, after reading the
following post, I can see why it is not done this way:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg11398.html
Andrew,
The principal objection in that post is wrong. He said, "In case of a
SELECT statement the situation is still more complex. The SCHEMA error
could happen after reading several result rows."
This can't happen. Either the schema error is returned before the first
row (i.e. on the first step) or the database is locked so that no
process can write to the database which precludes changing the schema
until the statement is reset or finalized.
His other objection that it would be difficult to rebind the variables
is also wrong. There is an existing API function to do this,
sqlite3_transfer_bindings().
He agrees that it would be a good idea for INSERT, UPDATE, and DELETE
statements, but suggests that it could lead to an infinite loop. This
assertion is also incorrect. If the schema changes, then second time the
statement is prepared it will work with the new schema or generate
another error (e.g. if a table referenced by the SQL is deleted). If you
get another schema error on your first step, it means that some other
process has modified the schema yet again since you prepared the
statement. Eventually this has to stop or your application will never
get any work done. When it does stop, the query will either work or it
will fail for some reason other that a schema change. If you are really
concerned about the schema changing this quickly you could re-prepare a
statement until some arbitrary retry limit is exceeded and then return a
new SCHEMA_CHANGING_TOO_QUICKLY error if it is exceeded. This sounds
like a kludge to me, but it can prevent indeterminate execution time.
I don't think there are any valid reasons for not including the wrapper
functionality in the SQLite library in this post.
Dennis Cote
