Jay Sprenkle wrote:
The SQLITE_SCHEMA case is handled in our code. But in this case, we are
in the middle of iterating a statement. Can we prepare it again without
"losing our place"? Is this the right action considering that we are
getting a SQLITE_ERROR when executing the statement, not SQLITE_SCHEMA?
I'm sorry, I'd just be guessing at an answer.
My guess is you cannot keep your place if you re-prepare a statement.
I guess what I'm encountering is either an obscure bug in SQLite or some
product of a misunderstanding of the engine's behavior when schema
changes occur. To elaborate on the information I posted before, I have
both a transaction and statement open (which I am iterating) and I
modify the database schema four times: 1) to rename an existing table
"foo" to "_foo", 2) to create a new table "foo" 3) to drop the old
"_foo" and 4) to rename an existing table "bar" to "_bar". All of these
go off without a hitch, and despite the fact that I have modified the
schema, at no point does either sqlite3_prepare() or sqlite3_step()
return an error, nor does sqlite3_finalize() return SQLITE_SCHEMA.
When I try to create a new "bar" table, however, I get the error "SQL
logic error or missing database". Note that this occurs on the "CREATE
TABLE" statement. When I finalize this statement, it does not return
SQLITE_SCHEMA. The only difference I can see between the "foo" case
(which works) and the "bar" case (which doesn't) is that in the former
case I have an open statement that I am iterating, whereas in the second
case I have a second statement open as well (basically I am recursing
into a hierarchical data structure). Or perhaps it is the manipulation
of "foo" inside the transaction that causes the attempt to create a new
"bar" to fail.
My questions:
1) If I am stepping though a statement and change the schema, shouldn't
the next step() or prepare() return an error requiring me to recreate
the statement? How come I'm not observing this behavior? Note that none
of the schema modifications actually effect tables used in the
above-mentioned tables (otherwise presumably I would get a "table is
locked" error?). Also, I am running all database operations in a single
thread, so concurrency should not be an issue.
2) Is it normal that "CREATE TABLE" would return "SQL logic error or
missing database"? If so, under what circumstances might this occur? I'm
frankly stumped as to why this occurs at this point. Perhaps I'm
overlooking something silly, but in any case it would be helpful to have
an idea of the most common cases that result in this error.
3) In general, is there some documentation of how SQLite handles schema
changes?
Many thanks in advance,.
Matt
