Re: [sqlite] 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