|
An Introduction To The SQLite C/C++ Interface
This article provides an overview and roadmap to the C/C++ interface to SQLite.
Early versions of SQLite were very easy to learn since they only supported 5 C/C++ interfaces. But as SQLite has grown in capability new C/C++ interfaces have been added so that now there are over 150 distinct APIs. This can be overwhelming to a new programmer. Fortunately, most of the C/C++ interfaces in SQLite are very specialized and never need to be used. Despite having so many entry points, the core API is still relatively simple and easy to code to. This article aims to provide all of the background information needed to easily understand how SQLite works.
A separate document, The SQLite C/C++ Interface, provides detailed specifications for all of the various C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, that document should be used as a reference guide. This article is intended as introduction only and is neither a complete nor authoritative reference for the SQLite API.
1.0 Core Objects And Interfaces
The principal task of an SQL database engine is to evaluate statements of SQL. In order to accomplish this purpose, the developer needs to know about two objects:
- The database connection object: sqlite3
- The prepared statement object: sqlite3_stmt
Strictly speaking, the prepared statement object is not required since the convenience wrapper interfaces, sqlite3_exec or sqlite3_get_table, can be used and these convenience wrappers encapsulate and hide the prepared statement object. Nevertheless, an understanding of prepared statements is needed to make full use of SQLite.
The database connection and prepared statement objects are controlled by a small set of C/C++ interface routine listed below.
The six C/C++ interface routines and two objects listed above form the core functionality of SQLite. The developer who understands them will have a good foundation for using SQLite.
Note that the list of routines is conceptual rather than actual. Many of these routines come in multiple versions. For example, the list above shows a single routine named sqlite3_open() when in fact there are three separate routines that accomplish the same thing in slightly different ways: sqlite3_open(), sqlite3_open16() and sqlite3_open_v2(). The list mentions sqlite3_column() when in fact no such routine exists. The "sqlite3_column()" shown in the list is place holders for an entire families of routines to be used for extracting column data in various datatypes.
Here is a summary of what the core interfaces do:
| sqlite3_open() | This routine opens a connection to an SQLite database file and returns a database connection object. This is often the first SQLite API call that an application makes and is a prerequisite for most other SQLite APIs. Many SQLite interfaces require a pointer to the database connection object as their first parameter and can be thought of as methods on the database connection object. This routine is the constructor for the database connection object. |
| sqlite3_prepare() |
This routine
converts SQL text into a prepared statement object and returns a pointer
to that object. This interface requires a database connection pointer
created by a prior call to sqlite3_open() and a text string containing
the SQL statement to be prepared. This API does not actually evaluate
the SQL statement. It merely prepares the SQL statement for evaluation.
Note that the use of sqlite3_prepare() is not recommended for new applications. The alternative routine sqlite3_prepare_v2() should be used instead. |
| sqlite3_step() | This routine is used to evaluate a prepared statement that has been previously created by the sqlite3_prepare() interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to sqlite3_step(). |
| sqlite3_column() | This routine returns a single column from the current row of a result set for a prepared statement that is being evaluated by sqlite3_step(). Each time sqlite3_step() stops with a new result set row, this routine can be called multiple times to find the values of all columns in that row. As noted above, there really is no such thing as a "sqlite3_column()" function in the SQLite API. Instead, what we here call "sqlite3_column()" is really a place-holder for an entire family of functions that return a value from the result set in various data types. There are also routines in this family that return the size of the result (if it is a string or BLOB) and the number of columns in the result set. |
| sqlite3_finalize() | This routine destroys a prepared statement created by a prior call to sqlite3_prepare(). Every prepared statement must be destroyed using a call to this routine in order to avoid memory leaks. |
| sqlite3_close() | This routine closes a database connection previously opened by a call to sqlite3_open(). All prepared statements associated with the connection should be finalized prior to closing the connection. |
1.1 Typical Usage Of Core Routines And Objects
An application that wants to use SQLite will typically use sqlite3_open() to create a single database connection during initialization. Note that sqlite3_open() can be used to either open existing database files or to create and open new database files. While many applications use only a single database connection, there is no reason why an application cannot call sqlite3_open() multiple times in order to open multiple database connections - either to the same database or to different databases. Sometimes a multi-threaded application will create separate database connections for each threads. Note too that is not necessary to open separate database connections in order to access two or more databases. A single database connection can be made to access two or more databases at one time using the ATTACH SQL command.
Many applications destroy their database connections using calls to sqlite3_close() at shutdown. Or, for example, an application might open database connections in response to a File->Open menu action and then destroy the corresponding database connection in response to the File->Close menu.
To run an SQL statement, the application follows these steps:
- Create a prepared statement using sqlite3_prepare().
- Evaluate the prepared statement by calling sqlite3_step() one or more times.
- For queries, extract results by calling sqlite3_column() in between two calls to sqlite3_step().
- Destroy the prepared statement using sqlite3_finalize().
The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is just ornamentation and detail.
2.0 Convenience Wrappers Around Core Routines
The sqlite3_exec() interface is a convenience wrapper that carries out all four of the above steps with a single function call. A callback function passed into sqlite3_exec() is used to process each row of the result set. The sqlite3_get_table() is another convenience wrapper that does all four of the above steps. The sqlite3_get_table() interface differs from sqlite3_exec() in that it stores the results of queries in heap memory rather than invoking a callback.
It is important to realize that neither sqlite3_exec() nor sqlite3_get_table() do anything that cannot be accomplished using the core routines. In fact, these wrappers are implemented purely in terms of the core routines.
3.0 Binding Parameters and Reusing Prepared Statements
In prior discussion, it was assumed that each SQL statement is prepared onces, evaluated, then destroyed. However, the SQLite allows the same prepared statement to evaluated multiple times. These is accomplished using the following routines:
After a prepared statement has been evaluated by one or more calls to sqlite3_step(), it can be reset in order to be evaluated again by a call to sqlite3_reset(). Using sqlite3_reset() on an existing prepared statement rather creating a new prepared statement avoids unnecessary calls to sqlite3_prepare(). In many SQL statements, the time needed to run sqlite3_prepare() equals or exceeds the time needed by sqlite3_step(). So avoiding calls to sqlite3_prepare() can result in a significant performance improvement.
Usually, though, it is not useful to evaluate exactly the same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times though with different values to insert. To accommodate this kind of flexibility, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values.
In SQLite, wherever it is valid to include a string literal, one can use a parameter in one of the following forms:
- ?
- ?NNN
- :AAA
- $AAA
- @AAA
In the examples above, NNN is an integer value and AAA is an identifier. A parameter initially has a value of NULL. Prior to calling sqlite3_step() for the first time or immediately after sqlite3_reset(), the application can invoke one of the sqlite3_bind() interfaces to attach values to the parameters. Each call to sqlite3_bind() overrides prior bindings on the same parameter.
An application is allowed to prepare multiple SQL statements in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding prepared statements.
4.0 Extending SQLite
SQLite includes interface that can be used to extend its functionality. Such routines include:
The sqlite3_create_collation() interface is used to create new collating sequences for sorting text. The sqlite3_create_module() interface is used register new virtual table implementations.
The sqlite3_create_function() interface creates new SQL functions - either scalar or aggregate. The new function implementation typically makes use of the following additional interfaces:
All of the built-in SQL functions of SQLite are created using exactly these same interfaces. Refer to the SQLite source code, and in particular the date.c and func.c source files for examples.
5.0 Other Interfaces
This article only mentions the foundational SQLite interfaces. The SQLite library includes many other APIs implementing useful features that are not described here. A complete list of functions that form the SQLite application program interface is found at the C/C++ Interface Specification. Refer to that document for complete and authoritative information about all SQLite interfaces.
*** DRAFT ***