From rogerbinns on October 20, 2009 22:09:22
The original sqlite3 API to prepare statements would result in
SQLITE_SCHEMA being returned from sqlite3_step if the schema changed. This
is because the underlying vdbe program said things like get the value from
column 3 in table 10 and the numbers could have changed. (SQLite did not do
extensive bookkeeping - generally any change would invalidate all
statements). You would then have to call prepare again on the SQL text and
try stepping again.
This caused enough grief that a new sqlite3_prepare_v2 api was added. This
version kept a copy of the SQL text and in the sqlite3_step internals would
automatically reprepare the statement on a schema change. Unfortunately it
messed up error handling. For example if a statement is prepared using an
unknown collation then SQLITE_ERROR is returned from prepare. But if a
statement was reprepared (because a collation had been removed) then
SQLITE_SCHEMA would be returned instead. The effect of this is that
different exception types would be returned depending on whether a
statement was being freshly prepared or was in the statement cache and
sqlite reprepared it. I tried to convince them of the sillyness but they
were determined to keep this broken behaviour - http://www.sqlite.org/src/info/8d510499cc Consequently I reverted to using version 1 of prepare since there was no
benefit to using v2. In addition to having broken error handling, it also
a kept a copy of the sql which I already have as the key for the statement
cache.
SQLite has optimizations for LIKE and ranges (BETWEEN) but these only
applied when the values are part of the SQL text and could not be done when
using bindings. 3.6.20 has new code that can optimize the query if using
bound values. It was originally added as a new API - http://www.sqlite.org/src/vinfo/c48f8ffedc30841aa4994eb925d09c2ee97a864d However on discussion in the developer's mailing list it became apparent
that everyone should just call the new API before calling the first step.
Since this was going to be the case, the functionality was folded into the
SQLite core. But the kicker is that it requires the statement to have been
prepared using v2 of the API.
Original issue: http://code.google.com/p/apsw/issues/detail?id=85
From rogerbinns on October 20, 2009 22:09:22
The original sqlite3 API to prepare statements would result in
SQLITE_SCHEMA being returned from sqlite3_step if the schema changed. This
is because the underlying vdbe program said things like get the value from
column 3 in table 10 and the numbers could have changed. (SQLite did not do
extensive bookkeeping - generally any change would invalidate all
statements). You would then have to call prepare again on the SQL text and
try stepping again.
This caused enough grief that a new sqlite3_prepare_v2 api was added. This
version kept a copy of the SQL text and in the sqlite3_step internals would
automatically reprepare the statement on a schema change. Unfortunately it
messed up error handling. For example if a statement is prepared using an
unknown collation then SQLITE_ERROR is returned from prepare. But if a
statement was reprepared (because a collation had been removed) then
SQLITE_SCHEMA would be returned instead. The effect of this is that
different exception types would be returned depending on whether a
statement was being freshly prepared or was in the statement cache and
sqlite reprepared it. I tried to convince them of the sillyness but they
were determined to keep this broken behaviour - http://www.sqlite.org/src/info/8d510499cc Consequently I reverted to using version 1 of prepare since there was no
benefit to using v2. In addition to having broken error handling, it also
a kept a copy of the sql which I already have as the key for the statement
cache.
SQLite has optimizations for LIKE and ranges (BETWEEN) but these only
applied when the values are part of the SQL text and could not be done when
using bindings. 3.6.20 has new code that can optimize the query if using
bound values. It was originally added as a new API - http://www.sqlite.org/src/vinfo/c48f8ffedc30841aa4994eb925d09c2ee97a864d However on discussion in the developer's mailing list it became apparent
that everyone should just call the new API before calling the first step.
Since this was going to be the case, the functionality was folded into the
SQLite core. But the kicker is that it requires the statement to have been
prepared using v2 of the API.
Original issue: http://code.google.com/p/apsw/issues/detail?id=85