prepared statement and database schema changed problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

prepared statement and database schema changed problem

Peter Hermsdorf
hi,

i'm using a DB base class which prepares some sql statements in it's
constructor. a derived class creates additional tables in the same DB
which "invalidates" the prepared statements in the base class (because
of the schema change).
After browsing the mailinglist i found an older discussion on a similar
topic where the conclusion was "execute the statement again and it will
work".

I'm using the code below to execute the prepared statements, but have
the problem to detect the "schema changed" error code.

the Output i get using the code below is:
26.05.05 11:34:56|sqlite3_step: Code 1 (database schema has changed)

so the errorcode is not SQLITE_SCHEMA (=16), but SQLITE_ERROR. But the
output of sqlite3_errmsg seems to be correct in this case (but the
return code of sqlite3_errcode is still 1 and not 16).

thanks for any advice!

bye, peter

bool continueTrying = true;
while(continueTrying)
{
   rc = sqlite3_step(stmt);
   switch(rc)
   {
     case SQLITE_BUSY:
       DPrint(INFO, "SQLITE_BUSY: sleeping fow a while (step)");
       usleep(100000);
       break;
     case SQLITE_DONE:
     case SQLITE_ROW:
         continueTrying = false; // We're done
         break;
     case SQLITE_ERROR:
         DPrint(ERROR,"sqlite3_step: Code %d (%s)", sqlite3_errcode(db),
sqlite3_errmsg(db));
         continueTrying = false;
         break;
     case SQLITE_SCHEMA:
         DPrint(WARN,"DB Schema changed. Trying again.");
         break;
     default:
         handleError(rc);
         continueTrying = false;
         break;
   }
}
Reply | Threaded
Open this post in threaded view
|

Re: prepared statement and database schema changed problem

Dennis Cote
Peter Hermsdorf wrote:

> hi,
>
> i'm using a DB base class which prepares some sql statements in it's
> constructor. a derived class creates additional tables in the same DB
> which "invalidates" the prepared statements in the base class (because
> of the schema change).
> After browsing the mailinglist i found an older discussion on a
> similar topic where the conclusion was "execute the statement again
> and it will work".
>
> I'm using the code below to execute the prepared statements, but have
> the problem to detect the "schema changed" error code.
>
> the Output i get using the code below is:
> 26.05.05 11:34:56|sqlite3_step: Code 1 (database schema has changed)
>
> so the errorcode is not SQLITE_SCHEMA (=16), but SQLITE_ERROR. But the
> output of sqlite3_errmsg seems to be correct in this case (but the
> return code of sqlite3_errcode is still 1 and not 16).
>
> thanks for any advice!
>
> bye, peter
>
> bool continueTrying = true;
> while(continueTrying)
> {
>   rc = sqlite3_step(stmt);
>   switch(rc)
>   {
>     case SQLITE_BUSY:
>       DPrint(INFO, "SQLITE_BUSY: sleeping fow a while (step)");
>       usleep(100000);
>       break;
>     case SQLITE_DONE:
>     case SQLITE_ROW:
>         continueTrying = false; // We're done
>         break;
>     case SQLITE_ERROR:
>         DPrint(ERROR,"sqlite3_step: Code %d (%s)",
> sqlite3_errcode(db), sqlite3_errmsg(db));
>         continueTrying = false;
>         break;
>     case SQLITE_SCHEMA:
>         DPrint(WARN,"DB Schema changed. Trying again.");
>         break;
>     default:
>         handleError(rc);
>         continueTrying = false;
>         break;
>   }
> }
>
Peter,

Your call to sqlite3_step() is returning an SQLITE_ERROR result. The
SQLITE_SCHEMA error code will be returned by the sqlite3_finalize() call
(or a call to sqlite3_errcode()) after the error is reported.

When you receive the SQLITE_SCHEMA error, you must finalize and then
re-prepare your SQL statement before you can retry executing it. You
can't simply re-execute it by calling sqlite3_step() again.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: prepared statement and database schema changed problem

Peter Hermsdorf

mhm, the problem is i have to store the "Query strings" somewhere, so
that i'm able to prepare them again ....
not nice, but OK.

thanks for your answer.

Dennis Cote wrote:

> Your call to sqlite3_step() is returning an SQLITE_ERROR result. The
> SQLITE_SCHEMA error code will be returned by the sqlite3_finalize() call
> (or a call to sqlite3_errcode()) after the error is reported.
>
> When you receive the SQLITE_SCHEMA error, you must finalize and then
> re-prepare your SQL statement before you can retry executing it. You
> can't simply re-execute it by calling sqlite3_step() again.
>
> HTH
> Dennis Cote