C/C++ Interface For SQLite Version 3 (old)

Note: This document was written in 2004 as a guide to helping programmers move from using SQLite version 2 to SQLite version 3. The information in this document is still essentially correct, however there have been many changes and enhancements over the years. We recommend that the following documents be used instead:

• An Introduction To The SQLite C/C++ Interface

• SQLite C/C++ Reference Guide

C/C++ Interface For SQLite Version 3

1.0 Overview

SQLite version 3.0 is a new version of SQLite, derived from the SQLite 2.8.13 code base, but with an incompatible file format and API. SQLite version 3.0 was created to answer demand for the following features:

• Support for UTF-16.

• User-definable text collating sequences.

• The ability to store BLOBs in indexed columns.

It was necessary to move to version 3.0 to implement these features because each requires incompatible changes to the database file format. Other incompatible changes, such as a cleanup of the API, were introduced at the same time under the theory that it is best to get your incompatible changes out of the way all at once.

The API for version 3.0 is similar to the version 2.X API, but with some important changes. Most noticeably, the "sqlite_" prefix that occurs on the beginning of all API functions and data structures are changed to "sqlite3_". This avoids confusion between the two APIs and allows linking against both SQLite 2.X and SQLite 3.0 at the same time.

There is no agreement on what the C datatype for a UTF-16 string should be. Therefore, SQLite uses a generic type of void* to refer to UTF-16 strings. Client software can cast the void* to whatever datatype is appropriate for their system.

2.0 C/C++ Interface

The API for SQLite 3.0 includes 83 separate functions in addition to several data structures and #defines. (A complete API reference is provided as a separate document.) Fortunately, the interface is not nearly as complex as its size implies. Simple programs can still make do with only 3 functions: sqlite3_open(), sqlite3_exec(), and sqlite3_close(). More control over the execution of the database engine is provided using sqlite3_prepare_v2() to compile an SQLite statement into byte code and sqlite3_step() to execute that bytecode. A family of routines with names beginning with sqlite3_column_ is used to extract information about the result set of a query. Many interface functions come in pairs, with both a UTF-8 and UTF-16 version. And there is a collection of routines used to implement user-defined SQL functions and user-defined text collating sequences.

2.1 Opening and closing a database

The sqlite3_open() routine returns an integer error code rather than a pointer to the sqlite3 structure as the version 2 interface did. The difference between sqlite3_open() and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native byte order) for the name of the database file. If a new database file needs to be created, then sqlite3_open16() sets the internal text representation to UTF-16 whereas sqlite3_open() sets the text representation to UTF-8.

The opening and/or creating of the database file is deferred until the file is actually needed. This allows options and parameters, such as the native text representation and default page size, to be set using PRAGMA statements.

The sqlite3_errcode() routine returns a result code for the most recent major API call. sqlite3_errmsg() returns an English-language text error message for the most recent error. The error message is represented in UTF-8 and will be ephemeral - it could disappear on the next call to any SQLite API function. sqlite3_errmsg16() works like sqlite3_errmsg() except that it returns the error message represented as UTF-16 in host native byte order.

The error codes for SQLite version 3 are unchanged from version 2. They are as follows:

2.2 Executing SQL statements

The sqlite3_exec() function works much as it did in SQLite version 2. Zero or more SQL statements specified in the second parameter are compiled and executed. Query results are returned to a callback routine.

In SQLite version 3, the sqlite3_exec routine is just a wrapper around calls to the prepared statement interface.

The sqlite3_prepare interface compiles a single SQL statement into byte code for later execution. This interface is now the preferred way of accessing the database.

The SQL statement is a UTF-8 string for sqlite3_prepare(). The sqlite3_prepare16() works the same way except that it expects a UTF-16 string as SQL input. Only the first SQL statement in the input string is compiled. The fifth parameter is filled in with a pointer to the next (uncompiled) SQLite statement in the input string, if any. The sqlite3_finalize() routine deallocates a prepared SQL statement. All prepared statements must be finalized before the database can be closed. The sqlite3_reset() routine resets a prepared SQL statement so that it can be executed again.

The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa" where "nnn" is an integer and "aaa" is an identifier. Such tokens represent unspecified literal values (or "wildcards") to be filled in later by the sqlite3_bind interface. Each wildcard has an associated number which is its sequence in the statement or the "nnn" in the case of a "?nnn" form. It is allowed for the same wildcard to occur more than once in the same SQL statement, in which case all instance of that wildcard will be filled in with the same value. Unbound wildcards have a value of NULL.

There is an assortment of sqlite3_bind routines used to assign values to wildcards in a prepared SQL statement. Unbound wildcards are interpreted as NULLs. Bindings are not reset by sqlite3_reset(). But wildcards can be rebound to new values after an sqlite3_reset().

After an SQL statement has been prepared (and optionally bound), it is executed using:

The sqlite3_step() routine return SQLITE_ROW if it is returning a single row of the result set, or SQLITE_DONE if execution has completed, either normally or due to an error. It might also return SQLITE_BUSY if it is unable to open the database file. If the return value is SQLITE_ROW, then the following routines can be used to extract information about that row of the result set:

