Writing Apache Modules with Perl and C By:   Lincoln Stein and Doug MacEachern Published:   O'Reilly & Associates, Inc.  - March 1999 Copyright © 1999 by O'Reilly & Associates, Inc.

Chapter 5 - Maintaining State / Storing State Information in SQL Databases
Using DBI

The DBI interface provides methods for opening SQL databases, sending queries to the opened database, and reading the answers returned by those queries.

To open a database, you call DBI->connect() with the "data source name," a string that tells the database driver where the database is located. If the database requires a username and password for access, you can pass that information in the connect() call as well. The format of the data source name is DBMS-specific. For a MySQL database, it looks like this:

"dbi:mysql:$database:$hostname:$port"

All MySQL data sources begin with "dbi:mysql". They are followed by the name of the database, and, optionally, by the name and port of the remote host on which the DBMS is running. If the hostname and port are omitted, the driver defaults to using a standard port on the local host. To connect to a database named www on the local host using the username games and the password grok, you'd make this call:

$dbh = DBI->connect('dbi:mysql:www', 'games', 'grok');

If successful, connect() returns a database handle, $dbh, which is used for subsequent communication with the database. The connect() method also accepts an optional fourth argument which consists of a hash reference of parameter name=value pairs. These control a variety of database options, such as whether to automatically commit all changes made to the database. The only option that we'll use in the examples that follow is PrintError, which when set to false, suppresses the printing of unwanted database warnings to the server error log.

The database handle has several methods, the most important of which are do(), prepare(), and errstr(). do() is used to execute SQL statements which do not return a list of records, such as INSERT, DELETE, UPDATE, or CREATE. If the operation is successful, do() returns a count of the number of rows modified. For example, the following query sets the GAMENO field of all sessions to 1 and returns the number of rows affected:

$count = $dbh->do('UPDATE hangman SET GAMENO=1');
die $dbh->errstr unless defined $count;

If the database encountered an error while processing the statement (for example, the SQL contained a syntax error), it will return undef. The errstr() method can be used to retrieve an informative error message from the driver.

SELECT queries can return a potentially large number of records, often more than will fit into memory at once. For this reason, the results from SELECT queries are returned in the form of statement handle objects. You then call the statement handle's fetch() method repeatedly to retrieve each row of the result.

Here's an example of retrieving the session_id and WORD fields from each session in the hangman database:

$sth = $dbh->prepare('SELECT session_id,WORD FROM hangman')
      || die $dbh->errstr;
$sth->execute() || die $sth->errstr;
while (my $row = $sth->fetch) {
  my($session, $word) = @$row;
  print "session => $session, word => $word\n";
}
$sth->finish;

The example starts with a call to the database handle's prepare() method with the text of the SQL SELECT statement. prepare() parses the SQL and checks it for syntactic correctness but does not actually execute it. The query is returned as a statement handler which we store into the variable $sth. If some error occurred while preparing the statement, prepare() returns undef, in which case we return the errstr() error text.

Next we call the statement handler's execute() method. This performs the query and returns either the number of rows retrieved or undef if an error occurred. In the case of a syntactically correct query that happens to return no rows (because the table is empty or because no records satisfied the criteria in the WHERE clause), execute() returns the value 0E0 which Perl regards as true in a logical context, but as zero in a numeric one.

Now we enter a loop in which we call the statement handler's fetch() method. Each time it's called, fetch() returns the requested columns in the form of an array reference. To retrieve the values themselves, we just dereference the value into a list. Because we requested the columns session_id and WORD, we get a reference to a two-item array back from fetch(). When there are no more rows left, fetch() returns undef.

DBI actually offers a family of fetch functions. fetchrow_array() is like fetch(), but it dereferences the row first and returns an array corresponding to the list of requested columns. Another function, fetchrow_hashref(), turns the current row into a hash of the column names and their values and returns the hash's reference to the caller. This allows us to make the example above more readable at the cost of making it somewhat less efficient:

$sth = $dbh->prepare('SELECT session_id,WORD FROM hangman')
      || die $dbh->errstr;
$sth->execute || die $sth->errstr;
while (my $row = $sth->fetchrow_hashref) {
  print "session => $row->{session_id}, word => $row->{WORD}\n";
}
$sth->finish;

DBI also provides a fetchrow_arrayref() method for fetching the row as an array reference. It is identical in every respect to fetch().

When you are finished with a statement handler, you should call its finish() method in order to free up the resources it uses.

The last thing you need to know about statement handlers is that many DBI drivers allow you to put placeholders, indicated by the ? character, inside SQL statements. prepare() compiles the statement and returns a statement handler as before, but when you later call execute() you pass in the values to be substituted into the placeholders. This allows you to treat statement handlers much as you would a subroutine by calling it repeatedly with different runtime arguments. For example, we can create a statement handler for returning the entire row of a given session with this bit of code:

$sth = $dbh->prepare('SELECT * FROM hangman WHERE session_id=?');

Now we can fetch information on session fd2c95dd, by calling the statement handler's execute() method this way:

$sth->execute('fd2c95dd');

The same statement handler can later be used to fetch information from other named sessions. You should still call finish() at the end of each series of fetches, even though you are going to reuse the statement handler. Failure to do so can lead to memory leaks.

When you are completely finished with a database handle, you should call its disconnect() method in order to sever the connection and clean up.

Apache::DBI and mod_perl

One of the problems with using DBI databases from conventional CGI scripts is that there's often a significant amount of overhead associated with opening a database connection. When you run a mod_perl-enabled version of Apache, you can take advantage of persistent database connections. Instead of creating a new database handle each time your Apache Perl module or Apache::Registry script runs, you check a global variable for a previously opened handle. If the global is empty, you open a new database connection. Otherwise, you use the contents of the global. A concise way of expressing this logic is with this snippet of code:

$DBH ||= DBI->connect($data_source, $user, $password);

Apache::DBI, a module written by Edmund Mergl, makes handling persistent database connections even easier. It replaces DBI's connect() and disconnect() methods with versions that handle persistent connections behind the scenes. connect() maintains a cache of database handles and returns one of them in response to attempts to open the same database multiple times. It also checks that the database handle is still "live" (some databases have a nasty habit of timing out inactive sessions) and reconnects if necessary. disconnect() is replaced by a no-op so that database handles are not inadvertently closed.

To activate Apache::DBI, you need only use it some time before loading the module or modules that need DBI services. One convenient place to load Apache::DBI is in the Perl startup file:

# perl startup file
use Apache::DBI ();
use Apache::Registry ();
use CGI::Cookie ();
. . .  etc.

If you don't have a Perl startup file, you can also load the module at server startup time by adding this directive to one of the server configuration files:

PerlModule Apache::DBI

You will now have persistent database connections when using mod_perl, and conventional use-once-and-throw-away connections when using standard CGI.