Re: [sqlite] Read row data into array?

[Jay Sprenkle]

On 3/8/06, cstrader <[EMAIL PROTECTED]> wrote:
> Would someone be willing to share with me c++ code that reads the result of a 
> select query into an array representing the data of the j rows in a selected 
> column?  I understand that callback() is executed once for each row of the 
> data.  But what is the best and fastest way to iteratively write these row 
> data into a single array that I can pass to my host program?


Callbacks are problematic in C++. It's tricky to get the address of a
class member
and provide the 'this' pointer correctly. Here's a quick example of
reading a single
record (I have a wrapper around the sqlite calls, but I think it gets
the message across):


    // open database
    dbOpen();

    // check to see if user is admin or moderator
    string sql = "SELECT
CookieUser,CookieVisit,Timezone,CookieLife,CGI,URL,ContactName,ContactMail,Description
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) );

    switch ( dbStep() )
      {
        case SQLITE_ROW:
          // get results ( 0 based index!!! )
          CookieUser  = dbColumn( 0 );
          CookieVisit = dbColumn( 1 );
          Timezone    = dbColumn( 2 );
          CookieLife  = atol( dbColumn( 3 ) );
          CGI         = dbColumn( 4 );
          URL         = dbColumn( 5 );
          ContactName = dbColumn( 6 );
          ContactMail = dbColumn( 7 );
          Description = dbColumn( 8 );
          break;
        case SQLITE_DONE:
          if ( CookieUser.empty() )
            throw ConException( string("Invalid configuration") );
          break;
        default:
          throw ConException( string("Cannot execute sql: ") + sql );
          break;
      }

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


dbColumn looks like this:

//---------------------------------------------------------------------------
// return a pointer to the text of a result row
// 0 based index!
//---------------------------------------------------------------------------
char* Convention::dbColumn( const unsigned int Index )
  {
    char* p = (char*) sqlite3_column_text( pStmt, Index );
    return (char*) ( p ? p : "" );
  }