Database Independent Development in C

The libdbi library provides a feature for C programmers that has long been missing. The interface is clean and the framework for adding new drivers is relatively straightforward.

Database Independent Development in C

libdbi brings to the UNIX C/C++ developer a functionality that has long been available to other programmers. A single binary may now be made database independent, without a tedious ODBC installation.

Windows programmers have long been able to free themselves from the bonds of a single database by using ODBC. With the porting of ODBC to UNIX, and the adoption of OS X (UNIX in six colors) by Apple, database independence becomes available to nearly all application developers.

That’s the theory, at least. Unfortunately, configuring ODBC requires administrative privileges, and configuration is decidedly non-trivial. If you’re like me, and most of the applications you write are written to be run in a web hosting environment, you can count on not having administrative access to the server.

Installing

At the time of this writing, libdbi supports PostgreSQL, MySQL, SQLite, mSQL and Oracle databases. The very first prerequisite is that you must install the client libraries for every database engine that you want to make available. In my case I installed PostgreSQL, MySQL and SQLite, the three database engines which I use the most.

libdbi comes in two parts. The first, libdbi, provides the basic library that you’ll link your programs against. It contains the framework that figures out which functions to call in the database client libraries. You can download it from: http://libdbi.sourceforge.net

The second part is libdbi-drivers. This package builds to shared objects, one for each database, which in turn link to their respective database client libraries. This package can be downloaded from: http://libdbi-drivers.sourceforge.net

libdbi must be configured and installed first. It’s a fairly straightforward affair. All of the options are standard configure, except for the –with-ltdl and –with-shlib-ext options. If your system has a problem with the dlopen function call (which you won’t know until you’ve tried to run a libdbi program), you’ll need to enable the –with-ltdl flag, which uses a dlopen call provided by libtool. If you want the drivers to have an extension besides the default of .so, you’ll need to specify that with the –with-shlib-ext option.

Installing libdbi-drivers

libdbi-drivers is a little more interesting to install. You must explicitly tell configure which drivers you want to include using the usual round of –with-driver flags. Every UNIX distribution seems to install these drivers in slightly different places though, so some special flags have been provided. My OpenBSD machine, for instance, places its PostgreSQL includes under /usr/local/include/postgresql and the client libraries in /usr/local/lib. The –with-driver-incdir and –with-driver-libdir options let you specify the locations of includes and client libraries if that is necessary. Generally the configure script is smart enough to look under both the /usr and /usr/local directory trees, and this kind of complication isn’t usually needed.

In the current pre-1.0 version of the driver distribution, there is an undocumented step which you must take. The libraries are installed in /usr/local/lib/dbd, with the full version number as part of the file extension, just as other shared libraries use. You’ll need to create links in that folder ending in the .so file extension and pointing to the actual libraries.

In case you haven’t realized it, this all means that you’ve got a lot of shared libraries kicking around. Rather than just the necessary client libraries, you now also have all of the shared objects for the libdbi drivers, as well as the libdbi library itself. It’s a viable tradeoff, but it’s easy to see how problems could develop down the road.

{mospagebreak title=Making the Connection}

The library is simplicity itself to use, somewhat reminiscent of the PEAR::DB classes in PHP. The two basic data types are dbi_conn, to represent a database connection, and dbi_result, to represent a database result set. Both are typedefs for a void*.

When your program is first initialized, no database drivers are loaded. It will happily execute queries and send you back empty result sets. You initialize drivers in two steps.

First, a call to dbi_initialize() will enumerate the available drivers to the library. The argument can technically be NULL, causing the library to look in the default location. My experience has been, however, that you need to explicitly specify the folders where the drivers reside. In the default installation, or at least on my system, that is /usr/local/lib/dbd. The return value will be the number of drivers found. If 0 drivers are found, check that you specified the correct folder. If the folder is correct, make sure that you created the links to the actual shared libraries in the folder.

Once drivers have been initialized, a database object is created by calling dbi_conn_new(). The argument is the character string that describes the driver, and corresponds to the folder names in the libdbi-drivers/drivers folder. The return object is a uninitialized connection object, waiting for you to supply the connection parameters.

Parameters are set by calling dbi_conn_set_option() with the connection object, the parameter, and the parameter’s value. Common parameter names are host, username, password and dbname. For the sqlite driver, the first three are omitted and sqlite_dbdir is used instead. Examples are provided below which should clarify the parameters.

