Sqlite query with callback

[Chris Holbrook]

Maybe someone should post an example of using the "raw" prepare, step and finalize calls?

somebody has.

[Chris Holbrook]

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?

[Paul Squires]

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.

[Andre Smit]

SQLite's One-Step Query Execution Interface reference.

[Chris Holbrook]

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:

/*
** 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;
}

[Paul Squires]

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:

'//
'//
'//
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

[Chris Holbrook]

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.