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:

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!

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!

______________________

Mike Diehl is a freelance Computer Nerd specializing in Linux administration, programing, and VoIP. Mike lives in Albuquerque, NM. with his wife and 3 sons. He can be reached at mdiehl@diehlnet.com

SQLite is a very good stand-alone database. There is also a very convenient GUI tool for creating and manipulating the initial database. It is called SQLite Manager and is an add-in to Firefox browser.

i second the above post about the segfault.
as mengjun said, the problem is with the nByte parameter of sqlite3_prepare_v2(). i found out that if you omit the sqlite3_exec() call, it doesn't segfault. this is a libsqlite3 problem (bug?) - your code is ok according to the lib's docs.

according to the documentation, a nByte positive value equal to strlen(statement) should be passed for minor performance gains or a negative value if you want the library to determine itself the statement's length, losing the aforementioned gain; although this may not be a problem, since you have the overhead of str(n)cpy()/strlen() calls you might make before the sqlite3_exec() if you don't go the hard-coded way.

so, by passing -1 as nByte there's no segfault.

again, thanx for the provided code+article. i read your postgresql article as well and it was very interesting!

Your article is very good for SQLite beginner like me. I really enjoy it. After reading it, I cannot help repeating what you did. However, I found the program, which is copied from the code on this page, always runs into a segmentation fault. The function that causes segfault is sqlite3_prepare_v2. I found the value '1000' for the third argument of sqlite3_prepare_v2() is the root of the problem. After I change the value to the real length of the sql query statement, the problem is gone. (The problem also vanishes if I replace the sql string literal with a char array and set the third argument as the string length.) According to the "SQLite C Interface (http://www.sqlite.org/c3ref/prepare.html)", the function prototype of sqlite3_prepare_v2 is as follows: