SQLite API Documentation

0. Introduction

0.1. About this FAQ

This document describes the C API for the SQLite library. This API is defined and commented in the interface header file "sqlite.h".
A shorter description of the API is at The C language interface to the SQLite library.

It is released under the terms of the SQLite license:

** The author disclaims copyright to this material.
** In place of a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.

0.2. Table of Contents

1. Core API
2. Return Codes
3. The table wrapper
4. Formatting functions
5. Accessories
6. Adding new SQL functions
7. Tracing
8. Authorization
9. Callback-free API

Appendices
1. Naming Conventions
2. PRAGMAs that affect API fns
3. Compile-time options
4. Multi-threading

1. Core API

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.

The interface to the SQLite library consists of three core functions, one opaque data structure, and some constants used as return values. These are all you really need in order to use SQLite in your application. There are other convenience functions available (and described below) but we will begin by describing the core functions.

1.1. Database connections

struct sqlite;

Every function that accesses an SQLite database uses the "opaque" sqlite structure returned by sqlite_open(). Opaque means that the definition of this struct is not exposed in the API. Its contents cannot be directly examined using the public API.

It is possible to have multiple sqlite structs reference the same database.

1.2. Opening a database

The sqlite_open() function opens an existing SQLite database or creates a new one. It is the only way to gain access to an SQLite database.

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

dbname is the database name.
mode signals the read/write status of the database. (This argument is currently ignored.)
pzErrMsg is a pointer to an error string pointer.

If the third argument is not NULL and an error occurs while trying to open the database, then an error message will be written to memory obtained from malloc() and *pzErrMsg will be made to point to this error message. The calling function is responsible for freeing the memory when it has finished with it. It should be freed with sqlite_freemem() if being called in a Windows DLL, or using sqliteFree() if statically linked.

The name of an SQLite database is the name of a file that will contain the database. If the file does not exist, SQLite attempts to create and initialize it. If the file is read-only (due to permission bits or because it is located on read-only media like a CD-ROM) then SQLite opens the database for reading only. The entire SQL database is stored in a single file on the disk. But additional temporary files may be created during the execution of an SQL command in order to store the database rollback journal or temporary and intermediate results of a query.

The return value of the sqlite_open() function is a pointer to an opaque sqlite structure. This pointer will be the first argument to all subsequent SQLite function calls that deal with the same database. NULL is returned if the open fails for any reason. If sqlite_open() is called for a database that is already open, it will return a distinct sqlite pointer. Changes made to the database using this pointer will be atomic.

1.3. Closing the database

void sqlite_close(sqlite*);

To close an SQLite database, call the sqlite_close() function passing it the sqlite* structure pointer that was obtained from a prior call to sqlite_open(). If a transaction is active when the database is closed, the transaction is rolled back. If there are other open database connections referencing the same database file, they will remain open.

1.4. Executing SQL statements

The sqlite_exec() function is used to process SQL statements and queries.

int sqlite_exec(
  sqlite *db,
  char *zSql,
  int (*xCallback)(void*,int,char**,char**),
  void *pArg,
  char **pzErrMsg
);

db is a pointer to the sqlite structure obtained from a prior call to sqlite_open().
zSql is a NUL-terminated string containing the text of one or more SQL statements and/or queries to be processed.
xCallback is a pointer to a callback function to be invoked once for each row in the result of a query. This may be NULL, in which case no callback will be invoked.
pArg is a pointer that is forwarded to become the first argument to the callback function.
pzErrMsg is a pointer to an error string.

Error messages are written to space obtained from malloc() and the error string pointer is made to point to the malloc'd space. The calling function is responsible for freeing this space when it has finished with it. This argument may be NULL, in which case error messages are not reported back to the calling function.

The callback function is used to receive the row results of a query. A trivial callback example is:

int Callback(void *pArg, int argc, char **argv, char **columnNames){
  return 0;
}

Note that the callback function reports a NULL value in the database as a NULL pointer, which is very different from an empty string. If the i-th parameter is an empty string, we will get:

argv[i][0] == 0

But if the i-th parameter is NULL we will get:

argv[i] == 0

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

The columnNames argument recieves the names of the columns. The default format is as "column", but can be as "table.column" when in a join or when pragma FULL_COLUMN_NAMES is ON. If the SHOW_DATATYPES pragma is ON, following the column names will be the type of each column.

