Teg <[EMAIL PROTECTED]> wrote:
> Hello drh,
>
> I always assume the prepared statement were transitory. I typically
> create them in a function, use them than delete them. Is there a
> discussion of their life span somewhere? I typically have DB's that
> never change schema after their initial creation. How reliable is it
> to say, create the prepared statement in a constructor and use them
> for the life of the class?
>
You can keep prepared statements around as long as you want.
The only restriction is that you have to finalize them all
before you sqlite3_close().
However, a prepared statement might become stale and be unusable.
You will know that a prepared statement has expired if when you
try to sqlite3_step() it you get back SQLITE_ERROR and then
the subsequence sqlite3_reset() returns SQLITE_SCHEMA.
There are many reasons that a prepared statement can become stale.
To be safe, you should always be ready to re-prepare the statement
using the original SQL text. If you have bindings, you can transfer
those bindings to the new statement using sqlite3_transfer_bindings().
I will typically have a structure like this:
struct Stmt {
char *zSql; /* Original text of the SQL statement */
sqlite3 *pDb; /* Pointer to the open database */
sqlite3_stmt *pStmt; /* The prepared statement */
};
Then I have a routine to prepare the Stmt something like this:
void prepare(Stmt *p){
sqlite3_stmt *pNew;
sqlite3_prepare(p->pDb, p->zSql, -1, &pNew, 0);
if( p->pStmt ){
sqlite3_transfer_bindings(p->pStmt, pNew);
sqlite3_finalize(p->pStmt);
}
p->pStmt = pNew;
}
Then I create a step routine like this:
int step(Stmt *p){
int rc;
int loop_limit = 3;
while( loop_limit-- ){
rc = sqlite3_step(p->pStmt);
if( rc==SQLITE_ERROR ){
rc = sqlite3_reset(p->pStmt);
}
if( rc==SQLITE_SCHEMA ){
prepare(p);
}else{
break;
}
}
return rc;
}
In the step() routine above, if a call to sqlite3_step() returns
an SQLITE_SCHEMA error, then the statement is automatically
recompiled and tried again. The code examples above omit a lot
of error checking and other logic that you want want to include
in a robust wrapper, but they should give you the general idea.
Among the things that can cause a statement to go stale:
* Any schema change - including a schema change that is caused
by some outside process.
* Executing an ATTACH or DETACH statement.
* Any call to sqlite3_authorizer().
* Any call to sqlite3_create_function().
* Any call to sqlite3_create_collation().
* PRAGMA statement become stale after one use, but do not
generally cause other statements to become stale.
People occasionally find bugs which are caused by prepared statements
not being expired rapidly enough. So you can expect the list above
to grow. Also notice that the first cause of stale statements -
outside programs modifying the schema - is not under your control.
For these reasons, your program should be ready to deal with an
SQLITE_SCHEMA error at any time. Even in something as simple as:
sqlite3_prepare(db, zSQL, &pStmt, 0, 0);
sqlite3_step(pStmt);
An outside process might have modified your database schema in
between the sqlite3_prepare and the sqlite3_step. It is best to
code defensively.
--
D. Richard Hipp <[EMAIL PROTECTED]>
