Perl's DBI Database Library

Summary: A short introduction to interacting with Perl's DBI module, which provides simple access to databases.

References: A good place to get more information on DBI is the DBI man page, which you can access by typing man DBI in a terminal window.

Contents:

• Background
• Database Handles
• Steps in Processing an SQL Statement
  • Step 01: Write the Statement
  • Step 02: Prepare the Statement
  • Step 03: Execute the Statement
  • Step 04: Check Success
  • Step 05: Process Results
  • Step 06: End the Query
• Some Other Notes
  • Quoting Strings
  • Access Privileges

Background

By this time, you should have learned a little about SQL and thought about the initial stages of an example using SQL to build a to do list. We are now ready to turn those tables into a Web application.

In order to do so, we need a way to access the database from Perl. There are a few simple steps in doing so. You first create a connection to the database (a so-called database handle). You do this once per session. For each statement, you need to set up the statement, execute the statement, and deal with the results.

Database Handles

To set up a database handle, you need to connect to a specified database. That means you need to specify the database manager you are using (in our case, PostgreSQL, which is abbreviated Pg) and the name of the database.

my $db = ...;
my $dbh = DBI->connect("dbi:Pg:dbname=$db");

When you are done with a database handle, you should disconnect from the database with

$dbh->disconnect;

Steps in Processing an SQL Statement

Step 01: Write the Statement

You begin by writing the statement as a normal Perl string variable. For example

my $statement = "select * from tasks where tid=$tid;";

Step 02: Prepare the Statement

Before the statement can be executed, it needs to be prepared for execution. I will admit that I have no idea what goes on during preparation, but you get a statement handle back.

my $sth = $dbh->prepare($statement);

Step 03: Execute the Statement

Once you've prepared a statement, you are ready to execute the statement. Notice that the return value from execute is a success/failure code and not the value returned by the statement (if any).

my $rv = $sth->execute;

Step 04: Check Success

The return value from execute should be nonzero. If it is 0, you need to deal with errors in an appropriate way.

if (!$rv) {
  handleError($dbh->errstr);
  ...
}
else {
  ...
}

Step 05: Process Results

If your command is a select command (and it often is), you deal with the results a row at a time by calling $sth->fetchrow_array. Each time, you get the next row. When it's done, you should get an undefined value. Here's a simple attempt to process a sequence of nickname/email pairs.

while (($nickname,$email) = $sth->fetchrow_array) {
  print "$nickname: $email\n";
}

Step 06: End the Query

When you're done with the query, you should note that you are done so that the databasae and Perl can get rid of any associated information.

$sth->finish;

Some Other Notes

Quoting Strings

As you may recall, SQL has some fascinating conventions on how strings are formatted. Before sending a string as part of a a query, you should quote it with $dbh->quote(str).

Access Privileges

If you are running your application through the Web, you need to give the Web server access to your database. For each table, execute the following command from within psql

grant all on table to apache;