The C language interface to the SQLite library

(This page was last modified on 2003/10/18 09:37:27 UTC)

The SQLite library is designed to be very easy to use from a C or C++ program. This document gives an overview of the C/C++ programming interface.

1.0 The Core API

The interface to the SQLite library consists of three core functions, one opaque data structure, and some constants used as return values. The core interface is as follows:

typedef struct sqlite sqlite;
#define SQLITE_OK           0   /* Successful result */

sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);

void sqlite_close(sqlite *db);

int sqlite_exec(
  sqlite *db,
  char *sql,
  int (*xCallback)(void*,int,char**,char**),
  void *pArg,
  char **errmsg
);

The above is all you really need to know in order to use SQLite in your C or C++ programs. There are other interface functions available (and described below) but we will begin by describing the core functions shown above.

The names of the columns are contained in first argc entries of the fourth argument. If the SHOW_DATATYPES pragma is on (it is off by default) then the second argc entries in the 4th argument are the datatypes for the corresponding columns.

If the EMPTY_RESULT_CALLBACKS pragma is set to ON and the result of a query is an empty set, then the callback is invoked once with the third parameter (argv) set to 0. In other words

argv == 0

After a virtual machine has been generated using sqlite_compile it is executed by one or more calls to sqlite_step. Each invocation of sqlite_step, except the last one, returns a single row of the result. The number of columns in the result is stored in the integer that the 2nd parameter points to. The pointer specified by the 3rd parameter is made to point to an array of pointers to column values. The pointer in the 4th parameter is made to point to an array of pointers to column names and datatypes. The 2nd through 4th parameters to sqlite_step convey the same information as the 2nd through 4th parameters of the callback routine when using the sqlite_exec interface. Except, with sqlite_step the column datatype information is always included in the in the 4th parameter regardless of whether or not the SHOW_DATATYPES pragma is on or off.

Each invocation of sqlite_step returns an integer code that indicates what happened during that step. This code may be SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or SQLITE_MISUSE.

If the virtual machine is unable to open the database file because it is locked by another thread or process, sqlite_step will return SQLITE_BUSY. The calling function should do some other activity, or sleep, for a short amount of time to give the lock a chance to clear, then invoke sqlite_step again. This can be repeated as many times as desired.

Whenever another row of result data is available, sqlite_step will return SQLITE_ROW. The row data is stored in an array of pointers to strings and the 2nd parameter is made to point to this array.

When all processing is complete, sqlite_step will return either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the statement completed successfully and SQLITE_ERROR indicates that there was a run-time error. (The details of the error are obtained from sqlite_finalize.) It is a misuse of the library to attempt to call sqlite_step again after it has returned SQLITE_DONE or SQLITE_ERROR.

When sqlite_step returns SQLITE_DONE or SQLITE_ERROR, the *pN and *pazColName values are set to the number of columns in the result set and to the names of the columns, just as they are for an SQLITE_ROW return. This allows the calling code to find the number of result columns and the column names and datatypes even if the result set is empty. The *pazValue parameter is always set to NULL when the return codes is SQLITE_DONE or SQLITE_ERROR. If the SQL being executed is a statement that does not return a result (such as an INSERT or an UPDATE) then *pN will be set to zero and *pazColName will be set to NULL.

If you abuse the library by trying to call sqlite_step inappropriately it will attempt return SQLITE_MISUSE. This can happen if you call sqlite_step() on the same virtual machine at the same time from two or more threads or if you call sqlite_step() again after it returned SQLITE_DONE or SQLITE_ERROR or if you pass in an invalid virtual machine pointer to sqlite_step(). You should not depend on the SQLITE_MISUSE return code to indicate an error. It is possible that a misuse of the interface will go undetected and result in a program crash. The SQLITE_MISUSE is intended as a debugging aid only - to help you detect incorrect usage prior to a mishap. The misuse detection logic is not guaranteed to work in every case.

2.3 Deleting A Virtual Machine

Every virtual machine that sqlite_compile creates should eventually be handed to sqlite_finalize. The sqlite_finalize() procedure deallocates the memory and other resources that the virtual machine uses. Failure to call sqlite_finalize() will result in resource leaks in your program.

