Using SQLite in C programs

Intro

SQLite is a free local database with SQL interface. I've written an introduction for the basics.

For interfacing with C, SQLite offers two files: a C file (module) containing all functions and a header file. They can either be included with the own files and compiled in or used as a shared library. Distributions should ship a -devel package for this. The version 2 is deprecated and should be replaced with version 3.

The most important data structures are sqlite3 for the database connection (similar to a file handle, it is opened, used for reading and writing and finally closed) and sqlite3_stmt for the queries. Queries are prepared, executed ("step"ed) and finalized.

The Database connection

A database is stored in a single binary file. Temporary data can be held solely in memory by using the special file name :memory:. The latter allows using SQLite for managing data, e.g. sorting.

The following program opens a database and closes it again. The return value is checked and an error message is printed if something went wrong.

#include <stdio.h>      // printf
#include <sqlite3.h>    // SQLite header (from /usr/include)

int main()
{
    sqlite3 *db;        // database connection
    int rc;             // return code
    char *errmsg;       // pointer to an error string

    /*
     * open SQLite database file test.db
     * use ":memory:" to use an in-memory database
     */
    rc = sqlite3_open(":memory:", &db);
    if (rc != SQLITE_OK) {
        printf("ERROR opening SQLite DB in memory: %s\n", sqlite3_errmsg(db));
        goto out;
    }
    printf("opened SQLite handle successfully.\n");

    /* use the database... */

out:
    /*
     * close SQLite database
     */
    sqlite3_close(db);
    printf("database closed.\n");
}

This can be compiled using (provided, that sqlite3-devel is installed):

$gcc -O0 -g openclose.c  -lsqlite3 -o openclose

A Makefile for this could be (this assumes, that the above file is saved as openclose.c, that pkg-config and sqlite3-devel are installed and uses the build-in rules to create executables from C files):

CFLAGS=$(shell pkg-config --cflags sqlite3) -O0 -g
LDLIBS=$(shell pkg-config --libs sqlite3)

default : openclose

debug : 
    @echo CFLAGS = $(CFLAGS)
    @echo LDLIBS = $(LDLIBS)

The classic way

The basic working is always:

  • Prepare a query. This is usually a string (e.g. created with asprintf()) that can optionally contain placeholders for binding values. This generates a statement.
  • Execute the statement, either once (for creating tables, inserting, or updating) or until all resulting rows are read.
  • Free the ressources by finalizing the statement.

Executing a static query

The following example creates a table. The query is a static string. This piece of code can be placed between sqlite3_open() and sqlite3_close() in the above example.

sqlite3_stmt *stmt;                                                                         /* 1 */

sqlite3_prepare_v2(db, "CREATE TABLE demo (name TEXT, age INTEGER);", -1, &stmt, NULL);     /* 2 */

rc = sqlite3_step(stmt);                                                                    /* 3 */
if (rc != SQLITE_DONE) {
    printf("ERROR inserting data: %s\n", sqlite3_errmsg(db));
    goto out;
}

sqlite3_finalize(stmt);                                                                     /* 4 */
  1. A variable for the statement is allocated.
  2. The current version of the prepare function is sqlite3_prepare_v2().
    • The first parameter is the database connection db.
    • The second parameter is the query (UTF-8 string).
    • The third parameter should be the length of the query but can be -1 for zero-terminated C strings.
    • For stmt, the address must be given because a pointer is returned therein.
    • The final parameter is not used here, please consult the documentation.
  3. The function sqlite3_step executes the prepared statement. Here, no returning rows are expected, only the success is tested.
  4. Free the ressources of the prepared statement.

Execute a dynamic query

The next example uses asprintf() to dynamically create a query. This can be done with simple and trusted input. For content provided by users, the binding of values should be used as shown below.

char *query = NULL;

asprintf(&query, "insert into demo (name, age) values ('%s', %d);", "Tom", 20);         /* 1 */

sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);                              /* 2 */

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
    printf("ERROR inserting data: %s\n", sqlite3_errmsg(db));
    goto out;
}

sqlite3_finalize(stmt);
free(query);                                                                            /* 3 */
  1. The function asprintf() allocates a buffer large enough to hold the created string.
  2. The statement is now prepared from the query string. The length can be given (it should be faster).
  3. Don't forget to free the string allocated by asprintf().

Once again: if a user provides vile input (i.e. "SQL injection"), the database is in danger.

Binding values

The following example shows how to use place-holders and bind values to them.

sqlite3_prepare_v2(db, "insert into demo (name, age) values (?1, ?2);", -1, &stmt, NULL);       /* 1 */

sqlite3_bind_text(stmt, 1, "Susan", -1, SQLITE_STATIC);                                         /* 2 */
sqlite3_bind_int(stmt, 2, 21);                                                                  /* 3 */

rc = sqlite3_step(stmt); 
if (rc != SQLITE_DONE) {
    printf("ERROR inserting data: %s\n", sqlite3_errmsg(db));
    goto out;
}

sqlite3_finalize(stmt);
  1. Instead of strings and integers, use ?1 etc. as place-holders. Others are possible, see the documentation.
  2. Bind the string "Susan" to ?1. As this is a static string, tell SQLite not to free it. If it is an allocated string, a function can be given to free it after the statement is processed.
  3. Bind an integer value (no freeing required).

The rest is as before.

Query data with the classic method

If the prepared statement returns resulting rows, the function sqlite3_step() returns the value SQLITE_ROW and can be called until no more data is available. The columns can be accessed by index:

sqlite3_prepare_v2(db, "select distinct name, age from demo where age > ? order by 2,1;", -1,
        &stmt, NULL);

sqlite3_bind_int(stmt, 1, 16);                                                                  /* 1 */

while ( (rc = sqlite3_step(stmt)) == SQLITE_ROW) {                                              /* 2 */
    printf("%s is %d years old\n", sqlite3_column_text(stmt, 0), sqlite3_column_int(stmt, 1));  /* 3 */
}

sqlite3_finalize(stmt);
  1. The prepared query contains ?1 in the WHERE clause. Bind an integer value.
  2. Loop while more data is available.
  3. The data is accessed by column index. Strings are given as const char * and can be directly given to functions like printf().

One-Step Query Execution Interface

The convenience function sqlite3_exec() combines the above steps. However, the binding of values appears not to be implemented. For user input, use either very careful masking or the classic way. The following example shows how to execute two SQL queries in one call. Resulting data is given to a callback function.

char *errmsg;
int callback(void *arg, int argc, char **argv, char **colName) {                                /* 1 */
    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\t", colName[i], argv[i] ?  : "NULL");
    }
    printf("\n");
    return 0;
}

rc = sqlite3_exec(db,                                                                           /* 2 */
    "select count(*), avg(age) from demo; select distinct name, age from demo order by 1,2;", 
    callback, NULL, &errmsg);

if (errmsg != NULL) {                                                                           /* 3 */
    printf("Error in sqlite3_exec: %s\n", errmsg);
    sqlite3_free(errmsg);
}
  1. Provide a callback function. It can be given arguments to distinguish multiple uses. The query results are provided similar to main() with argc and argv plus the names of the columns in a fourth string array. Here, the example function just prints key=value.
  2. Execute one or multiple queries. The callback function and its arguments (here: NULL) and a char pointer for an error message are the parameters.
  3. If an error occured, errmsg is allocated and non-NULL. In this case, it should be freed using the function sqlite2_free().

More examples

More examples are in this source code archive.

social