SQLite C Core API

Wrapped Queries

Connecting and Disconnecting

Before you can execute SQL commands, you first have to connect to a database. Connecting to a database is perhaps best described as opening a database, because SQLite databases are contained in single operating system files (one file to one database). Analogously, the preferred term for disconnecting is closing the database.

You open a database with the sqlite3_open_v2(), sqlite3_open(), or sqlite3_open16() functions, which have the following declaration(s):

int sqlite3_open_v2(
  const char *filename, /* Database filename (UTF-8) */
  sqlite3 **ppDb        /* OUT: SQLite db handle */
  int flags,
  const char *zVfs      /* Name of VFS module to use */
);

int sqlite3_open (
  const void *filename, /* Database filename (UTF-8) */
  sqlite3 **ppDb        /* OUT: SQLite db handle */
);

int sqlite3_open16(
  const void *filename, /* Database filename (UTF-16) */
  sqlite3 **ppDb        /* OUT: SQLite db handle */
);

Typically, you'll use sqlite3_open_v2(), because this is the latest and greatest function in SQLite for opening your database, and it accommodates more options and capabilities over the old sqlite3_open(). Regardless of the function chosen, the filename argument can be the name of an operating system file, the text string :memory:, or an empty string or a NULL pointer. If you use :memory:, sqlite3_open_v2() will create an in-memory database in RAM that lasts only for the duration of the session. If filename is an empty string or a NULL, sqlite3_open_v2() opens a temporary disk file that is automatically deleted when the connection closes. Otherwise, sqlite3_open_v2() attempts to open the database file by using its value. If no file by that name exists, sqlite3_open_v2() will open a new database file by that name if the SQLITE_OPEN_CREATE flag is included in the third parameter, or it will return an error if the SQLITE_OPEN_CREATE flag is omitted.

The flags parameter is a bit vector that can include the following values: SQLITE_OPEN_READONLY, SQLITE_OPEN_READWRITE, and SQLITE_OPEN_CREATE. The names are reasonable self-explanatory, but a few subtleties are worth noting. SQLITE_OPEN_READONLY and SQLITE_OPEN_READWRITE open a SQLite database in read-only or read/write mode as their names suggest. Both options require that the database file already exist; otherwise, an error will be returned. SQLITE_OPEN_CREATE combined with SQLITE_OPEN_READWRITE exhibits the legacy sqlite3_open() behavior. Where a database already exists, it is opened for reading and writing. If the database specified doesn't exist, it is created (though the act of persisting the database to disk will be pending the creation of the first object).

The flags parameter can also be combined with the SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, or SQLITE_OPEN_PRIVATECACHE flags to further control transactional behavior for the database handle.

The final parameter, zVfs, allows the caller to override the default sqlite3_vfs operating system interface.

Upon completion, sqlite3_open_v2() will initialize the sqlite3 structure passed into it by the ppDb argument. This structure should be considered as an opaque handle representing a single connection to a database. This is more of a connection handle than a database handle since it is possible to attach multiple databases to a single connection. However, this connection still represents exactly one transaction context regardless of how many databases are attached.

You close a connection by using the sqlite3_close() function, which is declared as follows:

int sqlite3_close(sqlite3*);

For sqlite3_close() to complete successfully, all prepared queries associated with the connection must be finalized. If any queries remain that have not been finalized, sqlite3_close() will return SQLITE_BUSY with the error message Unable to close due to unfinalized statements.

int sqlite3_exec(
  sqlite3*,        /* An open database */
  const char *sql, /* SQL code to be executed */
  sqlite_callback, /* Callback function */
  void *data,      /* 1st argument to callback function */
  char **errmsg    /* Error msg written here */
);