The sqlite_finalize routine also returns the result code that indicates success or failure of the SQL operation that the virtual machine carried out. The value returned by sqlite_finalize() will be the same as would have been returned had the same SQL been executed by sqlite_exec. The error message returned will also be the same.

It is acceptable to call sqlite_finalize on a virtual machine before sqlite_step has returned SQLITE_DONE. Doing so has the effect of interrupting the operation in progress. Partially completed changes will be rolled back and the database will be restored to its original state (unless an alternative recovery algorithm is selected using an ON CONFLICT clause in the SQL being executed.) The effect is the same as if a callback function of sqlite_exec had returned non-zero.

It is also acceptable to call sqlite_finalize on a virtual machine that has never been passed to sqlite_step even once.

3.0 The Extended API

Only the three core routines described in section 1.0 are required to use SQLite. But there are many other functions that provide useful interfaces. These extended routines are as follows:

int sqlite_last_insert_rowid(sqlite*);

int sqlite_changes(sqlite*);

int sqlite_get_table(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg
);

void sqlite_free_table(char**);

void sqlite_interrupt(sqlite*);

int sqlite_complete(const char *sql);

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);

void sqlite_busy_timeout(sqlite*, int ms);

const char sqlite_version[];

const char sqlite_encoding[];

int sqlite_exec_printf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  ...
);

int sqlite_exec_vprintf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  va_list
);

int sqlite_get_table_printf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  ...
);

int sqlite_get_table_vprintf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  va_list
);

char *sqlite_mprintf(const char *zFormat, ...);

char *sqlite_vmprintf(const char *zFormat, va_list);

void sqlite_freemem(char*);

void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*);

All of the above definitions are included in the "sqlite.h" header file that comes in the source tree.

3.1 The ROWID of the most recent insert

Every row of an SQLite table has a unique integer key. If the table has a column labeled INTEGER PRIMARY KEY, then that column serves as the key. If there is no INTEGER PRIMARY KEY column then the key is a unique integer. The key for a row can be accessed in a SELECT statement or used in a WHERE or ORDER BY clause using any of the names "ROWID", "OID", or "_ROWID_".

When you do an insert into a table that does not have an INTEGER PRIMARY KEY column, or if the table does have an INTEGER PRIMARY KEY but the value for that column is not specified in the VALUES clause of the insert, then the key is automatically generated. You can find the value of the key for the most recent INSERT statement using the sqlite_last_insert_rowid API function.

3.2 The number of rows that changed

The sqlite_changes API function returns the number of rows that have been inserted, deleted, or modified since the database was last quiescent. A "quiescent" database is one in which there are no outstanding calls to sqlite_exec and no VMs created by sqlite_compile that have not been finalized by sqlite_finalize. In common usage, sqlite_changes returns the number of rows inserted, deleted, or modified by the most recent sqlite_exec call or since the most recent sqlite_compile. But if you have nested calls to sqlite_exec (that is, if the callback routine of one sqlite_exec invokes another sqlite_exec) or if you invoke sqlite_compile to create a new VM while there is still another VM in existance, then the meaning of the number returned by sqlite_changes is more complex. The number reported includes any changes that were later undone by a ROLLBACK or ABORT. But rows that are deleted because of a DROP TABLE are not counted.

SQLite implements the command "DELETE FROM table" (without a WHERE clause) by dropping the table then recreating it. This is much faster than deleting the elements of the table individually. But it also means that the value returned from sqlite_changes will be zero regardless of the number of elements that were originally in the table. If an accurate count of the number of elements deleted is necessary, use "DELETE FROM table WHERE 1" instead.

3.3 Querying into memory obtained from malloc()

The sqlite_get_table function is a wrapper around sqlite_exec that collects all the information from successive callbacks and writes it into memory obtained from malloc(). This is a convenience function that allows the application to get the entire result of a database query with a single function call.

The main result from sqlite_get_table is an array of pointers to strings. There is one element in this array for each column of each row in the result. NULL results are represented by a NULL pointer. In addition to the regular data, there is an added row at the beginning of the array that contains the name of each column of the result.

As an example, consider the following query:

SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%';

This query will return the name, login and host computer name for every employee whose login begins with the letter "d". If this query is submitted to sqlite_get_table the result might look like this:

