An Introduction to the Perl DBI

Introduction

DBI is a Perl module that makes it possible to use Perl to access many different types of databases though a single API that works independently of the actual database being used. This means that the programmer is able to use the DBI module (Database Independent) to talk to a MySQL database, a DB2 database, an Oracle database, and many others using the same DBI-specific variables and function calls. Although there are also many functions built into the DBI for specific database engines, the core functionality is always the same. This functionality makes DBI a very powerful programming tool, but after a brief introduction, you will notice that the DBI is also very accessible and easy to understand; programmers require knowledge of only a few key features to get started writing scripts to perform any number of database tasks.

How it All Works

There are three tiers within every Perl DBI application. The first layer is the Perl script itself, which uses the DBI Application Program Interface (the objects, functions and variables predefined by the DBI) to interact with the database. The middle layer is the DBI, whose main responsibility is to pass those DBI-specific instructions to the appropriate database driver. The third layer is the DBD (Database Dependent) layer, which is the driver for the specific database engine you are using.

Example: The Postcard Application

Let's say I have a collection of postcards, and in order to keep them organized, I have entered information about them into a MySQL database. Then let's say that I want to show those postcards off to visitors to my website. Because I have only CGI available on my web server, I decide the best way to do that is through a CGI application. I start out my application like this:

#!/usr/bin/perl -w

use CGI("standard");
use DBI;

This bit of code first specifies this as a Perl script. Then it makes the Perl CGI module available (this module automates some HTML writing and means less typing). The last line makes available the DBI module.

My next step is to define some variables for the database and connect to it:

my $datasource = "DBI:mysql:postcards:localhost";
my $user = "greg"
my $passwd = "greatpostcards";

If you have ever written any sort of application that has a database interface, this information should look familiar to you. However, note that the syntax of the $datasource variable is a bit unique. The pattern, which seems at first to be counter-intuitive, works like this: DBI:[DBD driver]:[database name]:[database host].

Remember that DBI applications are designed to easily talk to many different types of databases. Specifying the type of database you want to use in your application is as easy as changing a single part of the string held in the $datasource variable. So, now that all of my connection information has been defined, my next step is to create a connection object. That's as easy as adding the following to my application: my $dbh = DBI->connect($datasource,$user,$passwd);.

The string contained in the $datasource variable is saying, "hey, DBI! Use the mysql database dependent module to access the postcards database that exists on the computer named localhost."

Now that I have defined my connection object, my next step is to prepare an SQL statement. I can do that by using the (you guessed it) prepare method:

my $sth = $dbh->prepare("SELECT postcard.*,
                         location.*
                         sender.*
                         FROM postcard
                         LEFT JOIN location ON postcard.location_id = location.location_id,
                         LEFT JOIN sender ON postcard.sender_id = sender.sender_id
                         WHERE postcard.pc_id = ?;");

Now, with the SQL statement prepared, my next step is to execute it. We do that by employing the execute method: $sth->execute($dynamicvariable);

Everything might seem pretty clear in these two lines, except you might notice the question mark in the SQL statement and the $dynamicvariable we pass to it when we invoke the execute method. This is a feature of the Perl DBI, which allows me to pass any number of variables to my SQL statement before it is executed. The value of these variables then fits into the question mark placeholders, in the order they are passed. (So I should be sure that the variables I pass go in the right order!) If my application does not require me to pass dynamic values to my SQL statement, I would have simply called the execute method without any parentheses, like this: $sth->execute;

Now, for the final step in my application, I need a way of getting at the contents of the record set that the execute method returns. As is always the way with Perl, there are a billion different ways of doing this. But since I'm dealing with rows of data, the fetchrow_array works well, because it allows me to loop through the returned row as an array.

my @array = $sth->fetchrow_array;
my $i;
for $i (0..$#array) {
    print $array[$i],"\n";
}

As I have already mentioned, I have many other DBI functions at my disposal at this point. The fetchrow_arrayref will return the database row as an array reference. The fetchrow_hashref will return it as a hash reference (this is especially handy if you have a lot of rows and you want to key them up by their column names; if you're like me, you tend to remember names better than numeric values). And there are many others. Be sure to read the documentation to find the function that most suits your needs and programming behaviors.

My next step is to clean up my program. Though clearing memory and closing database connections is not always necessary, it is good programming practice (not to mention easy to do). So I recommend it. Here's all you have to do:

$sth->finish;
$dbh->disconnect;

Once that's done, I'm done with my postcard application. But beyond the simple API demonstrated by the code snippets above, it may not be exactly clear why the Perl DBI is valuable. Here are some benefits I have found.

Why You Might Care About This

• Ubiquity: The Perl DBI module is included with the default install of any relatively new version of Perl. So if you have access to a UNIX-based system, you should be able to start programming the Perl DBI.
• Easy Integration: Because it's a Perl module, database functionality can be easily integrated into existing Perl/CGI applications.
• Simplicity and Power: being able to talk to a wide variety of databases with Perl (and even within a single Perl script) makes massive exports and/or imports of data even much more manageable (anyone who has had the job of upgrading a corporate RDBMS can see the value in this, I'm sure).

Further Reading and DBI Resources

You can start your search for Perl DBI information just about anywhere on the web. There is the Database Interfaces section of the CPAN website, there are Perl-oriented newsgroups. However, the best places to start reading about Perl DBI is either by checking out Symbolstone, the central resource for Perl DBI and DBD modules and documentation on the Web, or else just running the perldoc DBI command at your local UNIX prompt. Generally the information is accessible and easy to understand, and will help you further as you get started.

Conclusion

Notwithstanding all of the pitfalls and shortcomings of Perl programming generally, the power, simplicity, and accessibility of the Perl DBI make it worth understanding. And even though it has the ability to work well with the most complex development projects, it's easy enough to learn in time for your next Web administration crisis as well.