byterock

A little History

Since its inception by Tim Bunce in 1994, DBI (DataBase Interface) has been the de facto standard for interfacing databases with the Perl language. Thanks to the designers' foresight, DBI is database-independent, with methods, variables, and conventions that provide a consistent programming interface, no matter what database management system is being used.

Standard API?

So, in theory, programs written with DBI should work the same between DBs, Reality is a little different as it seems that each RDMS try to outdo each other in the number of non standard SQL function they implement as well there are a number of 'Drivers' that do not even access a RDMS or even a DB for that matter. Fortunately DBI make no attempt to force you to use a standard SQL or even SQL. Think of DBI as the glue that the Perl language uses to link to a specific 'Driver' to a data store giving us a common platform to work with.

Handles

The objects that DBI creates and which perl uses to interact with the DB are commonly known as handles. There are three handles used in DBI:

1. driver handles, which can create
2. database handles, which can in turn can create
3. statement handles.

Driver handles are not normally referenced by programmers as they are loaded when DBI is initialized. The great thing about DBI is that the individual drivers are completely encapsulated within a handle. So it is possible and sometimes very useful to have more than one driver handle loaded at the same time. For example, if you need to transfer data from an Informix database into an Oracle database, it is very easy to write a single Perl script that connects to both and passes data between them.

Database Handles are the encapsulation of a single database connection and are always the children of the driver handle that created them. As with their parents, you can spawn any number of different connections to a single driver handle. In the example above, you could use two connections (database handles) for both Oracle and Informix DBs to account for an even more complicated data structure. Database handles are also thread-safe, so an individual handle will not leak into other handles. Database handles are by convention called $dbh in Perl scripts.

Statement handles are the real meat of the DBI system, and as the name implies, they encapsulate single SQL statements to be executed on the database. Like the other handles, all statement handles are children of the database handle that created them, and like their parents, data will not leak out of them and into other handles. Statement handles are conventionally called $sth in Perl scripts.

Connecting

The first step in doing work with DBI is simply connecting to a DB, and like all database APIs, DBI needs to be told how to connect to a database, where to find the database, and how to log into the database.

DBI exposes the connect method (there is no new)

$dbh = DBI->connect($data_source, $username, $password, \%attr)

DBI has its own simple syntax for specifying the data_source. It is dbi, followed by a colon :, then name of the driver, followed by another colon :. So for example if we where going to connect to an Oracle instance our data source will start with

'dbi:Oracle:'

It is important to remember that this value is case--sensitive and the colon is required after the 'Driver' name.

DSN Data Source Name

DBI defines the final part of a connection string following the second colon as the DSN or 'Data Source Name' and that is about as far as standardization goes. The varieties of values that can be entered here are as varied as the DB that can be connected to.
For example all of these are valid connection statements that could get you connected to an Oracle DB depending of course on you local environment.

$dbh = DBI->connect('dbi:Oracle:', '', '');
$dbh = DBI->connect('dbi:Oracle:', 'system', 'password');
$dbh = DBI->connect('dbi:Oracle:XE', 'system', 'password');
$dbh = DBI->connect('dbi:Oracle:', 'system@XE', 'password');
$dbh = DBI->connect('dbi:Oracle:', 'system@localhost/XE', 'password');

Connect Attributes
At connection time, you may also set specific attributes for that connection, including some that are specific to particular DBD drivers. These values can control how errors are handled, and how data is committed and are always a HashRef after 'password' param.

The two most important and probably the only ones you will use are 'PrintError' which forces all errors into warnings and RaiseError' which forces all errors into exceptions or to but it a better way 'die'. PrintError is on by default and it is a good idea to turn it off if you are using RaiseError.

So to connect where you want your program to die on error your connection statement may look like this

$dbh = DBI->connect(dbi:Oracle:, 'system@XE','system', {RaiseError => 1, PrintError =>0});

One of DBI's most useful features is that it reports all errors or warnings to a single variable on the database handle.

$dbh->errstr

will always get you the last error. You can even duff your error handling to your own custom code by using the HandleError attribute.

So if you connected like this

$dbh = DBI->connect(dbi:Oracle:, 'system@XE',
'system', {RaiseError => 1,
PrintError =>0,
HandleError=>sud {use Exception; Exception->new('DBI')->raise($_[0]; }});

Any errors will be thrown as an instance of the 'Exception' class.

Disconnecting

Though DBI does not require you to explicitly disconnect a database connection before your program ends, it is always good practice to do so. DBI has two ways to disconnect. The first uses the database handle.

$dbh->disconnect();

Or you could use the DESTROY() method:

$dbh->DESTROY();

The first is the more common practice. It is also good form to finish with the statement handle.

$sth->finish();

in tandem with disconnect() as this will end any processes that may be ongoing in the statement handle and on the odd Driver it may be required.

Our first Query

Connecting, disconnecting, and error trapping is all well and good, but what we really want to do is perform some SQL against our database, using Perl. To start, we have to include the DBI code.

use DBI;
use strict;

Next, of course, we need to define our variables.

use vars qw($dbh $sth);

We will be using the standard $dbh for our database handle, and $sth for our statement handle. Now let's connect to an instance of Oracle's Express Edition. I am using XE's default HR account and password and letting Oracle figure the rest out by not using a DSN.

$dbh = DBI->connect('dbi:Oracle:', 'hr', 'hr');

Let's create a statement handle for our SQL using the prepare() method on our database handle $dbh.

$sth = $dbh->prepare('Select city from locations');

Next we call the execute() method on the statement handle to fire our SQL,

$sth->execute();

then fetch the data using the statement handle’s fetchrow() method and then print it out.

while (my ($city) = $sth->fetchrow() {
print " $city \n";
}

Finally finish() with the statement handle and disconnect() the database handle.

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

The results should look like this:

$ perl test.pl
Beijing
...
Whitehorse

Here is the whole program.

#!/usr/bin/perl

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

The above example illustrates the standard approach to getting data from DBI:

This will be familiar to any RDBMS programmer as it has been standard practice for many may years.

Next we will look at Deeper into running basic Queries.