The callback function should normally return 0. If the callback function returns non-zero, the query is immediately aborted and sqlite_exec() will return SQLITE_ABORT.

1.5. Examples

The page SQLite In 5 Minutes Or Less provides a nice, short example of using these functions.

The SQLite source contains a number of good examples of using sqlite_exec() and callbacks for it:

• sqlite_get_table_cb() in "table.c" is used to construct the result table for sqlite_get_table().
• dump_callback() in "shell.c" is used to dump the database to a text file.
• db_query_callback() in "threadtest.c" is used to store the results of a query.
• callback() in "shell.c" is used by the sqlite command-line utility to output results.
• upgrade_3_callback() in "main.c" is used to upgrade a database file to the current file format.

2. Return codes

2.1. Integer return values

The sqlite_exec() function normally returns SQLITE_OK. But if something goes wrong it can return a different value to indicate the type of error. Here is a complete list of the return codes:

sqlite_exec() and most other sqlite functions return an error value. Zero is used to signify success, and non-zero for failure and error conditions.
Here is a complete list of the return codes:

#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* An internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_ROW         100  /* sqlite_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite_step() has finished executing */

The meanings of these various return values are as follows:

SQLITE_OK

This value is returned if everything worked and there were no errors.

SQLITE_INTERNAL

This value indicates that an internal consistency check within the SQLite library failed. This can only happen if there is a bug in the SQLite library. If you ever get an SQLITE_INTERNAL reply from an sqlite_exec() call, please report the problem on the SQLite mailing list, or create a bug report.

SQLITE_ERROR

This return value indicates that there was an error in the SQL that was passed into the sqlite_exec().

SQLITE_PERM

This return value says that the access permissions on the database file are such that the file cannot be opened.

SQLITE_ABORT

This value is returned if the callback function returns non-zero.

SQLITE_BUSY

This return code indicates that another program or thread has the database locked. SQLite allows two or more threads to read the database at the same time, but only one thread can have the database open for writing at the same time. Locking in SQLite is on the entire database.

SQLITE_LOCKED

This return code is similar to SQLITE_BUSY in that it indicates that the database is locked. But the source of the lock is a recursive call to sqlite_exec(). This return can only occur if you attempt to invoke sqlite_exec() from within a callback routine of a query from a prior invocation of sqlite_exec(). Recursive calls to sqlite_exec() are allowed as long as they do not attempt to write the same table.

SQLITE_NOMEM

This value is returned if a call to malloc() fails.

SQLITE_READONLY

This return code indicates that an attempt was made to write to a database file that is opened for reading only.

SQLITE_INTERRUPT

This value is returned if a call to sqlite_interrupt() interrupts a database operation in progress.

SQLITE_IOERR

This value is returned if the operating system informs SQLite that it is unable to perform some disk I/O operation. This could mean that there is no more space left on the disk.

SQLITE_CORRUPT

This value is returned if SQLite detects that the database it is working on has become corrupted. Corruption might occur due to a rogue process writing to the database file or it might happen due to an perviously undetected logic error in of SQLite. This value is also returned if a disk I/O error occurs in such a way that SQLite is forced to leave the database file in a corrupted state. The latter should only happen due to a hardware or operating system malfunction.

SQLITE_FULL

This value is returned if an insertion failed because there is no space left on the disk, or the database is too big to hold any more information. The latter case should only occur for databases that are larger than 2GB in size.

SQLITE_CANTOPEN

This value is returned if the database file could not be opened for some reason.

SQLITE_PROTOCOL

This value is returned if some other process is messing with file locks and has violated the file locking protocol that SQLite uses on its rollback journal files.

SQLITE_SCHEMA

When the database first opened, SQLite reads the database schema into memory and uses that schema to parse new SQL statements. If another process changes the schema, the command currently being processed will abort because the virtual machine code generated assumed the old schema. This is the return code for such cases. Retrying the command usually will clear the problem.

SQLITE_TOOBIG

SQLite will not store more than about 1 megabyte of data in a single row of a single table. If you attempt to store more than 1 megabyte in a single row, this is the return code you get.

SQLITE_CONSTRAINT

This constant is returned if the SQL statement would have violated a database constraint.

SQLITE_MISMATCH

This error occurs when there is an attempt to insert non-integer data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite ignores the data type and allows any kind of data to be stored. But an INTEGER PRIMARY KEY column is only allowed to store integer data.

SQLITE_MISUSE

This error might occur if one or more of the SQLite API routines is used incorrectly. Examples of incorrect usage include calling sqlite_exec() after the database has been closed using sqlite_close() or calling sqlite_exec() with the same database pointer simultaneously from two separate threads.

SQLITE_NOLFS

Uses OS large file features not supported on host. This value is returned if the SQLite library was compiled with large file support (LFS) enabled but LFS isn't supported on the host operating system.

SQLITE_AUTH

Authorization denied. This value is returned when an authorization callback registered with sqlite_set_authorizer() returns SQLITE_DENY.

SQLITE_FORMAT

Auxiliary database format error. This value is returned when an attempt is made to attach a database which is empty, or has a version different from the main database.

2.2. Error strings

const char *sqlite_error_string(int rc);

rc is an error code returned by sqlite_exec() or a related SQLite API function.

This function returns a short static string that describes the kind of error specified in the argument. These strings are only in English. When an error string is available from an API function such as sqlite_exec(), it should be used instead. Those error strings often contain much more detailed information about the error.

The string returned is statically allocated. It should not be freed.

SQLITE_OK:         "not an error"
SQLITE_ERROR:      "SQL logic error or missing database"
SQLITE_INTERNAL:   "internal SQLite implementation flaw"
SQLITE_PERM:       "access permission denied"
SQLITE_ABORT:      "callback requested query abort"
SQLITE_BUSY:       "database is locked"
SQLITE_LOCKED:     "database table is locked"
SQLITE_NOMEM:      "out of memory"
SQLITE_READONLY:   "attempt to write a readonly database"
SQLITE_INTERRUPT:  "interrupted"
SQLITE_IOERR:      "disk I/O error"
SQLITE_CORRUPT:    "database disk image is malformed"
SQLITE_NOTFOUND:   "table or record not found"
SQLITE_FULL:       "database is full"
SQLITE_CANTOPEN:   "unable to open database file"
SQLITE_PROTOCOL:   "database locking protocol failure"
SQLITE_EMPTY:      "table contains no data"
SQLITE_SCHEMA:     "database schema has changed"
SQLITE_TOOBIG:     "too much data for one table row"
SQLITE_CONSTRAINT: "constraint failed"
SQLITE_MISMATCH:   "datatype mismatch"
SQLITE_MISUSE:     "library routine called out of sequence"
SQLITE_NOLFS:      "kernel lacks large file support"
SQLITE_AUTH:       "authorization denied"
SQLITE_FORMAT:     "auxiliary database format error"

(invalid argument) "unknown error"

3. The table wrappers

3.1. Fetching a result table

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.

int sqlite_get_table(
  sqlite *db,
  char *zSql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **pzErrMsg
);

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.

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

3.2. Freeing a result table

void sqlite_free_table(char **azResult);

azResult is a table result pointer obtained from a call to sqlite_get_table() or one of its ..._printf() variants.

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 table result pointer 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.

3.3. Examples

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"

4. Formatting functions

The four utility functions

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 similarly; 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.

4.1. String Formatting

The library's sqlite_..._[v]printf() routines are implemented using 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 malloc'd 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 using sqlite_freemem().

Flags accepted by the printf functions:

• - left justify
• +
• #
• 0 pad with zeros.
• = center in output

Standard fields accepted by the printf functions:

• %d outputs a integer in signed decimal format.
• %s outputs a string.
• %c outputs a character.
• %o outputs an integer in octal format.
• %u outputs an integer in unsigned decimal format.
• %x outputs an integer in hexadecimal format.
• %X outputs an integer in uppercase hexadecimal format.
• %r
• %f
• %e
• %E
• %g
• %G
• %i
• %n
• %% outputs a literal '%' character.
• %p outputs a pointer in unsigned decimal format.

Extended fields accepted by the printf functions:

• %q outputs a quoted string, suitable for use in a SQL query.
• %Q outputs a quoted string or NULL, suitable for use in a SQL query.
• %b outputs an integer in binary notation.
• %c now accepts a precision. The character output is repeated by the number of times the precision specifies.
• %' works like %c, but takes as its character the next character of the format string, instead of the next argument. For example, printf("%.78'-") prints 78 minus signs, the same as printf("%.78c",'-').

4.2. Freeing dynamic strings

void sqlite_freemem(char*);

All string allocation by the SQLite library is either done with malloc() or its internal wrapper sqliteMalloc(). These can be freed using either free() or sqliteFree() when statically linking to the library. When using the public API, sqlite_freemem() must be used. It is safe to call this function with a NULL pointer, even if the underlying free() is unsafe.

Pointers obtained from sqlite_get_table() should not be freed using this function. Use sqlite_free_table() instead.

4.3. Formatting wrappers

int sqlite_exec_printf(
  sqlite*,
  char *zSql,
  int (*)(void*,int,char**,char**),
  void*,
  char **pzErrMsg,
  ...
);

int sqlite_exec_vprintf(
  sqlite*,
  char *zSql,
  int (*)(void*,int,char**,char**),
  void*,
  char **pzErrMsg,
  va_list
);

int sqlite_get_table_printf(
  sqlite*,
  char *zSql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **pzErrMsg,
  ...
);

int sqlite_get_table_vprintf(
  sqlite*,
  char *zSql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **pzErrMsg,
  va_list
);

4.4. Examples

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 apostrophe 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 apostrophe 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)

5. Accessories

5.1. The ROWID of the most recent insert

int sqlite_last_insert_rowid(sqlite*);

Every row of an SQLite table has a unique integer key. If the table has a column labeled INTEGER PRIMARY KEY (exactly), then that column's data is used as the key. If there is no INTEGER PRIMARY KEY column then the key is a unique integer assigned when the row is added. 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.

5.2. Interrupting an SQLite operation

void sqlite_interrupt(sqlite*);

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.

5.3. Testing for a complete SQL statement

int sqlite_complete(const char *zSql);

The sqlite_complete() function tests whether or not its argument string forms one or more complete SQL statements. If this function returns true, then the argument forms complete SQL statement(s). There are no guarantees that the syntax of the statements are correct, but we at least know the statements are complete. If sqlite_complete() returns false, then more text is required to complete the SQL statements.

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.

5.4. Library version string

const char sqlite_version[];
const char *sqlite_libversion(void);

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

The sqlite_libversion() function is provided to allow calling a function to obtained this value, which is helpful when using the Windows DLL. This value is also available in the built-in SQL function SQLITE_VERSION().

5.5. Library character encoding

const char sqlite_encoding[];
const char *sqlite_libencoding(void);

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.

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

5.6. Changing the library's response to locked files

void sqlite_busy_handler(sqlite*, int(*)(void*,const char*,int), void *pArg);
void sqlite_busy_timeout(sqlite*, int ms);

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.

There is an example busy handler sqliteDefaultBusyCallback() in the SQLite code in "main.c".

5.7. The number of changed rows

int sqlite_changes(sqlite*);

The sqlite_changes() API function returns the number of rows that were inserted, deleted, or modified during the most recent sqlite_exec() call. 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.

This function replaces the use of "PRAGMA count_changes", which may be removed from future versions.

5.8. Handling binary data

5.8.1. Encoding binary data

int sqlite_encode_binary(const unsigned char *in, int n, unsigned char *out);

in is a buffer of the data to encode.
n is the size of the data in the buffer in to encode.
out is the output buffer to recieve the encoded data.

This function encodes a binary buffer "in" of size n bytes so that it contains no instances of characters '\'' or '\0'. The output is null-terminated and can be used as a string value in an INSERT or UPDATE statement. Use sqlite_decode_binary() to convert the encoded string back into the original binary data.

The result is written into a preallocated output buffer "out". "out" must be able to hold at least (256*n + 1262)/253 bytes. In other words, the output will be expanded by as much as 3 bytes for every 253 bytes of input plus 2 bytes of fixed overhead. (This is approximately 2 + 1.019*n or about a 2% size increase.)

The return value is the number of characters in the encoded string, excluding the '\0' terminator.

The encoded string has the following properties:

• Does not contain '\0' chars.
• Does not contain single-quotes. This makes it safe to use directly in SQL statements.
• Increases the size of the string by only about 2%.
• Will never produce an empty string, even on empty input.
• Every string will increase in size by at least 1 byte.