int main(int argc, char **argv)
{
  sqlite3 *db;
  char *zErr;
  int rc;
  char *sql;
  rc = sqlite3_open_v2("test.db", &db);
  if(rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(1);
  }
  sql = "create table episodes(id int, name text)";
  rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
  if(rc != SQLITE_OK) {
    if (zErr != NULL) {
      fprintf(stderr, "SQL error: %s\n", zErr);
      sqlite3_free(zErr);
  }
  sql = "insert into episodes values (10, 'The Dinner Party')";
  rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
}

typedef int (*sqlite3_callback) (
  void*,  /* Data provided in the 4th argument of sqlite3_exec() */
  int,    /* The number of columns in row */
  char**, /* An array of strings representing fields in the row */
  char**  /* An array of strings representing column names */
);

int callback(void* data, int ncols, char** values, char** headers);
int main(int argc, char **argv)
{
  sqlite3 *db;
  int rc;
  char *sql;
  char *zErr;
  rc = sqlite3_open_v2("test.db", &db);
  if(rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(1);
  }
  const char* data = "Callback function called";
  sql = "insert into episodes (id, name) values (11,'Mackinaw Peaches');"
  "select * from episodes;";
  rc = sqlite3_exec(db, sql, callback, data, &zErr);
  if(rc != SQLITE_OK) {
    if (zErr != NULL) {
      fprintf(stderr, "SQL error: %s\n", zErr);
      sqlite3_free(zErr);
    }
  }
  sqlite3_close(db);
  return 0;
} /* main() */

int callback(void* data, int ncols, char** values, char** headers)
{
  int i;
  fprintf(stderr, "%s: ", (const char*)data);
  for(i=0; i < ncols; i++) {
    fprintf(stderr, "%s=%s ", headers[i], values[i]);
  }
  fprintf(stderr, "\n");
  return 0;
}

Callback function called: id=10 name=The Dinner Party
Callback function called: id=11 name=Mackinaw Peaches

int sqlite3_get_table(
  sqlite3*,        /* An open database */
  const char *sql, /* SQL to be executed */
  char ***resultp, /* Result written to a char *[] that this points to */
  int *nrow,       /* Number of result rows written here */
  int *ncolumn,    /* Number of result columns written here */
  char **errmsg    /* Error msg written here, if any */
);

int main(int argc, char **argv)
{
  /* Connect to database, etc. */

  char *result[];
  sql = "select * from episodes;";
  rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);

  /* Do something with data */

  /* Free memory */
  sqlite3_free_table(result);
}

name | id
-----------------------
The Junior Mint   | 43
The Smelly Car    | 28
The Fusilli Jerry | 21

result [0] = "name";
result [1] = "id";
result [2] = "The Junior Mint";
result [3] = "43";
result [4] = "The Smelly Car";
result [5] = "28";
result [6] = "The Fusilli Jerry";
result [7] = "21";

/* Iterating Through sqlite3_get_table() Results */
rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
for(i=0; i < nrows; i++) {
  for(j=0; j < ncols; j++) {
    /* the i+1 term skips over the first record,
       which is the column headers */
    fprintf(stdout, "%s", result[(i+1)*ncols + j]);
  }
}

Prepared Queries

We have already introduced the concepts of the prepare, step, and finalize functions. This section covers all aspects of this process, including stepping through result sets, fetching records, and using parameterized queries. We told you prepared statements were infinitely preferable to the convenience wrapper functions, and this is where we prove the point.

The wrapper functions simply wrap all these steps into a single function call, making it more convenient in some situations to run specific commands. Each query function provides its own way of getting at rows and columns. As a general rule, the more packaged the method is, the less control you have over execution and results. Therefore, prepared queries offer the most features, the most control, and the most information, with sqlite3_exec() offering slightly fewer features and sqlite3_get_table() offering fewer still.

Prepared queries use a special group of functions to access field and column information from a row. You get column values using sqlite3_column_xxx(), where xxx represents the data type of the value to be returned (for example, int, double, blob). You can retrieve data in whatever format you like. You can also obtain the declared types of columns (as they are defined in the CREATE TABLE statement) and other miscellaneous metadata such as storage format and both associated table and database names. sqlite3_exec(), by comparison, provides only a fraction of this information through its callback function. The same is true with sqlite3_get_table(), which only includes the result set's column headers with the data.

In practice, you will find that each query method has its uses. sqlite3_exec() is especially good for running commands that modify the database (create, drop, insert, update, and delete). One function call, and it's done. Prepared queries are typically better for SELECT statements because they offer so much more information, more linear coding (no callback functions), and more control by using cursors to iterate over results.