nrow = 2
ncolumn = 3
result[0] = "employee_name"
result[1] = "login"
result[2] = "host"
result[3] = "dummy"
result[4] = "No such user"
result[5] = 0
result[6] = "D. Richard Hipp"
result[7] = "drh"
result[8] = "zadok"

Notice that the "host" value for the "dummy" record is NULL so the result[] array contains a NULL pointer at that slot.

If the result set of a query is empty, then by default sqlite_get_table will set nrow to 0 and leave its result parameter is set to NULL. But if the EMPTY_RESULT_CALLBACKS pragma is ON then the result parameter is initialized to the names of the columns only. For example, consider this query which has an empty result set:

SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;

The default behavior gives this results:

nrow = 0
ncolumn = 0
result = 0

But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following is returned:

nrow = 0
ncolumn = 3
result[0] = "employee_name"
result[1] = "login"
result[2] = "host"

Memory to hold the information returned by sqlite_get_table is obtained from malloc(). But the calling function should not try to free this information directly. Instead, pass the complete table to sqlite_free_table when the table is no longer needed. It is safe to call sqlite_free_table with a NULL pointer such as would be returned if the result set is empty.

The sqlite_get_table routine returns the same integer result code as sqlite_exec.

3.4 Interrupting an SQLite operation

The sqlite_interrupt function can be called from a different thread or from a signal handler to cause the current database operation to exit at its first opportunity. When this happens, the sqlite_exec routine (or the equivalent) that started the database operation will return SQLITE_INTERRUPT.

3.5 Testing for a complete SQL statement

The next interface routine to SQLite is a convenience function used to test whether or not a string forms a complete SQL statement. If the sqlite_complete function returns true when its input is a string, then the argument forms a complete SQL statement. There are no guarantees that the syntax of that statement is correct, but we at least know the statement is complete. If sqlite_complete returns false, then more text is required to complete the SQL statement.

For the purpose of the sqlite_complete function, an SQL statement is complete if it ends in a semicolon.

The sqlite command-line utility uses the sqlite_complete function to know when it needs to call sqlite_exec. After each line of input is received, sqlite calls sqlite_complete on all input in its buffer. If sqlite_complete returns true, then sqlite_exec is called and the input buffer is reset. If sqlite_complete returns false, then the prompt is changed to the continuation prompt and another line of text is read and added to the input buffer.

3.6 Library version string

The SQLite library exports the string constant named sqlite_version which contains the version number of the library. The header file contains a macro SQLITE_VERSION with the same information. If desired, a program can compare the SQLITE_VERSION macro against the sqlite_version string constant to verify that the version number of the header file and the library match.

3.7 Library character encoding

By default, SQLite assumes that all data uses a fixed-size 8-bit character (iso8859). But if you give the --enable-utf8 option to the configure script, then the library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.

Note that the character encoding mechanism used by SQLite cannot be changed at run-time. This is a compile-time option only. The sqlite_encoding character string just tells you how the library was compiled.

3.8 Changing the library's response to locked files

The sqlite_busy_handler procedure can be used to register a busy callback with an open SQLite database. The busy callback will be invoked whenever SQLite tries to access a database that is locked. The callback will typically do some other useful work, or perhaps sleep, in order to give the lock a chance to clear. If the callback returns non-zero, then SQLite tries again to access the database and the cycle repeats. If the callback returns zero, then SQLite aborts the current operation and returns SQLITE_BUSY.

The arguments to sqlite_busy_handler are the opaque structure returned from sqlite_open, a pointer to the busy callback function, and a generic pointer that will be passed as the first argument to the busy callback. When SQLite invokes the busy callback, it sends it three arguments: the generic pointer that was passed in as the third argument to sqlite_busy_handler, the name of the database table or index that the library is trying to access, and the number of times that the library has attempted to access the database table or index.

For the common case where we want the busy callback to sleep, the SQLite library provides a convenience routine sqlite_busy_timeout. The first argument to sqlite_busy_timeout is a pointer to an open SQLite database and the second argument is a number of milliseconds. After sqlite_busy_timeout has been executed, the SQLite library will wait for the lock to clear for at least the number of milliseconds specified before it returns SQLITE_BUSY. Specifying zero milliseconds for the timeout restores the default behavior.

