SQLite CVSTrac

The use of a callback to report row data in SQLite is simple to describe, but it is often inconvenient to use. It would be better to have a way to get a row data as it was generated that did not involve the use of a callback.

To this end, I propose the following new API as the fundamental access mechanism for SQLite. The older sqlite_exec() would still be support for compatibility, but would actually be implemented in terms of the routines described below. Please feel free to add your remarks and suggestions to the text below.

Executing SQL now becomes a two-step process. First you have to compile the SQL into a virtual machine. Then you have to execute the virtual machine to access the database. Compiling is done with a function like this:

    int sqlite_compile(
      sqlite *db,           /* An open database */
      const char *zSQL,     /* The SQL to be compiled */
      const char **pzTail,  /* OUT: Part of zSQL not compiled */
      sqlite_vm **pVm,      /* OUT: Virtual machine written here */
      char **pzErrMsg       /* OUT: Error message written here */
    );

The routine above compiles the first SQL statement out of zSQL and generates a virtual machine that will run that single statement. If the original input contains two or more statements, *pzTail is left pointing to the beginning of the second statement. If zSQL original contained only a single SQL statement, then *pzTail is left pointing at the NUL-terminator for zSQL. This allows multiple SQL statements to be processed in a loop:

    const char *z = zOrigSql;
    while( z && z[0] ){
      sqlite_compile(db, z, &z, &pVm, 0);
      /* Deal with pVm */
    }

A virtual machine is allocated and written into *pVm. The virtual machine is opaque - users cannot use the internals of the sqlite_vm structure. The only thing you are allowed to do with an sqlite_vm is pass it to other API routines.

Run a VM something like this:

    while( sqlite_step(pVm, &n, &azValue, &azColName)==SQLITE_ROW ){
      /* Do something with a row of data */
    }
    rc = sqlite_finalize(pVm, &zErrMsg);

The sqlite_step() call runs the virtual machine until it generates a new row of data, encounters an error, or until it finishes. After the VM finishes, call sqlite_finalize(pVm) to deallocate it. The sqlite_finalize() returns the result code (ex: SQLITE_OK, SQLITE_BUSY, SQLITE_FULL, etc) and also sets the error message string if appropriate.

Information about a single row of the result is written into variables that are passed by address into sqlite_step(). The variable "n" is filled with the number of columns in a single row of the result. azValue is filled with a pointer to an array of pointers to strings - one string for each result. NULL values in the result are represented by NULL pointers. This works just like the azValues parameter to an sqlite_exec() callback. The azColName variable is filled with a pointer to an array of pointers to strings. The first N strings contain column names and the second ground of N strings contain column types. The column types are always reported, regardless of the setting of the SHOW_DATATYPES pragma.

    while( sqlite_step(pVm, &n, &azValue, &azColName) ){
      int i;
      for(i=0; i<n; i++){
        printf("%s(type %s) = %s\n",
          azColName[i], azColName[i+n], azValue[i]);
      }
    }
    rc = sqlitevm_finalize(pVm, &zErrMsg);

The non-callback API has now been implemented and tested. For additional information see the comments in sqlite.h.in or read the latest C/C++ interface documentation.

sqlite - Non Callback Api