Re: [sqlite] Busy management

[Jay Sprenkle]

On 3/3/06, Ludovic Ferrandis <[EMAIL PROTECTED]> wrote:
> I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
> ms then try the command Y times. I found 2 ways to do it:

I do it using sqlite3_step(); Using bound variables and step eliminates the need
for escaping string data and prevents SQL injection attacks. I retry
the statement
up to 10 times in case another process has locked the database.
Psuedo code looks like this:

    // open database
    dbOpen();

    // Get configuration information for this website instance
    string sql = "SELECT blah"
                 " FROM Setup"
                 ;

    // prepare statement instead of building it to avoid sql injection attacks
    if ( ! dbPrep( sql ) )
      throw ConException( string("Cannot prepare sql: ") + sql +
string(", ") +  + sqlite3_errmsg(db) );

    bool loop = true;
    for ( int i = 0; ( i < 10 ) && ( loop ); i++ )
      switch ( dbStep() )
        {
          // if database busy wait for a short time
          // to see if it becomes available
          case SQLITE_BUSY:
          case SQLITE_LOCKED:
            break;
          case SQLITE_ROW:
            // get results ( 0 based index!!! )
            blah  = dbColumn( 0 );
            break;
          case SQLITE_DONE:
            if ( CookieUser.empty() )
              throw ConException( string("Invalid configuration") );
            loop = false;
            break;
          default:
            throw ConException( string("Cannot execute sql: ") + sql );
            break;
        }

    // clean up when finished
    dbFinalize();
    dbClose();