5.8.2. Decoding binary data

int sqlite_decode_binary(const unsigned char *in, unsigned char *out);

Decodes the string in into binary data and writes it into buffer out. This routine reverses the encoding done by sqlite_encode_binary(). The output will always be a few bytes less than the input. The in and out parameters may point to the same buffer in order to decode a string in place.

Returns the number of bytes of output. If the input is not a well-formed encoding, -1 is returned.

5.8.3. Example using binary data

The routines above are not tested by the usual SQLite test suite. To test these routines, compile just "encode.c" with the option -DENCODER_TEST=1, and run the result. The main() in this file provides a good example of how to use these functions.

6. Adding new SQL functions

Beginning with version 2.4.0, SQLite allows the SQL language to be extended with new functions implemented as C code by registering new functions at runtime. SQLite now implements all of its built-in functions using this interface. The sqlite_create_function() interface is used to create regular functions and sqlite_create_aggregate() is used to create new aggregate functions.

6.1. General Rules

The sqlite* argument passed to these functions is the database connection with which the function is being registered. Each function must be registered with each database connection that will use it.

The registration functions return 0 on success, and non-zero on failure.

Function names should be recognizable by the parser, so they shouldn't contain whitespace or operator characters. The length of a function name may not exceed 255 characters. Any attempt to create a function whose name exceeds 255 characters in length will result in an error.

A C function may be registered with several different names.

Passing in NULL for the C function pointer(s) will unregister the function.

Registering a function with the same name and number of arguments as a previously registered function, whether regular or aggregate, replaces the previously registered function.

6.2. Regular Functions

struct sqlite_func;

int sqlite_create_function(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xFunc)(sqlite_func*,int,const char**),
  void *pUserData
);

For regular functions, the xFunc callback is invoked once for each function call. The implementation of xFunc should call one of the sqlite_set_result_...() interfaces to return its result.

int sqlite_function_type(
  sqlite *db,               /* The database there the function is registered */
  const char *zName,        /* Name of the function */
  int datatype              /* The datatype for this function */
);

#define SQLITE_NUMERIC     (-1)
#define SQLITE_TEXT        (-2)
#define SQLITE_ARGS        (-3)

void *sqlite_user_data(sqlite_func*);

The sqlite_user_data() routine can be used to retrieve the pUserData pointer that was passed in when the function was registered.

6.3. Aggregate Functions

For aggregate functions, the xStep callback is invoked once for each row in the result and then xFinalize is invoked at the end to compute a final answer. The xStep routine can use the sqlite_aggregate_context() interface to allocate memory that will be unique to that particular instance of the SQL function. This memory will be automatically deleted after xFinalize is called. The sqlite_aggregate_count() routine can be used to find out how many rows of data were passed to the aggregate. The xFinalize callback should invoke one of the sqlite_set_result_...() interfaces to set the final result of the aggregate.

int sqlite_create_aggregate(
  sqlite *db,
  const char *zName,
  int nArg,
  void (*xStep)(sqlite_func*,int,const char**),
  void (*xFinalize)(sqlite_func*),
  void *pUserData
);

void *sqlite_aggregate_context(sqlite_func*, int nBytes);

int sqlite_aggregate_count(sqlite_func*);

6.4. Return Values

Values returned by user-registered SQL functions are returned using these functions. There is a choice in which one to use, however. For example, any value can be returned as a string. None of these functions cause the user function to terminate directly. You may continue to perform actions in your user function implementation after they are called.

These functions are also safe to call several times in the same function. For example, you could call sqlite_set_result_string() first, and then call a validation routine in which sqlite_set_result_error() might be called. If sqlite_set_result_error() is called at any point in the user function, an error will be generated, even if there is a later call to one of the other result functions.

char *sqlite_set_result_string(sqlite_func *pFn, const char *zResult, int n);

Use this function to return a string value. A copy is made of the string before this routine returns so it is safe to pass in an ephemeral string. This function allocates a buffer to hold the result and returns a pointer to this buffer. The calling routine (that is, the implementation of a user function) can alter the content of this buffer if desired.

void sqlite_set_result_int(sqlite_func*,int);

Use this function to return an integer value.

void sqlite_set_result_double(sqlite_func*,double);

Use this function to return a floating point value.

