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],"
";
}

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.