Table of Contents for

Using SQLite

Database connections are allocated and established with one of the sqlite3_open_xxx() commands. These pass back a database connection in the form of an sqlite3 data structure. There are three variants:

For new code, it is recommended that you use the call sqlite3_open_v2(). The newer call allows more control over how the database is opened and processed.

The use of the double pointer may be a bit confusing at first, but the idea behind it is simple enough. The pointer-to-a-pointer is really nothing more than a pointer that is passed by reference. This allows the function call to modify the pointer that is passed in. For example:

sqlite3    *db = NULL;
rc = sqlite3_open_v2( "database.sqlite3", &db, SQLITE_OPEN_READWRITE, NULL );
/* hopefully, db now points to a valid sqlite3 structure */ 

Note that db is an sqlite3 pointer (sqlite3*), not an actual sqlite3 structure. When we call sqlite3_open_xxx() and pass in the pointer reference, the open function will allocate a new sqlite3 data structure, initialize it, and set our pointer to point to it.

This approach, including the use of a pointer reference, is a common theme in the SQLite APIs that are used to create or initialize something. They all basically work the same way and, once you get the hang of them, they are pretty straightforward and easy to use.

There is no standard file extension for an SQLite3 database file, although .sqlite3, .db, and .db3 are popular choices. The extension .sdb should be avoided, as this extension has special meaning on some Microsoft Windows platforms, and may suffer from significantly slower I/O performance.

The string encoding used by a database file is determined by the function that is used to create the file. Using sqlite3_open() or sqlite3_open_v2() will result in a database with the default UTF-8 encoding. If sqlite3_open16() is used to create a database, the default string encoding will be UTF-16 in the native byte order of the machine. You can override the default string encoding with the SQL command PRAGMA encoding. See encoding in Appendix F for more details.

In addition to recognizing standard filenames, SQLite recognizes a few specialized filename strings. If the given filename is a NULL pointer or an empty string (""), then an anonymous, temporary, on-disk database is created. An anonymous database can only be accessed through the database connection that created it. Each call will create a new, unique database instance. Like all temporary items, this database will be destroyed when the connection is closed.

If the filename :memory: is used, then a temporary, in-memory database is created. In-memory databases live in the database cache and have no backing store. This style of database is extremely fast, but requires sufficient memory to hold the entire database image in memory. As with anonymous databases, each open call will create a new, unique, in-memory database, making it impossible for more than one database connection to access a given in-memory database.

In-memory databases make great structured caches. It is not possible to directly image an in-memory database to disk, but you can copy the contents of an in-memory database to disk (or disk to memory) using the database backup API. See the section sqlite3_backup_init() in Appendix G for more details.

To close and release a database connection, call sqlite3_close().

Any pointer returned by a call to sqlite3_open_xxx(), including a NULL pointer, can be passed to sqlite3_close(). This function verifies there are no outstanding changes to the database, then closes the file and frees the sqlite3 data structure. If the database still has nonfinalized statements, the SQLITE_BUSY error will be returned. In that case, you need to correct the problem and call sqlite3_close() again.

In most cases, sqlite3_open_xxx() will return a pointer to an sqlite3 structure, even when the return code indicates a problem. This allows the caller to retrieve an error message with sqlite3_errmsg(). (See Result Codes and Error Codes.) In these situations, you must still call sqlite3_close() to free the sqlite3 structure.

Here is the outline of a program that opens a database, performs some operations, and then closes it. Most of the other examples in this chapter will build from this example by inserting code into the middle:

#include "sqlite3.h"
#include <stdlib.h>

int main( int argc, char **argv )
{
    char            *file = ""; /* default to temp db */
    sqlite3         *db = NULL;
    int             rc = 0;

    if ( argc > 1 )
        file = argv[1];

    sqlite3_initialize( );
    rc = sqlite3_open_v2( file, &db, SQLITE_OPEN_READWRITE | 
                                     SQLITE_OPEN_CREATE, NULL );
    if ( rc != SQLITE_OK) {
        sqlite3_close( db );
        exit( -1 );
    }

    /*  perform database operations  */

    sqlite3_close( db );
    sqlite3_shutdown( );
}

The default filename is an empty string. If passed to sqlite3_open_xxx(), this will result in a temporary, on-disk database that will be deleted as soon as the database connection is closed. If at least one argument is given, the first argument will be used as a filename. If the database does not exist, it will be created and then opened for read/write access. It is then immediately closed.

If this example is run using a new filename, it will not create a valid database file. The SQLite library delays writing the database header until some actual data operation is performed. This “lazy” initialization gives an application the chance to adjust any relevant pragmas, such as the text encoding, page size, and database file format, before the database file is fully created.