Accessing SQLite in C

 in

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!

______________________

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

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Перевёл Себе

Валерий's picture

Перевёл Себе на блог http://blog.wel.org.ua/index.php/2010/08/программирование-на-с-для-sqlite/

SQLite

akm's picture

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.

just when I need it

Caleb Cushing (xenoterracide)'s picture

I was looking for this last night... so you posted just in time for me to get something done with C and sqlite. haven't tried it yet but hopefully it'll be enough of what I need.

thanx for the article.

Anonymous's picture

thanx for the article. sqlite is very good at what it does.

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!

a minor issue about sqlite3_prepare_v2()

Mengjun Xie's picture

Hello Mike,

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:

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

I guess on your machine the code runs perfectly fine. However, it might be better to put some reasonable number for 'nByte' to make the code run across different machines. Thanks!

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState