Accessing SQLite in C

In my last article I wrote about accessing a PostgreSQL database in C/C++. In this article, I'm going to discuss performing the same functions in C against an SQLite database. Unlike Postgresql and the supporting libraries, SQLite creates completely self-contained databases that aren't dependant upon a client-server architecture. This feature makes SQLite ideal for use in applications that don't require the complexity of a server, yet can benefit from the flexibility of the SQL language.

As in the previous article, we need a database to work with, and we'll use the same database as we used last time:

create table people (
        id              integer,
        firstname       varchar(20),
        lastname        varchar(20),
        phonenumber     char(10)
);

insert into people (id, firstname, lastname, phonenumber) values
        (1, 'Fred', 'Flintstone', '5055551234');

insert into people (id, firstname, lastname, phonenumber) values
        (2, 'Wilma', 'Flintstone', '5055551234');

insert into people (id, firstname, lastname, phonenumber) values
        (3, 'Barny', 'Rubble', '5055554321');

It's reassuring to see that the identical syntax works with SQLite that worked with Postgresql. In fact, SQLite supports much of the SQL 92 standard.

Assuming that these SQL statements were in a file called create.sql, we would create our database and populate it with a command like:

sqlite3 ljdata.sl3 < create.sql

The SQLite website recommends using the “sl3” extension to signify that we're working with an SQLite version 3 database.

If we examine the resulting file, ljdata.sl3 using the strings command, we see that not only is all of our data there, but so is all of the information needed to create the table structure.