void sqlite_set_result_error(sqlite_func *pFn, const char *zError, int n);

Use this function to return an error from a user function, and set an error string.

6.5. Examples

All of SQLite's built in SQL functions are implemented using this interface. See the source file "func.c" for good examples.

6.5.1 Registering functions

Function sqliteRegisterBuiltinFunctions() provides an example of registering both regular and aggregate functions.

The regular SQL function coalesce() demonstrates a variable argument function with a minimum number of arguments, in this case 2. This is done when it is registered in sqliteRegisterBuiltinFunctions(). The lines which register it with a NULL (0) pointer for 1 and 0 arguments mean that it must have at least 2 arguments or the parser will generate an error.

    { "coalesce",  -1, SQLITE_ARGS,    ifnullFunc },
    { "coalesce",   0, 0,              0          },
    { "coalesce",   1, 0,              0          },

6.5.2 Regular functions

The regular SQL function min(), implemented in minFunc(), provides an example of a function that takes a variable number of arguments. In this case it has been prevented from having 0 arguments by registering it with a NULL pointer that case.

    { "min",       -1, SQLITE_ARGS,    minFunc    },
    { "min",        0, 0,              0          },

6.5.3 Aggregate functions

The aggregate SQL function sum(), implemented in sumStep() and sumFinalize() provides an example of an aggregate function that takes a variable number of arguments.

7. Tracing

Register a function that is called at every invocation of sqlite_exec(). If a query string contains multiple statements, the tracing callback will only be invoked once for the entire string, not once for each distinct SQL statement, despite the implementation of the new callback-free API, which doesn't support tracing. This function can be used, for example, to generate a log file of all SQL statements executed against a database connection.

7.1. Initializing tracing

void *sqlite_trace(sqlite *db, void(*xTrace)(void*,const char*), void *pTraceArg);

db is the sqlite database connection to trace on.
xTrace is the tracing callback function to register.
pTraceArg is a pointer to pass through to the trace callback.

The sqlite_trace() call returns the previously registered trace callback, or NULL if there wasn't one.

Tracing can be turned off by registering a NULL callback pointer.

7.2. Tracing callbacks

The callback has the following prototype:

void Tracer(void *pTraceArg, const char *zSql);

pTraceArg is the pointer registered with the sqlite_trace() call.
zSql is the SQL string argument to the sqlite_exec() call.

7.3. Tracing example

8. Authorization

Although SQLite doesn't support SQL's privilege commands GRANT and REVOKE, it is still possible to check authorization of SQL queries using a callback function registered using the function sqlite_set_authorizer().

8.1. Initializing authorization

int sqlite_set_authorizer(
  sqlite *db,
  int (*xAuth)(void*,int,const char*,const char*),
  void *pAuthArg
);

db is the sqlite database connection to authorize the operation on.
xAuth is the authorization callback function.
pAuthArg will be passed into all subsequent calls of the authorization callback function.

Only one authorization callback may be registered with an sqlite database connection at a time. If another is registered, the current one is removed. The authorizer and its custom authorization argument only apply to the sqlite connection with which it is registered. If multiple connections are opened on the same database, the callback must be registered separately for each one it is needed in.

8.2. Authorization callbacks

The authorization callback is invoked before every attempt to access a column of a table or view, as well as for other operation types. It will usually only be called once per query, not for each row. The callback returns SQLITE_OK if access is allowed, SQLITE_DENY if the entire SQL statement should be aborted with an error and SQLITE_IGNORE if the column should be treated as a NULL value.

The callback has the following prototype:

int Authorizer(void* pAuthArg, int nCode, const char* zArg1, const char* zArg2);

pAuthArg is the argument passed into the original sqlite_set_authorizer() call.
nCode will be one of the values below. These values signify the type of operation is to be authorized.
zArg1 and zArg2 will be strings or NULL, depending on which of the following codes is used as the nCode parameter.

