DBI

Lexington Perl Mongers
September, 1999

In the September meeting, I did a very quick overview of DBI, and how you can use it to talk to your database. I've lost my notes, and so this may not be what I said at all.

What is it?
Why would I want to use it?
Where do I get it?
How do I use it?
Where do I learn more?

What is it?

DBI is the database-independant interface for Perl. It's a three-tier system (4 in some cases). Your code talks to DBI, which, in turn, talks to the DBD - DataBase Driver, which is a module written specifically for the particular database that you are interested in. The DBD, in turn talks to the database, or, in some cases, it will talk to a platform-specific database driver, such as ODBC, which then talks to the database.

Why would I want to use it?

There are other database modules, such as Win32::ODBC, mysql.pm, and so on, that are written specifically for particular databases. Why would you want to use this generic thing? The main reason is portability. I freqently write code on a Windows machine, talking to an Access database, and then move it to my Linux machine, talking to a MySQL database, with no code changes. Or to an NT machine, running MS SQL Server, again, with no code changes. This is a substantial savings in development time and in code maintenance. It also means that I can write code, and be confident that it works on databases that I have no opportunity to test for, such as Oracle, Informix, or DB2.

Where do I get it?

You can install things one of three ways. Hey, it's Perl! There's more than one way to do it.

You can download DBI, and your appropriate DBD, from CPAN, and do the standard:

perl Makefile.PL
make
make test
make install

If you are running Perl on Windows, go to a DOS prompt, and type:

ppm install DBI
ppm install DBD::ODBC

And, if you're on a Unix system, and have the CPAN module set up correctly, you can type:

perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'install DBD::mysql'

Substitute for 'mysql' whatever database you are interested in using.

How do I use it?

In order to use DBI, you'll need to know some SQL. DBI is really just a pass-through layer, as described above. The following code connects to a database, and makes some queries:

#!/usr/bin/perl
use DBI;

$dbh = DBI->connect('DBD:mysql:students', 'username', 'password');
$sth = $dbh->prepare("select fname, lname, studentID from student
                         where fname = 'John'
                         order by lname");
$sth->execute;
$sth->bind_columns(undef, \($fname, $lname, $id));
while ($sth->fetch)  {
     print "$lname, $fname: $id\n";
}

connect establishes a connection to the database.
prepare sends a SQL query to the database, so that it can optimize the query when you come around to running it. execute runs the query. bind_columns associates variables with the return columns of the query, automatically putting the data into those variables when you do a fetch. fetch gets the next row from the query. It returns false if there are no more rows.

There are a number of other ways to retreive data. One particularly useful way (although substantially slower than bind_columns) is the fetchrow_hashref method:

$sth = $dbh->prepare("select * from students");
$sth->execute;
while ($student = $sth->fethrow_hashref)  {
     print "$student->{fname} $student->{lname} is a $student->{year}.\n";
}

This is useful when you are doing a select *, and want to refer to the fields by name in a hash. This might make your code more readable, and has the additional advantage that all of your data is in one handy data structure that you can pass around. Hashrefs are nice.

Where can I learn more?

Some good DBI resources are:

The DBI mailing lists
Aligator's DBI web site
The DBI book from O'Reilly (I've not read it yet, so I don't know how good it is, but it's written by Tim and Aligator, and nobody in the world knows more about the subject.)
The documentation that comes with the module is fantastic. Use pod2html to generate HTML documentation.

Rich Bowen - rbowen@rcbowen.com