This is my in-depth SQLite tutorial for C++. This is the first tutorial I am doing on this website. It is really difficult to find a good tutorial for prepared statements with SQLite, and that is what this tutorial will remedy.

SQLite is a really powerful embedded database library. It provides a simple interface, and is a really good introduction to working with databases. It is used in enterprise grade systems, and safety critical systems. It is also used as a file format for some applications. As such it is a really powerful tool for programmers of all systems, and it is important to learn as a tool. The only downside to it is the documentation does not give an example of prepared statements, and it is very verbose about it. Other than that it has some of the best documentation for just getting up and going with it of any open source projects, but it doesn’t demonstrate how to use prepared statements. As such this tutorial will explain how to use prepared statements with SQLite.

A quick note about the examples below is that they are all just snippets of code, not complete programs. If you want a complete program, you can download one here.

Opening and Closing a Connection

The first item to do when working with SQLite is to open a connection to the database. Since SQLite is not a server based database the connection is opened to a file. If the return code from sqlite3_open is anything but zero then an error occurred. When you are all done using the database you need to call sqlite3_close as shown in the sample below.

1
2
3
4
5
6
7
8
9
10
11
// open a connection to the database
sqlite3* db;
int rc = sqlite3_open("test.sqlite", &db);
if(rc){
    // handle error
    fprintf(stderr, "%s\n", sqlite3_errmsg(db));
}

// Close the connection to the database. This is important to protect
// the data integrity.
sqlite3_close(db);

Using sqlite3_exec to Perform Queries

Now to perform queries on the database, sqlite3_exec is called. This function allows one to perform arbitrary SQL commands to the database. It also requires a callback function to handle the data returned by the function. The code below demonstrates creating a table using this method. If you don’t need a callback, you can just pass in NULL for the callback function.

1
2
3
4
5
6
7
8
9
10
11
12
13
const char sql[] =
"CREATE TABLE FOO("
 "ID  INTEGER PRIMARY KEY AUTOINCREMENT"
 "A   INTEGER"
 "B   TEXT"
 "C   REAL);";
// The code below is a function
char *zErrMsg;
int rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
if(rc){
    // error handling, print zErrMsg and free it
    sqlite3_free(zErrMsg);
}

SQLite makes extensive use of function pointers to return data from calls to the database. So when a query is executed on the database that return data from the database, the callback function is called every time a row is returned. You can store the returned data however you want. Personally, I store it in a std::vector of some type, depending on what I want to do with the data. It is also important to note that sqlite returns all data as c style strings. Queries that return data include SELECT and that’s pretty much it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
static int cb(void* notused, int argc, char** argv,
    char** azColNm){
    for(int i = 0; i < argc; i++){
        printf("%s = %s\n", azColNm[i], argv[0]);
    }
    return 0;
}

// The code below is a function
char *zErrMsg;
int rc = sqlite3_exec(db, "SELECT * FROM FOO;", cb, NULL, &zErrMsg);
if(rc){
    // error handling, print zErrMsg and free it
    sqlite3_free(zErrMsg);
}

Using Prepared Statements

Prepared statements are most useful for inserting data into the database. They are typically used to construct large queries to insert into the database. They are basically SQL queries that have a couple of generic parameters.

INSERT INTO FOO(A, B, C) VALUES(?1, ?2, ?3);

Now setting parameters in that query can be a bit of a challenge depending on the type of data you want to insert. To bind parameters to a query you use one of the sqlite3_bind* functions. Also, one needs to create a sqlite3_stmt to hold the statement data. The example below demonstrates the usage of several of the bind parameters with an insert query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sqlite3_stmt* stmt;
rc = sqlite3_prepare_v2(db, sql_insert, -1, &stmt, NULL);
if(rc){
    puts("Failed to prepare statement\n");
}
for(int i = 0; i < 100; i++){
    // Invent some random text to use as data
    std::string str;
    for(int n = 0; n < rand() % 20; n++){
        str += rand() % 96 + 32;
    }
    // bind the params
    sqlite3_bind_int(stmt, 1, i * rand());
    sqlite3_bind_text(stmt, 2, str.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_double(stmt, 3, (double)rand() / (double)rand());
    // run the statement
    sqlite3_step(stmt);
}
sqlite3_finalize(stmt); // Free the statement

Example Source Code

You may download the example source code here. The example includes all of the items explained in this tutorial.