/*
**      nCode                               zArg1           zArg2
*/
#define SQLITE_COPY                  0   /* Table Name      File Name       */
#define SQLITE_CREATE_INDEX          1   /* Index Name      Table Name      */
#define SQLITE_CREATE_TABLE          2   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_INDEX     3   /* Index Name      Table Name      */
#define SQLITE_CREATE_TEMP_TABLE     4   /* Table Name      NULL            */
#define SQLITE_CREATE_TEMP_TRIGGER   5   /* Trigger Name    Table Name      */
#define SQLITE_CREATE_TEMP_VIEW      6   /* View Name       NULL            */
#define SQLITE_CREATE_TRIGGER        7   /* Trigger Name    Table Name      */
#define SQLITE_CREATE_VIEW           8   /* View Name       NULL            */
#define SQLITE_DELETE                9   /* Table Name      NULL            */
#define SQLITE_DROP_INDEX           10   /* Index Name      Table Name      */
#define SQLITE_DROP_TABLE           11   /* Table Name      NULL            */
#define SQLITE_DROP_TEMP_INDEX      12   /* Index Name      Table Name      */
#define SQLITE_DROP_TEMP_TABLE      13   /* Table Name      NULL            */
#define SQLITE_DROP_TEMP_TRIGGER    14   /* Trigger Name    Table Name      */
#define SQLITE_DROP_TEMP_VIEW       15   /* View Name       NULL            */
#define SQLITE_DROP_TRIGGER         16   /* Trigger Name    Table Name      */
#define SQLITE_DROP_VIEW            17   /* View Name       NULL            */
#define SQLITE_INSERT               18   /* Table Name      NULL            */
#define SQLITE_PRAGMA               19   /* Pragma Name     1st arg or NULL */
#define SQLITE_READ                 20   /* Table Name      Column Name     */
#define SQLITE_SELECT               21   /* NULL            NULL            */
#define SQLITE_TRANSACTION          22   /* NULL            NULL            */
#define SQLITE_UPDATE               23   /* Table Name      Column Name     */

The return value of the authorization function should be one of the following constants:

#define SQLITE_OK     0   /* Allow access (This is actually defined above) */
#define SQLITE_DENY   1   /* Abort the SQL statement with an error */
#define SQLITE_IGNORE 2   /* Don't allow access, but don't generate an error */

What will happen if a different value is returned? See fn sqliteAuthBadReturnCode().

8.3. Authorization example

The following simple example disables operations based on authorization bits set in the *pAuthArg argument, which is an integer bitmask.

/*
** Disable some database operations based on a bitmask of allowed ones.
** "pAuthArg" is a pointer to a bitmask of allowed operation codes.
** "nCode" is the operation code being checked.
** "zArg1" and "zArg2" are unused.
*/
int Authorizer(void* pAuthArg, int nCode, const char* zArg1, const char* zArg2)
{
  int nMask = *(int*)pAuthArg;
  int nBit  = (1 << nCode);

  return (nBit & nMask)? SQLITE_OK: SQLITE_DENY;
}

The authorizer is used in the following code fragment.

/* Register the callback function and the authorization mask. */
int nMask = SQLITE_PRAGMA | SQLITE_READ | SQLITE_SELECT;
sqlite_set_authorizer(db, Authorizer, (void*)&nMask);

/* Do some operations ... */

/* Change the authorization mask to allow UPDATEs. */
nMask |= SQLITE_UPDATE;

/* Do some more operations ... */

Can we return an error string from the authorization callback?

9. Callback-free API

There is also a page in the wiki documenting this.

9.0. Overview

Using a callback with sqlite_exec() to return result rows is simple to implement, but it is often inconvenient to use. The sqlite_get_table() wrapper allows easily iterating through the results, but with the overhead of having to make copies of the entire result set, and keeping it all in memory at once. The "callback-free" functions allow the convenience of iterating through the results as they are generated, without the overhead of sqlite_get_table().

Executing a query is now a three-step process. First you compile the query into a virtual machine (VM). Then you execute the VM to iterate through the results it generates. Finally you terminate the VM.

This interface was added after version 2.7.6.

9.1. VM instances

struct sqlite_vm;

This is an opaque structure which is created by sqlite_compile(). After the original query has been compiled into a VM, all operations are done using using the obtained sqlite_vm* pointer instead. It represents a single SQL statement which is ready to execute.

9.2. Compiling a query

int sqlite_compile(
  sqlite *db,                 /* The database on which the SQL executes */
  const char *zSql,           /* The SQL to be executed */
  const char **pzTail,        /* OUT: Next statement after the first */
  sqlite_vm **ppVm,           /* OUT: The virtual machine */
  char **pzErrMsg             /* OUT: Write error messages here */
);