As you'll recall, prepared queries are performed in three basic steps: compilation, execution, and finalization. You compile the query with sqlite3_prepare_v2(), execute it step-by-step using sqlite3_step(), and close it using sqlite3_finalize(), or you can reuse it using sqlite3_reset(). This process and the individual steps are all explained in detail in the following sections.

Compilation

Compilation, or preparation, takes a SQL statement and compiles it into byte code readable by the virtual database engine (VDBE). It is performed by sqlite3_prepare_v2(), which is declared as follows:

int sqlite3_prepare_v2 (
  sqlite3 *db,           /* Database handle */
  const char *zSql,      /* SQL text, UTF-8 encoded */
  int nBytes,            /* Length of zSql in bytes. */
  sqlite3_stmt **ppStmt, /* OUT: Statement handle */
  const char **pzTail    /* OUT: Pointer to unused portion of zSql */
);

sqlite3_prepare_v2() compiles the first SQL statement in the zSQL string (which can contain multiple SQL statements). It allocates all the resources necessary to execute the statement and associates it along with the byte code into a single statement handle (also referred to as simply a statement), designated by the out parameter ppStmt, which is a sqlite3_stmt structure. From the programmer's perspective, this structure is little more than an opaque handle used to execute a SQL statement and obtain its associated records. However, this data structure contains the command's byte code, bound parameters, B-tree cursors, execution context, and any other data sqlite3_step() needs to manage the state of the query during execution.

sqlite3_prepare_v2() does not affect the connection or database in any way. It does not start a transaction or get a lock. It works directly with the compiler, which simply prepares the query for execution. Statement handles are highly dependent on the database schema with which they were compiled. If another connection alters the database schema, between the time you prepare a statement and the time you actually run it, your prepared statement will expire. However, sqlite3_prepare_v2() is built to automatically attempt to recompile (re-prepare) your statement if possible and will do this silently if a schema change has invalidated your existing statement. If the schema has changed in such a way as to make recompilation impossible, your call to sqlite3_step() with the statement will lead to a SQLITE_SCHEMA error, which is discussed later in the section Errors and the Unexpected. At this point, you would need to examine the error associated with SQLITE_SCHEMA by using the sqlite3_errmsg() function.

Execution*

Once you prepare the query, the next step is to execute it using sqlite3_step(), declared as follows:

int sqlite3_step(sqlite3_stmt *pStmt);

sqlite3_step() takes the statement handle and talks directly to the VDBE, which steps through its byte-code instructions one by one to carry out the SQL statement. On the first call to sqlite3_step(), the VDBE obtains the requisite database lock needed to perform the command. If it can't get the lock, sqlite3_step() will return SQLITE_BUSY, if there is no busy handler installed. If one is installed, it will call that handler instead.

For SQL statements that don't return data, the first call to sqlite3_step() executes the command in its entirety, returning a result code indicating the outcome. For SQL statements that do return data, such as SELECT, the first call to sqlite3_step() positions the statement's B-tree cursor on the first record. Subsequent calls to sqlite3_step() position the cursor on subsequent records in the result set. sqlite3_step() returns SQLITE_ROW for each record in the set until it reaches the end, whereupon it returns SQLITE_DONE, indicating that the cursor has reached the end of the set.

All other API functions related to data access use the statement's cursor to obtain information about the current record. For example, the sqlite3_column_xxx() functions all use the statement handle, specifically its cursor, to fetch the current record's fields.

Finalization and Reset*

Once the statement has reached the end of execution, it must be finalized. You can either finalize or reset the statement using one of the following functions:

int sqlite3_finalize(sqlite3_stmt *pStmt);
int sqlite3_reset(sqlite3_stmt *pStmt);

sqlite3_finalize() will close out the statement. It frees resources and commits or rolls back any implicit transactions (if the connection is in autocommit mode), clearing the journal file and freeing the associated lock.

