Re: [sqlite] How long can I keep a prepared statement around?
[EMAIL PROTECTED] wrote:
> The TCL bindings also keep a cache of recently used SQL
> statements. The bindings automatically detect if the SQL matches a
statement in cache
> and reuse the existing sqlite3_stmt if it does.
Does the TCL wrapper allow multiple commands in one go? eg can it be
called with {select 3 ; select 4} ? Assuming so, it would require the
statement comparison to be identical, even including whitespace.
Also, how do you deal with threads correctly if the same database handle
is used across threads?
> Other language
> bindings and wrappers would do well to emulate this design.
Several years ago you did post an email implying you'd could do the
statement caching as part of SQLite itself rather than having all the
wrapper authors re-invent the wheel. I can't find that email any more ...
Roger
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How long can I keep a prepared statement around?
"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] -
AW: RE: RE: [sqlite] How long can I keep a prepared statement around?
That's basically the way I do it. I have a factory method for all
prepared statements, where I wrap the (optional) recompilation in and aquire a
named mutex for every method invocation in the class.
I think this should be a safe approach to prevent SQLITE_SCHEMA errors if the
only schema changes occur on the same sqlite3 connection. (E.g. no other
process, thread or connection changes the schema.)
Mike
>> Looking at the implementation of sqlite3_expired, its just a
>> comparison of the expired flag in the statement or if the
>> passed statement is NULL. The cost is an additional if
>> statement before execution, however this does not save you
>> from SQLITE_SCHEMA errors. In theory someone could modify the
>> schema just after you called sqlite3_expired, but before you
>> step the statement.
>
>I've acquired a mutex lock at this point so no danger of another thread
>clobbering the schema before the step.
>
>I'm also testing for NULL statements anyway (first pass). I figured
>an ease way to avoid schema errors would be to change:
>
>if (db._insertStatement == NULL)
>{
> // preparation
>
>to:
>
>if (sqlite3_expired(db._insertStatement))
>{
> // preparation
>
>>
>> The question should more likely be: When is the expired flag set?
>>
>
>I agree.
>
>Daniel.
>
>==
>
>Please access the attached hyperlink for an important electronic
>communications disclaimer:
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>==
>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: RE: [sqlite] How long can I keep a prepared statement around?
> Looking at the implementation of sqlite3_expired, its just a
> comparison of the expired flag in the statement or if the
> passed statement is NULL. The cost is an additional if
> statement before execution, however this does not save you
> from SQLITE_SCHEMA errors. In theory someone could modify the
> schema just after you called sqlite3_expired, but before you
> step the statement.
I've acquired a mutex lock at this point so no danger of another thread
clobbering the schema before the step.
I'm also testing for NULL statements anyway (first pass). I figured
an ease way to avoid schema errors would be to change:
if (db._insertStatement == NULL)
{
// preparation
to:
if (sqlite3_expired(db._insertStatement))
{
// preparation
>
> The question should more likely be: When is the expired flag set?
>
I agree.
Daniel.
==
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
AW: RE: [sqlite] How long can I keep a prepared statement around?
Looking at the implementation of sqlite3_expired, its just a comparison of the expired flag in the statement or if the passed statement is NULL. The cost is an additional if statement before execution, however this does not save you from SQLITE_SCHEMA errors. In theory someone could modify the schema just after you called sqlite3_expired, but before you step the statement. The question should more likely be: When is the expired flag set? Mike > >> >> 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? > >Daniel. > > > >== > >Please access the attached hyperlink for an important electronic >communications disclaimer: > >http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html >== > > > >- >To unsubscribe, send email to [EMAIL PROTECTED] >- > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How long can I keep a prepared statement around?
> > 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? Daniel. == Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html == - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How long can I keep a prepared statement around?
Hello Michael, Thanks. Best regards, Kervin --- Michael Ruck <[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 > > Am 24.09.2006 um 20:48 schrieb Kervin L. Pierre: > > > Hello, > > > > I have a few queries that are executed very > > often. I would like to keep them around as > > much as possible. > > > > The problem is, I don't know what > > 'invalidates' a prepared statement. In > > other words, when can I expect to have to > > 're-'prepare a statement? > > > > How long can I keep a prepared statement? > > Can they be passed between threads? I take > > it they are tied to a specific sqlite3_db* > > handle? Do starting new transactions, or > > transaction rollbacks, etc. affect them? > > > > Ideally, for instance, for simple queries > > such as 'BEGIN' and 'COMMIT', I'd like to > > keep those prepared statements for the > > lifetime of the application if possible. > > > > Any information would be appreciated. > > > > Best regards, > > Kervin > > > > > -- > > > --- > > To unsubscribe, send email to > [EMAIL PROTECTED] > > > -- > > > --- > > > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How long can I keep a prepared statement around?
Hello, I have a few queries that are executed very often. I would like to keep them around as much as possible. The problem is, I don't know what 'invalidates' a prepared statement. In other words, when can I expect to have to 're-'prepare a statement? How long can I keep a prepared statement? Can they be passed between threads? I take it they are tied to a specific sqlite3_db* handle? Do starting new transactions, or transaction rollbacks, etc. affect them? Ideally, for instance, for simple queries such as 'BEGIN' and 'COMMIT', I'd like to keep those prepared statements for the lifetime of the application if possible. Any information would be appreciated. Best regards, Kervin - To unsubscribe, send email to [EMAIL PROTECTED] -