If the SQL string contains multiple statements, *pzTail is left pointing to the beginning of the second statement. If there was only a single SQL statement, then *pzTail is left pointing at its NUL-terminator. This function only compiles a single statement at a time. 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 */
}

If the SQL string contains multiple statements, it must remain valid while the VM is being used. The VM will refer back to the string as each statement is processed. The VM will not make a copy of this string.

If there is an error or the input text contained no SQL (if the input is an empty string or a comment) then *ppVm is set to NULL.

As usual, the error string is dynamically allocated, and must be freed using sqlite_freemem().

The return value is SQLITE_OK if successful, otherwise an error string. sqlite_compile() uses the same error return values as sqlite_exec().

9.3. Executing a query

int sqlite_step(
  sqlite_vm *pVm,              /* The virtual machine to execute */
  int *pN,                     /* OUT: Number of columns in result */
  const char ***pazValue,      /* OUT: Column data */
  const char ***pazColName     /* OUT: Column names and datatypes */
);

After an SQL statement has been compiled, it is handed to this routine to be executed. This routine executes the statement as far as it can go then returns. The return value will be one of SQLITE_DONE, SQLITE_ERROR, SQLITE_BUSY, SQLITE_ROW, or SQLITE_MISUSE.

SQLITE_DONE means that the execute of the SQL statement is complete an no errors have occurred. sqlite_step() should not be called again for the same virtual machine. *pN is set to the number of columns in the result set and *pazColName is set to an array of strings that describe the column names and datatypes. The name of the i-th column is (*pazColName)[i] and the datatype of the i-th column is (*pazColName)[i+*pN]. *pazValue is set to NULL.

SQLITE_ERROR means that the virtual machine encountered a run-time error. sqlite_step() should not be called again for the same virtual machine. *pN is set to 0 and *pazColName and *pazValue are set to NULL. Use sqlite_finalize() to obtain the specific error code and the error message text for the error.

SQLITE_BUSY means that an attempt to open the database failed because another thread or process is holding a lock. The calling routine can try again to open the database by calling sqlite_step() again. The return code will only be SQLITE_BUSY if no busy handler is registered using the sqlite_busy_handler() or sqlite_busy_timeout() routines. If a busy handler callback has been registered but returns 0, then this routine will return SQLITE_ERROR and sqlite_finalize() will return SQLITE_BUSY when it is called.

SQLITE_ROW means that a single row of the result is now available. The data is contained in *pazValue. The value of the i-th column is (*azValue)[i]. *pN and *pazColName are set as described in SQLITE_DONE. Invoke sqlite_step() again to advance to the next row.

SQLITE_MISUSE is returned if sqlite_step() is called incorrectly. For example, if you call sqlite_step() after the virtual machine has halted (after a prior call to sqlite_step() has returned SQLITE_DONE) or if you call sqlite_step() with an incorrectly initialized virtual machine or a virtual machine that has been deleted or that is associated with an sqlite structure that has been closed.

Information about a single result row of the is written into variables that are passed by address into sqlite_step(). The variable "*pN" is filled with the number of columns in a single row of the result. "*pazValue" 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 group of N strings contain column types. The column types are always reported, regardless of the setting of the SHOW_DATATYPES pragma.

9.4. Finishing execution

int sqlite_finalize(
  sqlite_vm *pVm,            /* The virtual machine to be destroyed */
  char **pzErrMsg            /* OUT: Write error messages here */
);

This routine is called to delete a virtual machine after it has finished executing. The return value is the result code. SQLITE_OK is returned if the statement executed successfully and a non-zero value is returned if there was an error. If an error occurred and pzErrMsg is not NULL, then an error message is written into memory obtained from malloc() and *pzErrMsg is made to point to that error message. The calling routine should use sqlite_freemem() to delete this message when it has finished with it.

This routine can be called at any point during execution of the virtual machine. If the virtual machine has not completed execution when this routine is called, that is treated like an error or an interrupt. (See sqlite_interrupt().) Incomplete updates may be rolled back and transactions cancelled, depending on the circumstances, and the result code returned will be SQLITE_ABORT.

9.5. Examples

    while( sqlite_step(pVm, &n, &azValue, &azColName) ){
      int i;
      for(i=0; i