The sqlite3_column_count() function returns the number of columns in the results set. sqlite3_column_count() can be called at any time after sqlite3_prepare_v2(). sqlite3_data_count() works similarly to sqlite3_column_count() except that it only works following sqlite3_step(). If the previous call to sqlite3_step() returned SQLITE_DONE or an error code, then sqlite3_data_count() will return 0 whereas sqlite3_column_count() will continue to return the number of columns in the result set.

Returned data is examined using the other sqlite3_column_***() functions, all of which take a column number as their second parameter. Columns are zero-indexed from left to right. Note that this is different to parameters, which are indexed starting at one.

The sqlite3_column_type() function returns the datatype for the value in the Nth column. The return value is one of these:

The sqlite3_column_decltype() routine returns text which is the declared type of the column in the CREATE TABLE statement. For an expression, the return type is an empty string. sqlite3_column_name() returns the name of the Nth column. sqlite3_column_bytes() returns the number of bytes in a column that has type BLOB or the number of bytes in a TEXT string with UTF-8 encoding. sqlite3_column_bytes16() returns the same value for BLOBs but for TEXT strings returns the number of bytes in a UTF-16 encoding. sqlite3_column_blob() return BLOB data. sqlite3_column_text() return TEXT data as UTF-8. sqlite3_column_text16() return TEXT data as UTF-16. sqlite3_column_int() return INTEGER data in the host machines native integer format. sqlite3_column_int64() returns 64-bit INTEGER data. Finally, sqlite3_column_double() return floating point data.

It is not necessary to retrieve data in the format specify by sqlite3_column_type(). If a different format is requested, the data is converted automatically.

Data format conversions can invalidate the pointer returned by prior calls to sqlite3_column_blob(), sqlite3_column_text(), and/or sqlite3_column_text16(). Pointers might be invalided in the following cases:

• The initial content is a BLOB and sqlite3_column_text() or sqlite3_column_text16() is called. A zero-terminator might need to be added to the string.

• The initial content is UTF-8 text and sqlite3_column_bytes16() or sqlite3_column_text16() is called. The content must be converted to UTF-16.

• The initial content is UTF-16 text and sqlite3_column_bytes() or sqlite3_column_text() is called. The content must be converted to UTF-8.

Note that conversions between UTF-16be and UTF-16le are always done in place and do not invalidate a prior pointer, though of course the content of the buffer that the prior pointer points to will have been modified. Other kinds of conversion are done in place when it is possible, but sometime it is not possible and in those cases prior pointers are invalidated.

The safest and easiest to remember policy is this: assume that any result from

• sqlite3_column_blob(),

• sqlite3_column_text(), or

• sqlite3_column_text16()

is invalided by subsequent calls to

• sqlite3_column_bytes(),

• sqlite3_column_bytes16(),

• sqlite3_column_text(), or

• sqlite3_column_text16().

This means that you should always call sqlite3_column_bytes() or sqlite3_column_bytes16() before calling sqlite3_column_blob(), sqlite3_column_text(), or sqlite3_column_text16().

2.3 User-defined functions

User defined functions can be created using the following routine:

The nArg parameter specifies the number of arguments to the function. A value of 0 indicates that any number of arguments is allowed. The eTextRep parameter specifies what representation text values are expected to be in for arguments to this function. The value of this parameter should be one of the parameters defined above. SQLite version 3 allows multiple implementations of the same function using different text representations. The database engine chooses the function that minimization the number of text conversions required.

Normal functions specify only xFunc and leave xStep and xFinal set to NULL. Aggregate functions specify xStep and xFinal and leave xFunc set to NULL. There is no separate sqlite3_create_aggregate() API.

The function name is specified in UTF-8. A separate sqlite3_create_function16() API works the same as sqlite_create_function() except that the function name is specified in UTF-16 host byte order.

Notice that the parameters to functions are now pointers to sqlite3_value structures instead of pointers to strings as in SQLite version 2.X. The following routines are used to extract useful information from these "values":

Function implementations use the following APIs to acquire context and to report results:

2.4 User-defined collating sequences

The following routines are used to implement user-defined collating sequences:

The sqlite3_create_collation() function specifies a collating sequence name and a comparison function to implement that collating sequence. The comparison function is only used for comparing text values. The eTextRep parameter is one of SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, or SQLITE_ANY to specify which text representation the comparison function works with. Separate comparison functions can exist for the same collating sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations. The sqlite3_create_collation16() works like sqlite3_create_collation() except that the collation name is specified in UTF-16 host byte order instead of in UTF-8.

The sqlite3_collation_needed() routine registers a callback which the database engine will invoke if it encounters an unknown collating sequence. The callback can lookup an appropriate comparison function and invoke sqlite_3_create_collation() as needed. The fourth parameter to the callback is the name of the collating sequence in UTF-8. For sqlite3_collation_need16() the callback sends the collating sequence name in UTF-16 host byte order.

https://sqlite.org/capi3.html