Looking at DBI Part 1

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.


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.


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


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.


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.


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.


Or you could use the DESTROY() method:


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


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,


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.


The results should look like this:

$ perl test.pl

Here is the whole program.


use DBI;
use strict;
use vars qw($dbh $sth);

# get a database handle
$dbh = DBI->connect('dbi:Oracle:', 'hr', 'hr');

# get a statement handle
$sth=$dbh->prepare('SELECT city FROM locations');

# execute the statement handle

# loop through the results
while (my ($r_city) = $sth->fetchrow()){
print " $r_city \n";


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

Prepare ? Execute ? Fetch

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.


I don't see how it could be DBI's responsibility to cope with all (or even any) SQL dialects.

DBI shouldn't care about SQL dialects, in fact, quoting from the manual "The DBI itself does not mandate or require any particular language to be used; it is language independent. In ODBC terms, the DBI is in "pass-thru" mode, although individual drivers might not be. The only requirement is that queries and other statements must be expressed as a single string of characters passed as the first argument to the "prepare" or "do" methods."

So DBI could, though it hasn't much, be used for any datasource that accepts queries in a linear form which isn't SQL (RDQL, SPARQL, OCL, OQL, XQuery or even YQL or FQL).

Indeed - if you look at comparable toolkits for other languages (PDO for PHP, JDBC for Java, etc) they do much the same as DBI. They provide a common interface for connecting to databases, sending SQL queries, and interpreting result sets, but they don't attempt to rewrite your SQL for you.

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations