"Shields, Daniel" <[EMAIL PROTECTED]> wrote:
> >
> > Use the function sqlite3_expired to determine, when you need
> > to recompile a prepared statement. That's the approach I use.
> >
> > Mike
>
> How does this compare with the re-preparing statements that
> have failed with a SQLITE_SCHEMA error during sqlite3_step?
>
> e.g. http://www.sqlite.org/faq.html#q17
>
> Using sqlite3_expired certainly seems simpler to me. I'm
> guessing it would be less efficient. Would Dr. Hipp (or
> anyone else) care to comment on the relative merits of
> these two methods?
>
Statements can expire for many reasons, including:
* Some other process changes the database schema
* Your connection executes ATTACH, DETACH, or VACUUM
* Your connection calls sqlite3_set_authorizer()
* (There might be other reasons that do not come immediately
to mind...)
If the cause of the statements expiring is some other process,
then our database connection might not know right away that
the statements have expired. It will not find out until you
next try to access the database, the schema change is noticed,
and SQLITE_SCHEMA is returned by sqlite3_reset() or sqlite3_finalize().
So you must always be prepared to deal with SQLITE_SCHEMA errors.
sqlite3_expired() will tell you about known expirations but not
about expirations that have not yet been discovered.
The way the TCL language bindings deal with this is instructive
and worth reviewing. In the TCL bindings, the original SQL text
of each statement is held with the sqlite3_stmt* pointer. If
sqlite3_step() returns SQLITE_ERROR and a subsequent sqlite3_reset()
returns SQLITE_SCHEMA, then the statement is reprepared and the
sqlite3_step() is automatically retried. Thus a TCL programmer need
not have any knowledge of SQLITE_SCHEMA - such errors are handled
transparently and automatically by the bindings. The TCL bindings
also keep a cache of recently used SQL statements. So a TCL programmer
need not have any knowledge of sqlite3_prepare() either. There is no
direct interface to sqlite3_prepare() from within TCL. To use SQLite
from TCL, you simply give it SQL text to be evaluated. The bindings
automatically detect if the SQL matches a statement in cache
and reuse the existing sqlite3_stmt if it does.
Bottom line: TCL programmers get all the speed advantages of using
prepared statements but they never see SQLITE_SCHEMA errors or
expired statements and they need not even know what a prepared
statement is, much less how to operate sqlite3_prepare(). This
frees programmer brain-cycles to be used in solving the problem
at hand rather than fussing with the SQL interface. Other language
bindings and wrappers would do well to emulate this design.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