Finally, a connection is opened with dbi_conn_connect(), with the now initialized connection object as the only parameter.

{mospagebreak title=Getting Results}

Once a connection has been established, dbi_conn_query() lets you make a query into the database. dbi_conn_query takes the connection as the first argument and a printf formatted SQL statement as its second argument. The remainder is the standard variable argument list. It is worth noting that at this time the arguments are in no way run through any kind of string escaping function. It’s the duty of the programmer to escape any input. The native escaping function of the database library can be obtained with a little work, although at times I have written my own string escaping function.

The return value from dbi_conn_query() is a dbi_result object. You cycle through the result set by using the dbi_result_next_row() function, which takes the result set as its only argument. This function must be called at least once before any actual column results can be extracted. This function returns 0 if there are no more rows in the result set.

To get the values of individual columns, there are a variety of dbi_result_get_datatype functions which take the dbi_result object and the column name as parameters. The most common are dbi_result_get_long and dbi_result_get_string, but they exist for every common data type, including datetime columns (which return a time_t).

Finally, when you are done with the result set and the connection, there are a few cleanup functions. dbi_result_free() frees up the memory used by the result set. dbi_conn_close() will close the database connection, and dbi_shutdown() unloads the database drivers that have been loaded.

{mospagebreak title=An Example}

This is a very simple utility that I created to query the user list for an application I’ve been working on. Most applications will be a little more complex.

#include <stdio.h> #include <dbi/dbi.h>

int main() {

 dbi_conn db;  dbi_result result;

 unsigned long user_id;  const char* name;  const char* email;  int drivercount;

 drivercount = dbi_initialize(“/usr/local/lib/dbd”);  printf(“Loaded %d driversn”, drivercount);  db = dbi_conn_new(“mysql”);

 dbi_conn_set_option(db, “host”, “localhost”);  dbi_conn_set_option(db, “username”, “user”);  dbi_conn_set_option(db, “password”, “secret”);  dbi_conn_set_option(db, “dbname”, “bigapp”);

 dbi_conn_connect(db);  result = dbi_conn_query(db, “SELECT user_id, name, email FROM author”);    while (dbi_result_next_row(result)) {   user_id = dbi_result_get_ulong(result, “user_id”);   name = dbi_result_get_string(result, “name”);   email = dbi_result_get_string(result, “email”);   printf(“%2ld) %s <%s>n”, user_id, name, email);  }

 dbi_result_free(result);  dbi_conn_close(db);  dbi_shutdown();

 return 0;

}

On my installation, providing that I called this userlist.c, my command line would look like this:

cc userlist.c -o userlist -I/usr/local/include -L/usr/local/lib -ldbi

As you can see, this program is blissfully short. It’s so short in fact that it makes C a very good candidate for handling some of the routine daily reporting work that every IT department has to suffer through. Generate some HTML output instead of the plain text that I showed and you start to put out very nice looking nightly reports. In later articles I’ll also discuss the use of libraries for generating PDF output. With that under your belt you’ll have a reporting tool that can rival what commercial solutions can provide.

{mospagebreak title=Extending libdbi}

The mechanism for adding a new driver is very straight forward. In addition to the detailed guide, the full source code of the other drivers is present, which gives you a good chance to see how others have done it before.

There are a couple of obvious candidates for inclusion. The FreeTDS library would be good for adding Microsoft SQL Server and Sybase support. The Firebird and Interbase client libraries are also good candidates. With these added to your tool kit there won’t be much that can stand in the way.

Unfortunately there really isn’t room for a full example of a driver port in this article. They’re rather long bits of code.

Conclusions

The libdbi library provides a feature for C programmers that has long been missing. Writing one program that can make use of multiple databases used to mean either using ODBC, with its inherent complications, or tying yourself to bulky technology like ADO or the Borland Database Engine. None of these solutions was a good answer for UNIX programmers, especially UNIX programmers deploying CGI programs on a hosted server.

The libdbi interface is clean, and the list of supported databases, although small, covers most of the needs of open source developers. The relatively straightforward framework for adding new drivers means that it’s not an unreasonable task to add support for databases that aren’t already included, provided client libraries and headers are available.

I’ll definitely be using libdbi in future products, both for CGI and desktop applications. If the library shows up well in production, I hope to make it one of my most frequently used tools.

[gp-comments width="770" linklove="off" ]

chat