SQLite CVSTrac

Page History

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 */
      char **azErrMsg,      /* Error message written here */
      sqlitevm **pVm,       /* Virtual machine written here */
      const char **pzTail   /* Part of zSQL not compiled */
    );

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, 0, &pVm, &z);
      /* Deal with pVm */
    }

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

Run a VM something like this:

    while( sqlitevm_step(pVm) ){
      /* Do something with a row of data */
    }
    rc = sqlitevm_finalize(pVm, &zErrMsg);

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

Within the VM run loop you can access data as follows:

    while( sqlitevm_step(pVm) ){
      int i;
      for(i=0; i<sqlitevm_column_count(pVm); i++){
        printf("%s(type %s) = %s\n",
          sqlitevm_column_name(pVm, i),
          sqlitevm_column_type(pVm, i),
          sqlitevm_column_data(pVm, i));
      }
    }
    rc = sqlitevm_finalize(pVm, &zErrMsg);

The sqlitevm_column_count() routine returns the number of columns in the result row. sqlitevm_column_name() and sqlitevm_column_type() return the name and datatype of the column. sqlitevm_column_data() returns the text data for the column. All of these sqlite_column functions might be implemented as macros for speed. sqlitevm_column_data() will return NULL for NULL data. If the 2nd argument is out of range, unpreditable things will happen. (Perhaps a segfault.)

sqlite - Non Callback Api

Page History