If you want to reuse the statement, you can do so using sqlite3_reset(). It will keep the compiled SQL statement (and any bound parameters) but commits any changes related to the current statement to the database. It also releases its lock and clears the journal file if autocommit is enabled. The primary difference between sqlite3_finalize() and sqlite3_reset() is that the latter preserves the resources associated with the statement so that it can be executed again, avoiding the need to call sqlite3_prepare() to compile the SQL command.

Let's go through an example. The listing below shows a simple, complete program using a prepared query. It is taken from select.c in the examples.

int main(int argc, char **argv)
{
  int rc, i, ncols;
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char *sql;
  const char *tail;

  rc = sqlite3_open_v2("foods.db", &db);
  if(rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(1);
  }

  sql = "select * from episodes;";

  rc = sqlite3_prepare_v2(db, sql, -1, &stmt, &tail);

  if(rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
  }

  rc = sqlite3_step(stmt);
  ncols = sqlite3_column_count(stmt);

  while(rc == SQLITE_ROW) {
    for(i=0; i < ncols; i++) {
      fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
    }
    fprintf(stderr, "\n");

    rc = sqlite3_step(stmt);
  }
  sqlite3_finalize(stmt);
  sqlite3_close(db);

  return 0;
}

This example connects to the foods.db database, queries the episodes table, and prints out all columns of all records within it. Keep in mind this is a simplified example—there are a few other things we need to check for when calling sqlite3_step(), such as errors and busy conditions, but we will address them later.

Like sqlite3_exec(), sqlite3_prepare_v2() can accept a string containing multiple SQL statements.

However, unlike sqlite3_exec(), it will process only the first statement in the string. But it does make it easy for you to process subsequent SQL statements in the string by providing the pzTail out parameter. After you call sqlite3_prepare(), it will point this parameter (if provided) to the starting position of the next statement in the zSQL string. Using pzTail, processing a batch of SQL commands in a given string can be executed in a loop as follows:

while(sqlite3_complete(sql)) {
  rc = sqlite3_prepare(db, sql, -1, &stmt, &tail);
  /* Process query results */

  /* Skip to next command in string. */
  sql = tail;
}

This example uses another API function not yet covered—sqlite3_complete(), which does as its name suggests. It takes a string and returns true if there is at least one complete (but not necessarily valid) SQL statement in it, and it returns false otherwise. In reality, sqlite_complete() looks for a semicolon terminator for the string (and accounting for literals in the SQL). So, although its name suggests some kind of infallible observer checking your statements, it's really just a handy tool for things such as showing you the continuation prompt in the sqlite command line when writing multiline statements and doing other similar useful tasks.

Fetching Records*

So far, you have seen how to obtain records and columns from sqlite3_exec() and sqlite3_get_table(). Prepared queries, by comparison, offer many more options when it comes to getting information from records in the database.

For a statement that returns records, the number of columns in the result set can be obtained using sqlite3_column_count() and sqlite3_data_count(), which are declared as follows:

int sqlite3_column_count(sqlite3_stmt *pStmt);
int sqlite3_data_count(sqlite3_stmt *pStmt);

sqlite3_column_count() returns the number of columns associated with a statement handle. You can call it on a statement handle before it is actually executed. If the query in question is not a select statement, sqlite3_column_count() will return 0. Similarly, sqlite3_data_count() returns the number of columns for the current record, after sqlite3_step() returns SQLITE_ROW. This function will work only if the statement handle has an active cursor.

Getting Column Information

You can obtain the name of each column in the current record using sqlite3_column_name(), which is declared as follows:

const char *sqlite3_column_name( sqlite3_stmt*, /* statement handle */
                                 int iCol       /* column ordinal */);

Similarly, you can get the associated storage class for each column using sqlite3_column_type(), which is declared as follows:

int sqlite3_column_type( sqlite3_stmt*, /* statement handle */
                         int iCol       /* column ordinal */);

This function returns an integer value that corresponds to one of five storage class codes, defined as follows:

#define SQLITE_INTEGER  1
#define SQLITE_FLOAT    2
#define SQLITE_TEXT     3
#define SQLITE_BLOB     4
#define SQLITE_NULL     5

These are SQLite's native data types, or storage classes. All data stored within a SQLite database is stored in one of these five forms, depending on its initial representation and the affinity of the column. For our purposes, the terms storage class and data type are synonymous.

