Table of Contents for

Using SQLite

SQLite includes a number of convenience functions that can be used to prepare, step, and finalize an SQL statement in one call. Most of these functions exist for historical reasons and, as the name says, convenience.

While they’re not fully deprecated, there are a number of reasons why their use is not exactly encouraged. First off, understand that there is nothing special under the hood. Both of these functions eventually call the same sqlite3_prepare_xxx(), sqlite3_step(), and sqlite3_finalize() calls that are available in the public API. These functions are not faster, nor are they more efficient.

Second, since the API doesn’t support the use of bound parameters, you’re forced to use string manipulations to build your SQL commands. That means these functions are slower to process and much more vulnerable to SQL injection attacks. This is particularly dangerous because all the convenience functions are designed to automatically process multiple SQL statements from a single command string. If input strings are not properly sanitized, this situation effectively gives anyone providing input data full access to the database engine, including the ability to delete data or drop whole tables.

These functions also tend to be a bit slower. All results are returned in a string representation, without any kind of type information. This can make it difficult to determine the type of a return value, and can lead to a lot of extra type conversions.

For all their disadvantages, there is still the simple fact that these functions are very convenient. If you’re just trying to throw together a quick and dirty snippet of code, these functions provide an easy means of doing that. They’re also perfectly acceptable for DDL commands, such as CREATE TABLE. For any type of DML command, especially those that involve values from unsanitized sources, I strongly recommend using the normal prepare, step, and finalize routines. You’ll end up with safer code and better performance.

The first function allows for fairly generic execution of any SQL command string.

The sqlite3_exec() function is reasonably all encompassing, and can be used to execute any SQL statement. If you’re executing a table query and want to access the result set, you will need to supply a function pointer that references a user-defined callback. This callback will be called once for each row returned. If you’re executing an SQL statement that does not normally return any database value, there is no need to provide a callback function. The success or failure of the SQL command will be indicated in the return value.

The sqlite3_exec() function makes any database results available through a user-defined callback function. As each result row is computed, the callback is called to make the row data available to your code. Essentially, each internal call to sqlite3_step() that results in a return value of SQLITE_ROW results in a callback.

The format of the callback looks like this:

The second, third, and fourth parameters act very similar to the traditional C variables argc and argv (and an extra argv) in main( int argc, char **argv ), the traditional start to every C program. The column value and name arrays will always be the same size for any given callback, but the specific size of the arrays and the column names can change over the course of processing a multi-statement SQL string. There is no need to release any of these values. Once your callback function returns, sqlite3_exec() will handle all the memory management.

If you’d prefer not to mess with a callback, you can use sqlite3_get_table() to extract a whole table at once. Be warned, however, that this can consume a large amount of memory, and must be used carefully.

While you can technically call sqlite3_get_table() with any SQL command string, it is specifically designed to work with SELECT statements.

As indicated, you must release the result of a call to sqlite3_get_table() with a call to sqlite3_free_table(). This will properly release the individual allocations used to build the result value. As with sqlite3_exec(), you must call sqlite3_free() on any errMsg value that is returned.

The result array is a one-dimensional array of character pointers. You must compute your own offsets into the array using the formula:

/* offset to access column C of row R of **result */
int  offset  = ((R + 1) * numCol) + C;
char *value  = result[offset];

The “+ 1” used to compute the row offset is required to skip over the column names, which are stored in the first row of the result. This assumes that the first row and column would be accessed with an index of zero.

As a convenience function, there is nothing special about sqlite3_get_table(). In fact, it is just a wrapper around sqlite3_exec(). It offers no additional performance benefits over the prepare, step, and finalize interfaces. In fact, between all the type conversions inherent in sqlite3_exec(), and all the memory allocations, sqlite3_get_table() has substantial overhead over other methods.

Since sqlite3_get_table() is a wrapper around sqlite3_exec(), it is possible to pass in an SQL command string that consists of multiple SQL statements. In the case of sqlite3_get_table(), this must be done with care, however.

If more than one SELECT statement is passed in, there is no way to determine where one result set ends and the next begins. All the resulting rows are run together as one large result array. All of the statements must return the same number of columns, or the whole sqlite3_get_table() command will fail. Additionally, only the first statement will return any column names. To avoid these issues, it is best to call sqlite3_get_table() with single SQL commands.

There are a number of reasons why these convenience functions may not be the best choice. Their use requires building an SQL command statement using string manipulation functions, and that process tends to be error prone. However, if you insist, your best bet is to use one of SQLite’s built-in string-building functions: sqlite3_mprintf(), sqlite3_vmprintf(), or sqlite3_snprintf(). See Appendix G for more details.