Announcement

Collapse
No announcement yet.

Sqlite query with callback

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sqlite query with callback

    I'm doing some data analysis with biggish volumes of data for which my previous approach - i.e. getting all the output from a query into memory, was not working. I couldn't find an example to copy, so had to cobble one together from info on Jose Roca's excellent forum. Just posted a simple example to the Source Code Forum.
    Last edited by Chris Holbrook; 29 Mar 2008, 12:19 PM. Reason: changed title

  • #2
    Hi Chris,

    FYI, I don't think that using the Callback method is the preferred methodology these days with SQLite. If processing huge data sets then the following sequence can be used and it uses very little memory:

    sqlite3_prepare_v2
    sqlite3_step
    sqlite3_finalize

    Here are some SQLite wrappers from my SQlite3 Client/Server that may help:
    Code:
    '//
    '//
    '//
    Function sqlPrepare( ByVal hDB As Long, sSql As String, ByRef ppStmt As Dword ) Export As Long
        Local errcode   As Long
        Local pzTail    As Asciiz Ptr    
       
        errcode = sqlite3_prepare_v2( hDB, ByVal StrPtr(sSQL), Len(sSQL), ppStmt, pzTail )
    
        Function = errcode
    End Function
    
    
    
    '//
    '//
    '//
    Function sqlStep( ByVal ppStmt As Dword ) Export As Long
    
       Local errcode As Long
       
       errcode = sqlite3_step( ppStmt )
    
       Function = errcode
    
    End Function
    
    
    '//
    '//
    '//
    Function sqlFinalize( ByVal ppStmt As Dword ) Export As Long
    
       Local errcode As Long
       
       errcode = sqlite3_finalize( ppStmt )
    
       Function = errcode
    
    End Function
    Paul Squires
    FireFly Visual Designer (for PowerBASIC Windows 10+)
    Version 3 now available.
    http://www.planetsquires.com

    Comment


    • #3
      Originally posted by Paul Squires View Post
      Hi Chris,

      FYI, I don't think that using the Callback method is the preferred methodology these days with SQLite. If processing huge data sets then the following sequence can be used and it uses very little memory:

      sqlite3_prepare_v2
      sqlite3_step
      sqlite3_finalize
      Paul, that's exactly what sqlite3_exec does. To quote from the header file ( I assume the comments are SQLite's rather than JR's):

      '** The sqlite3_exec() interface is implemented in terms of
      '** [sqlite3_prepare_v2()], [sqlite3_step()], and [sqlite3_finalize()].
      '** The sqlite3_exec() routine does nothing that cannot be done
      '** by [sqlite3_prepare_v2()], [sqlite3_step()], and [sqlite3_finalize()].
      '** The sqlite3_exec() is just a convenient wrapper.
      BTW I'm posting a slightly nicer example using a listview into the Source Code Forum, give it 10 mins.

      (later) here is the C code for sqlite3_exec, again I took it from Jose Roca's forum but I think it is SQLite's code:
      Code:
      /*
      ** Execute SQL code.  Return one of the SQLITE_ success/failure
      ** codes.  Also write an error message into memory obtained from
      ** malloc() and make *pzErrMsg point to that message.
      **
      ** If the SQL is a query, then for each row in the query result
      ** the xCallback() function is called.  pArg becomes the first
      ** argument to xCallback().  If xCallback=NULL then no callback
      ** is invoked, even for queries.
      */
      int sqlite3_exec(
        sqlite3 *db,                /* The database on which the SQL executes */
        const char *zSql,           /* The SQL to be executed */
        sqlite3_callback xCallback, /* Invoke this callback routine */
        void *pArg,                 /* First argument to xCallback() */
        char **pzErrMsg             /* Write error messages here */
      ){
        int rc = SQLITE_OK;
        const char *zLeftover;
        sqlite3_stmt *pStmt = 0;
        char **azCols = 0;
      
        int nRetry = 0;
        int nCallback;
      
        if( zSql==0 ) return SQLITE_OK;
        while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0] ){
          int nCol;
          char **azVals = 0;
      
          pStmt = 0;
          rc = sqlite3_prepare(db, zSql, -1, &pStmt, &zLeftover);
          assert( rc==SQLITE_OK || pStmt==0 );
          if( rc!=SQLITE_OK ){
            continue;
          }
          if( !pStmt ){
            /* this happens for a comment or white-space */
            zSql = zLeftover;
            continue;
          }
      
          nCallback = 0;
      
          nCol = sqlite3_column_count(pStmt);
          azCols = sqliteMalloc(2*nCol*sizeof(const char *) + 1);
          if( azCols==0 ){
            goto exec_out;
          }
      
          while( 1 ){
            int i;
            rc = sqlite3_step(pStmt);
      
            /* Invoke the callback function if required */
            if( xCallback && (SQLITE_ROW==rc || 
                (SQLITE_DONE==rc && !nCallback && db->flags&SQLITE_NullCallback)) ){
              if( 0==nCallback ){
                for(i=0; i<nCol; i++){
                  azCols[i] = (char *)sqlite3_column_name(pStmt, i);
                }
                nCallback++;
              }
              if( rc==SQLITE_ROW ){
                azVals = &azCols[nCol];
                for(i=0; i<nCol; i++){
                  azVals[i] = (char *)sqlite3_column_text(pStmt, i);
                }
              }
              if( xCallback(pArg, nCol, azVals, azCols) ){
                rc = SQLITE_ABORT;
                goto exec_out;
              }
            }
      
            if( rc!=SQLITE_ROW ){
              rc = sqlite3_finalize(pStmt);
              pStmt = 0;
              if( rc!=SQLITE_SCHEMA ){
                nRetry = 0;
                zSql = zLeftover;
                while( isspace((unsigned char)zSql[0]) ) zSql++;
              }
              break;
            }
          }
      
          sqliteFree(azCols);
          azCols = 0;
        }
      
      exec_out:
        if( pStmt ) sqlite3_finalize(pStmt);
        if( azCols ) sqliteFree(azCols);
      
        rc = sqlite3ApiExit(0, rc);
        if( rc!=SQLITE_OK && rc==sqlite3_errcode(db) && pzErrMsg ){
          int nErrMsg = 1 + strlen(sqlite3_errmsg(db));
          *pzErrMsg = sqlite3_malloc(nErrMsg);
          if( *pzErrMsg ){
            memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg);
          }
        }else if( pzErrMsg ){
          *pzErrMsg = 0;
        }
      
        assert( (rc&db->errMask)==rc );
        return rc;
      }
      Last edited by Chris Holbrook; 25 Mar 2008, 04:53 PM. Reason: to add the C code for sqlite3_exec

      Comment


      • #4
        SQLite's One-Step Query Execution Interface reference.

        Comment


        • #5
          Hi Chris,

          Thanks for the code. I remember seeing many posts about people having trouble with the callback method. You seemed to have nailed it.

          The stuff that I was referring to comes from the book "The Definitive Guide to SQLite" (pages 210 and 211). It doesn't really say not to use the callback method, it just hints that it has certain limitations. Here is the quote:


          "So sqlite3_exec() provides not only an easy way to modify the database but an interface with which to process records as well. Why then should you bother with prepared queries? Well, as you will see in the next section, there are quite a few advantages to using the latter:

          • Prepared queries don’t require a callback interface, which makes coding simple and more linear.

          • Prepared queries have associated functions that provide better column information. You can obtain a column’s storage type, declared type, schema name (if it is aliased), table name, and database name. sqlite3_exec()’s callback interface provides just the column names.

          • Prepared queries provide a way to obtain field/column values in other data types besides text, in native C data types such as int and double, whereas sqlite3_exec()’s callback interface only provides fields as string values.

          • Prepared queries can be rerun, allowing you to reuse the compiled SQL.

          • Prepared queries support parameterized SQL statements.

          As a matter of history, sqlite3_exec()’s callback interface is reminiscent of the old SQLite 2.x API (It is interesting to note that sqlite3_exec() is implemented in a source file called legacy.c.). In that API, the callback interface was the way you performed all queries and retrieved all records. The new approach is a refinement of this interface, which works more like other database client libraries."
          Both methods get the job done and that's all that matters.
          Paul Squires
          FireFly Visual Designer (for PowerBASIC Windows 10+)
          Version 3 now available.
          http://www.planetsquires.com

          Comment


          • #6
            Originally posted by Paul Squires View Post
            Both methods get the job done
            Well, it's "horses for courses". Callbacks are no good for handling blobs, as your reference implies. Actually, one should say "the sqlite3_exec callback" is no good for this purpose, as it would be pretty straightforward to write a derivative of sqlite3_exec which had a different sort of callback, maybe passing through the data type and a data descriptor.

            I have no axe to grind here, the sqlite3_exec did the job I needed to do and I posted it as I could not find a working example to copy myself! Maybe someone should post an example of using the "raw" prepare, step and finalize calls?

            Comment


            • #7
              Originally posted by Chris Holbrook View Post
              Maybe someone should post an example of using the "raw" prepare, step and finalize calls?
              somebody has.

              Comment

              Working...
              X