You can obtain the declared data type of a column as it is defined in the table's schema using the sqlite3_column_decltype() function, which is declared as follows:

const char *sqlite3_column_decltype(
  sqlite3_stmt*, /* statement handle */
  int            /* column ordinal */);

If a column in a result set does not correspond to an actual table column (say, for example, the column is the result of a literal value, expression, function, or aggregate), this function will return NULL as the declared type of that column. For example, suppose you have a table in your database defined as follows:

CREATE TABLE t1(c1 INTEGER);

Then you execute the following query:

SELECT c1 + 1, 0 FROM t1;

In this case, sqlite3_column_decltype() will return INTEGER for the first column and NULL for the second.

In addition to the declared type, you can obtain other information on a column using the following functions:

const char *sqlite3_column_database_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_table_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_origin_name(sqlite3_stmt *pStmt, int iCol);

The first function will return the database associated with a column, the second will return its table, and the last function will return the column's actual name as defined in the schema. That is, if you assigned the column an alias in the SQL statement, sqlite3_column_origin_name() will return its actual name as defined in the schema. Note that these functions are available only if you compile SQLite with the SQLITE_ENABLE_COLUMN_METADATA preprocessor directive.

Getting Column Values

You can obtain the values for each column in the current record using the sqlite3_column_xxx() functions, which are of the following general form:

xxx sqlite3_column_xxx( sqlite3_stmt*, /* statement handle */
                        int iCol /* column ordinal */);

Here xxx is the data type you want the data represented in (for example, int, blob, double, and so on). These are the most commonly used of the sqlite3_column_xxx() functions:

int sqlite3_column_int(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);

For each function, SQLite converts the internal representation (storage class in the column) to the type specified in the function name. There are a number of rules SQLite uses to convert the internal data type representation to that of the requested type. The table below lists these rules.

Like the sqlite3_bind_xxx() functions described later, BLOBs require a little more work in that you must specify their length in order to copy them. For BLOB columns, you can get the actual length of the data using sqlite3_column_bytes(), which is declared as follows:

int sqlite3_column_bytes( sqlite3_stmt*, /* statement handle */
                          int            /* column ordinal */);

Once you get the length, you can copy the binary data using sqlite3_column_blob(). For example, say the first column in the result set contains binary data. One way to get a copy of that data would be as follows:

int len = sqlite3_column_bytes(stmt,0);
void* data = malloc(len);
memcpy(data, len, sqlite3_column_blob(stmt,0));

A Practical Example

To help solidify all these column functions, the following listing (taken from columns.c) illustrates using the functions we've described to retrieve column information and values for a simple SELECT statement.

int main(int argc, char **argv)
{
  int rc, i, ncols, id, cid;
  char *name, *sql;
  sqlite3 *db;
  sqlite3_stmt *stmt;

  sql = "select id, name from episodes";
  sqlite3_open_v2("test.db", &db);

  sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);

  ncols = sqlite3_column_count(stmt);
  rc = sqlite3_step(stmt);

  /* Print column information */
  for(i=0; i < ncols; i++) {
    fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
    sqlite3_column_name(stmt, i),
    sqlite3_column_type(stmt, i),
    sqlite3_column_decltype(stmt, i));
  }

  fprintf(stdout, "\n");

  while(rc == SQLITE_ROW) {
    id   = sqlite3_column_int( stmt, 0);
    cid  = sqlite3_column_int( stmt, 1);
    name = sqlite3_column_text(stmt, 2);
    if(name != NULL) {
      fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid,name);
    } else {
      /* Field is NULL */
      fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id,cid);
    }
    rc = sqlite3_step(stmt);
  }

  sqlite3_finalize(stmt);
  sqlite3_close(db);
  return 0;
}

This example connects to the database, selects records from the episodes table, and prints the column information and the fields for each row (using their internal storage class). Running the program produces the following output:

Parameterized Queries*

The API includes support for designating parameters in a SQL statement, allowing you to provide (or bind) values for them at a later time. Bound parameters are used in conjunction with sqlite3_prepare(). For example, you could create a SQL statement like the following:

insert into foo values (?,?,?);

Then you can, for example, bind the integer value 2 to the first parameter (designated by the first ? character), the string value pi to the second parameter, and the double value 3.14 for the third parameter, as illustrated in the following code (taken from parameters.c):

const char* sql = "insert into foo values(?,?,?)";
sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);

sqlite3_bind_int(stmt, 1, 2);
sqlite3_bind_text(stmt, 2, "pi");
sqlite3_bind_double(stmt, 3, 3.14);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

This generates and executes the following statement:

insert into foo values (2, 'pi', 3.14)

This particular method of binding uses positional parameters where each parameter is designated by a question mark (?) character and later identified by its index or relative position in the SQL statement.

Before delving into the other parameter methods, it is helpful to first understand the process by which parameters are defined, bound, and evaluated. When you write a parameterized statement such as the following, the parameters within it are identified when sqlite3_prepare() compiles the query:

insert into episodes (id,name) values (?,?)

sqlite3_prepare() recognizes that there are parameters in a SQL statement. Internally, it assigns each parameter a number to uniquely identify it. In the case of positional parameters, it starts with 1 for the first parameter found and uses sequential integer values for subsequent parameters. It stores this information in the resulting statement handle (sqlite3_stmt structure), which will then expect a specific number of values to be bound to the given parameters before execution. If you do not bind a value to a parameter, sqlite3_step() will use NULL for its value by default when the statement is executed.

After you prepare the statement, you then bind values to it. You do this using the sqlite3_bind_xxx() functions, which have the following general form:

sqlite3_bind_xxx( sqlite3_stmt*, /* statement handle */
                  int i, /* parameter number */
                xxx value /* value to be bound */
);

The xxx in the function name represents the data type of the value to bind. For example, to bind a double value to a parameter, you would use sqlite3_bind_double(), which is declared as follows:

int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

In general, the bind functions can be divided into two classes, one for scalar values (int, double, int64, and NULL) and the other for arrays (blob, text, and text16). They differ only in that the array bind functions require a length argument and a pointer to a cleanup function. Also, sqlite3_bind_text() automatically escapes quote characters like sqlite3_mprintf(). Using the BLOB variant, the array bind function has the following declaration:

int sqlite3_bind_blob( sqlite3_stmt*, /* statement handle */
                       int, /* ordinal */
                       const void*, /* pointer to blob data */
                       int n, /* length (bytes) of data */
                       void(*)(void*)); /* cleanup hander */

There are two predefined values for the cleanup handler provided by the API that have special meanings, defined as follows:

#define SQLITE_STATIC    ((void(*)(void *))  0)
#define SQLITE_TRANSIENT ((void(*)(void *)) -1)

Each value designates a specific cleanup action. SQLITE_STATIC tells the array bind function that the array memory resides in unmanaged space, so SQLite does not attempt to clean it up. SQLITE_TRANSIENT tells the bind function that the array memory is subject to change, so SQLite makes its own private copy of the data, which it automatically cleans up when the statement is finalized. The third option is to provide a pointer to your own cleanup function, which must be of the following form:

void cleanup_fn(void*)

If provided, SQLite will call your cleanup function, passing in the array memory when the statement is finalized.

Once you have bound all the parameters you want, you can then execute the statement. You do this using the next function in the sequence: sqlite3_step(). sqlite3_step() will take the bound values, substitute them for the parameters in the SQL statement, and then begin executing the command.

Now that you understand the binding process, the four parameter-binding methods differ only by the following:

• The way in which parameters are represented in the SQL statement (using a positional parameter, explicitly defined parameter number, or alphanumeric parameter name)
• The way in which parameters are assigned numbers

For positional parameters, sqlite3_prepare() assigns numbers using sequential integer values starting with 1 for the first parameter. In the previous example, the first ? parameter is assigned 1, and the second ? parameter is assigned 2. With positional parameters, it is your job to keep track of which number corresponds to which parameter (or question mark) in the SQL statement and correctly specify that number in the bind functions.

Numbered Parameters