3.9 Using the _printf() wrapper functions

The four utility functions

• sqlite_exec_printf()
• sqlite_exec_vprintf()
• sqlite_get_table_printf()
• sqlite_get_table_vprintf()

implement the same query functionality as sqlite_exec and sqlite_get_table. But instead of taking a complete SQL statement as their second argument, the four _printf routines take a printf-style format string. The SQL statement to be executed is generated from this format string and from whatever additional arguments are attached to the end of the function call.

There are two advantages to using the SQLite printf functions instead of sprintf. First of all, with the SQLite printf routines, there is never a danger of overflowing a static buffer as there is with sprintf. The SQLite printf routines automatically allocate (and later frees) as much memory as is necessary to hold the SQL statements generated.

The second advantage the SQLite printf routines have over sprintf are two new formatting options specifically designed to support string literals in SQL. Within the format string, the %q formatting option works very much like %s in that it reads a null-terminated string from the argument list and inserts it into the result. But %q translates the inserted string by making two copies of every single-quote (') character in the substituted string. This has the effect of escaping the end-of-string meaning of single-quote within a string literal. The %Q formatting option works similar; it translates the single-quotes like %q and additionally encloses the resulting string in single-quotes. If the argument for the %Q formatting options is a NULL pointer, the resulting string is NULL without single quotes.

Consider an example. Suppose you are trying to insert a string value into a database table where the string value was obtained from user input. Suppose the string to be inserted is stored in a variable named zString. The code to do the insertion might look like this:

sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%s')",
  0, 0, 0, zString);

If the zString variable holds text like "Hello", then this statement will work just fine. But suppose the user enters a string like "Hi y'all!". The SQL statement generated reads as follows:

INSERT INTO table1 VALUES('Hi y'all')

This is not valid SQL because of the apostrophy in the word "y'all". But if the %q formatting option is used instead of %s, like this:

sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%q')",
  0, 0, 0, zString);

Then the generated SQL will look like the following:

INSERT INTO table1 VALUES('Hi y''all')

Here the apostrophy has been escaped and the SQL statement is well-formed. When generating SQL on-the-fly from data that might contain a single-quote character ('), it is always a good idea to use the SQLite printf routines and the %q formatting option instead of sprintf.

If the %Q formatting option is used instead of %q, like this:

sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES(%Q)",
  0, 0, 0, zString);

Then the generated SQL will look like the following:

INSERT INTO table1 VALUES('Hi y''all')

If the value of the zString variable is NULL, the generated SQL will look like the following:

INSERT INTO table1 VALUES(NULL)

All of the _printf() routines above are built around the following two functions:

char *sqlite_mprintf(const char *zFormat, ...);
char *sqlite_vmprintf(const char *zFormat, va_list);

The sqlite_mprintf() routine works like the the standard library sprintf() except that it writes its results into memory obtained from malloc() and returns a pointer to the malloced buffer. sqlite_mprintf() also understands the %q and %Q extensions described above. The sqlite_vmprintf() is a varargs version of the same routine. The string pointer that these routines return should be freed by passing it to sqlite_freemem().

3.10 Performing background jobs during large queries

The sqlite_progress_handler() routine can be used to register a callback routine with an SQLite database to be invoked periodically during long running calls to sqlite_exec(), sqlite_step() and the various wrapper functions.

The callback is invoked every N virtual machine operations, where N is supplied as the second argument to sqlite_progress_handler(). The third and fourth arguments to sqlite_progress_handler() are a pointer to the routine to be invoked and a void pointer to be passed as the first argument to it.

The time taken to execute each virtual machine operation can vary based on many factors. A typical value for a 1 GHz PC is between half and three million per second but may be much higher or lower, depending on the query. As such it is difficult to schedule background operations based on virtual machine operations. Instead, it is recommended that a callback be scheduled relatively frequently (say every 1000 instructions) and external timer routines used to determine whether or not background jobs need to be run.

For examples of how the SQLite C/C++ interface can be used, refer to the source code for the sqlite program in the file src/shell.c of the source tree. Additional information about sqlite is available at sqlite.html. See also the sources to the Tcl interface for SQLite in the source file src/tclsqlite.c.

Back to the SQLite Home Page