# strings < ljdata.sl3
SQLite format 3
atablepeoplepeople
CREATE TABLE people (
        id              integer,
        firstname       varchar(20),
        lastname        varchar(20),
        phonenumber     char(10)
BarnyRubble5055559999
WilmaFlintstone5055551234
FredFlintstone5055551234

So just like in the previous article, it's time to look at some code.

     1  #include <stdio.h>
     2  #include <sqlite3.h>
     3
     4  int     main(void) {
     5          sqlite3 *conn;
     6          sqlite3_stmt    *res;
     7          int     error = 0;
     8          int     rec_count = 0;
     9          const char      *errMSG;
    10          const char      *tail;
    11
    12          error = sqlite3_open("ljdata.sl3", &conn);
    13          if (error) {
    14                  puts("Can not open database");
    15                  exit(0);
    16          }
    17
    18          error = sqlite3_exec(conn,
    19                  "update people set phonenumber=\'5055559999\' where id=3",
    20                  0, 0, 0);
    21
    22          error = sqlite3_prepare_v2(conn,
    23                  "select lastname,firstname,phonenumber,id from people order by id",
    24                  1000, &res, &tail);
    25
    26          if (error != SQLITE_OK) {
    27                  puts("We did not get any data!");
    28                  exit(0);
    29          }
    30
    31          puts("==========================");
    32
    33          while (sqlite3_step(res) == SQLITE_ROW) {
    34                  printf("%s|", sqlite3_column_text(res, 0));
    35                  printf("%s|", sqlite3_column_text(res, 1));
    36                  printf("%s|", sqlite3_column_text(res, 2));
    37                  printf("%u\n", sqlite3_column_int(res, 3));
    38
    39                  rec_count++;
    40          }
    41
    42          puts("==========================");
    43          printf("We received %d records.\n", rec_count);
    44
    45          sqlite3_finalize(res);
    46
    47          sqlite3_close(conn);
    48
    49          return 0;
    50  }

Here, I've tried to reproduce all of the functionality from the previous article's program, but as you'll see, this pointed out an important difference between the two libraries.

Compiling this program simply requires that we link against the sqlite3 library like so:

gcc ./db.c -o db -lsqlite3

Lines 1-10 are just boilerplate declarations and definitions. I declared main as main(void) because this is a demonstration and isn't meant to accept any command-line parameters.

In lines 12-16, we start talking to the database. Here we see that I've opened the database file, ljdata.sl3 in the current directory. We pass in a pointer to the sqlite3 structure pointer so that the sqlite3_open function can allocate memory and populate the structure for us. When the function returns, this variable contains a pointer to this structure. This function also returns an error value that we test in line 13.

Lines 18-20 perform a simple database update, as in the previous article. Here sqlite3_exec() receives the conn variable that we discussed as part of the sqlite3_open() function, and of course, an SQL statement to execute. But you'll also notice 3 additional parameters. The third parameter is a pointer to a callback function that would be called for each row that that SQL query returned. The fourth parameter is a pointer to what would be the first parameter of the callback, if we chose to use that feature. Being able to use a callback to process the rows returned by a query is nice, but it's overkill for this particular query, which only performs an update on the database.

The last parameter to the sqlite3_exec() function is a cheat on my part. This parameter is a pointer to a char array and is meant to contain an error message, should our query generate an error. Again, this is a simple query and I don't expect any errors. In production code, I'd be a bit more defensive.

Things get a bit more interesting in lines 22-29. These lines begin the prepare, execute, loop, close paradigm that almost every other database access library implements. As an aside, it's interesting to note that the sqlite3_exec() function is actually a convenience function implemented in terms of the functions we'll be talking about in the remainder of this program.

So, we start out by preparing our query for execution with a call to sqlite3_prepare_v2() in line 22. This causes the SQLite library to compile our query into a byte-code for later execution; our query won't be run at this time.

Intuitively, you might expect this function to be called something like sqlite3_prepare(), and this points out one of the differences between SQLite and most other database libraries. The API has undergone some changes over the years. Rather than break existing code with subtle changes to how the library behaves, the SQLite developers have simply added to the API while maintaining backward compatibility. So, there actually is an sqlite3_prepare() function, but it behaves slightly differently than the “v2” function. The v2 function is the recommended function for new code.

Our call to sqlite3_prepare_v2() accepts the connection handle and an SQL query string, just as we expect. However, the sqlite3_prepare_v2() call can be used to prepare multiple SQL statements in the same query string, so we pass in the maximum length of our query string much like the strncmp() function. Next we pass in a pointer to sqlite3_stmt pointer. This pointer will contain a “statement” object that we will use later to get the results of our query, just as we did in the Postgresql version.

Finally, we pass in a “tail” pointer that sqlite3_prepare_v2() will use to keep it's place within our query in the event that our query string contains more than one SQL statement. This sure beats clobbering the query string like strtok() does.

In lines 26-29, we check for errors and bail ungraciously at the first hint of problems.

Line 31 points out a subtle difference between the Postgresql version and the SQLite version of this program. In the Postgresql version, we were able to determine how many records our query had retrieved, without having to loop over the dataset, as we will in the SQLite version. It turns out that the SQLite version doesn't actually retrieve all of the data at once. Instead, we have what resembles a cursor that we have to loop over to get our results, one row at a time.

We loop over this “cursor” in line 33 with repeated calls to sqlite3_step(); Each call to the sqlite3_step() function returns the SQLITE_ROW constant until we've reached the end of the dataset. When this occurs, our loop terminates.

Inside the while loop, we see another deviation from other common database access libraries. Here we see type-specific “column accessor” functions. On line 34, see a call to sqlite3_column_text(). This function takes our statement object, as well as an integer that designates which column we want to retrieve. As you can see, the first 3 columns, 0-2 are text, char, or varchar values. In line 37, we see an almost identical call to sqlite_column_int() in order to get the integer-valued id field from our dataset.

We count the records as we retrieve them in line 39 so that we can reproduce all of the functionality of the previous article's program.

Lines 42-43 finish off the report and print the record count.

The call to sqlite3_finalize() on line 45 returns any resources that were in use by our statement object. Finally, we close our database connection on line 47 with a call to sqlite3_close().

Miraculously, this program weighs in at almost exactly the same number of lines as the equivalent Postgresql version!

And, just like last time, the output looks like this.

# ./db
==========================
Flintstone|Fred|5055551234|1
Flintstone|Wilma|5055551234|2
Rubble|Barny|5055559999|3
==========================
We received 3 records.

I found the Postgresql library to be a bit more approachable than the SQLite library. The Postgresql website had sample code. The SQLite website had a lot of very well written documentation, but because of the changes in the API, it was a bit daunting at first. But as you can see, neither library is difficult to use. Each library solves particular database access problems that the other library might not solve as well. For large databases where the client-server model makes sense, the Postgresql library is the way to go. On the other hand, for client-side, or self-contained databases, SQLite is really neat. Either way, it's fun for the whole family!