Numbered parameters, on the other hand, allow you to specify your own numbers for parameters, rather than use an internal sequence. The syntax for numbered parameters uses a question mark followed by the parameter number. Take, for example, the following piece of code (taken from parameters.c):

name = "Mackinaw Peaches";
sql = "insert into episodes (id, cid, name) "
      "values (?100,?100,?101)";
rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if(rc != SQLITE_OK) {
  fprintf(stderr, "sqlite3_prepare() : Error: %s\n", tail);
  return rc;
}

sqlite3_bind_int(stmt, 100, 10);
sqlite3_bind_text(stmt, 101, name, strlen(name), SQLITE_TRANSIENT);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

This example uses 100 and 101 for its parameter numbers. Parameter number 100 has the integer value 10 bound to it. Parameter 101 has the string value Mackinaw Peaches bound to it. Note how numbered parameters come in handy when you need to bind a single value in more than one place in a SQL statement. Consider the values part of the previous SQL statement:

insert into episodes (id, cid, name) values (?100,?100,?101);

Parameter 100 is being used twice—once for id and again for cid. Thus, numbered parameters save time when you need to use a bound value in more than one place.

Named Parameters

The third parameter binding method is using named parameters. Whereas you can assign your own numbers using numbered parameters, you can assign alphanumeric names with named parameters. Likewise, because numbered parameters are prefixed with a question mark (?), you identify named parameters by prefixing a colon (:) or at-sign (@) to the parameter name. Consider the following code snippet (taken from parameters.c):

name = "Mackinaw Peaches";
sql = "insert into episodes (id, cid, name) values (:cosmo,:cosmo,@newman)";

rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);

sqlite3_bind_int( stmt,
                  sqlite3_bind_parameter_index(stmt, ":cosmo"),
                  10);

sqlite3_bind_text( stmt,
sqlite3_bind_parameter_index(stmt,
                             "@newman"),
                             name,
                             strlen(name),
                             SQLITE_TRANSIENT );

sqlite3_step(stmt);
sqlite3_finalize(stmt);

This example is identical to the previous example using numbered parameters, except it uses two named parameters called :cosmo and @newman instead. Like positional parameters, named parameters are automatically assigned numbers by sqlite3_prepare(). Although the numbers assigned to each parameter are unknown, you can resolve them using sqlite3_bind_parameter_index(), which takes a parameter name and returns the corresponding parameter number. This is the number you use to bind the value to its parameter. All in all, named parameters mainly help with legibility more than anything else.

Tcl Parameters

The final parameter scheme is called Tcl parameters and is specific more to the Tcl extension than it is to the C API. Basically, it works identically to named parameters except that rather than using alphanumeric values for parameter names, it uses Tcl variable names. In the Tcl extension, when the Tcl equivalent of sqlite3_prepare() is called, the Tcl extension automatically searches for Tcl variables with the given parameter names in the active Tcl program environment and binds them to their respective parameters. Despite its current application in the Tcl interface, nothing prohibits this same mechanism from being applied to other language interfaces, which can in turn implement the same feature. In this respect, referring to this parameter method solely as Tcl parameters may be a bit of a misnomer. The Tcl extension just happened to be the first application that utilized this method. Basically, the Tcl parameter syntax does little more than provide an alternate syntax to named parameters—rather than prefixing the parameters with a colon (:) or an at-sign (@), it uses a dollar sign ($).

Errors and the Unexpected*

Things can and do go wrong, and there is part of the API devoted to that.

The three things you always have to guard against in your code are errors, busy conditions, and, my personal favorite, schema changes.

Handling Errors

Many of the API functions return integer result codes. That means they can potentially return error codes of some sort. The most common functions to watch are typically the most frequently used, such as sqlite3_open_v2(), sqlite3_prepare_v2(), and friends, as well as sqlite3_exec(). You should always program defensively and review every API function before you use it to ensure that you deal with error conditions that can arise. Of all the error results defined in SQLite, only a fraction of them will really matter to your application in practice. All of the SQLite result codes are listed in the table below, and the API functions that can return them include the following:

sqlite3_bind_xxx()
sqlite3_close()
sqlite3_create_collation()
sqlite3_collation_needed()
sqlite3_create_function()
sqlite3_prepare_v2()
sqlite3_exec()
sqlite3_finalize()
sqlite3_get_table()
sqlite3_open_v2()
sqlite3_reset()
sqlite3_step()

You can get extended information on a given error using sqlite3_errmsg(), which is declared as follows:

const char *sqlite3_errmsg(sqlite3*);

It takes a connection handle as its only argument and returns the most recent error resulting from an API call on that connection. If no error has been encountered, it returns the string not an error.

Although it is very uncommon outside of embedded systems, one of the most critical errors you can encounter is SQLITE_NOMEM, which means that no memory can be allocated on the heap (for example, malloc() failed). SQLite is quite robust in this regard, recovering gracefully from out-of-memory error conditions. It will continue to work assuming the underlying operating system completes memory allocation system calls like malloc().

Handling Busy Conditions

Two important functions related to processing queries are sqlite3_busy_handler() and sqlite3_busy_timeout(). If your program uses a database on which there are other active connections, odds are it will eventually have to wait for a lock and therefore will have to deal with SQLITE_BUSY. Whenever you call an API function that causes SQLite to seek a lock and SQLite is unable to get it, the function will return SQLITE_BUSY. There are three ways to deal with this:

• Handle SQLITE_BUSY yourself, either by rerunning the statement or by taking some other action
• Have SQLite call a busy handler
• Ask SQLite to wait (block or sleep) for some period of time for the lock to clear

The last option involves using sqlite3_busy_timeout(). This function tells SQLite how long to wait for a lock to clear before returning SQLITE_BUSY. Although it can ultimately result in you still having to handle SQLITE_BUSY, in practice setting this value to a sufficient period of time (say 30 seconds) usually provides enough time for even the most intensive transaction to complete. Nevertheless, you should still have some contingency plan in place to handle SQLITE_BUSY.

User-Defined Busy Handlers

The second option entails using sqlite3_busy_handler(). This function provides a way to call a user-defined function rather than blocking or returning SQLITE_BUSY right away. It's declared as follows:

int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);

The second argument is a pointer to a function to be called as the busy handler, and the third argument is a pointer to application-specific data to be passed as the first argument to the handler. The second argument to the busy handler is the number of prior calls made to the handler for the same lock.

Such a handler might call sleep() for a period to wait out the lock, or it may send some kind of notification. It may do whatever you like, because it is yours to implement. Be warned, though, that registering a busy handler does not guarantee that it will always be called. SQLite will forego calling a busy handler for a connection if it perceives a deadlock might result. Specifically, if your connection in SHARED is interfering with another connection in RESERVED, SQLite will not invoke your busy handler, hoping you will take the hint. In this case, you are trying to write to the database from SHARED (starting the transaction with BEGIN) when you really should be starting from RESERVED (starting the transaction with BEGIN IMMEDIATE).

The only restriction on busy handlers is that they may not close the database. Closing the database from within a busy handler can delete critical data structures out from under the executing query and result in crashing your program.

Handling Schema Changes*

Whenever a connection changes the database schema, all other prepared statements that were compiled before the change are invalidated. The result is that the first call to sqlite3_step() for such statements will attempt to recompile the relevant SQL and proceed normally from there if possible. If recompilation is impossible (for example, if an object has been dropped entirely), the sqlite3_step() returns SQLITE_SCHEMA. From a locking standpoint, the schema change occurs between the time a reader calls sqlite3_prepare() to compile a statement and calling sqlite3_step() to execute it.

When this happens, the only course of action for you is to handle the change in circumstances and start over. Several events can cause SQLITE_SCHEMA errors:

• Detaching databases
• Modifying or installing user-defined functions or aggregates
• Modifying or installing user-defined collations
• Modifying or installing authorization functions
• Vacuuming the database

The reason the SQLITE_SCHEMA condition exists ultimately relates to the VDBE. When a connection changes the schema, other compiled queries may have VDBE code that points to database objects that no longer exist or are in a different location in the database. Rather than running the risk of a bizarre runtime error later, SQLite invalidates all statements that have been compiled but not executed. They must be recompiled.

*

*

Operational Control*

Threads*