Perl Programming/DBI - Perl database interface

DBI - Perl database interface

There is a comprehensive library of modules for connecting to databases from Perl programs. It is maintained by Tim Bunce and it is called DBI - database interface. The main Website for DBI is http://dbi.perl.org/.

Installing the necessary modules

To use DBI to connect to a database, you need at least two CPAN modules. One is the main DBI module, simply called DBI. The other one is a DBD - a database driver module. There are DBI drivers for many mainstream database management systems, such as MySQL and Oracle. The examples in this tutorial concern Oracle; accordingly, the database driver for Oracle is called DBD::Oracle.

So, to run the examples in this chapter you need the modules DBI and DBD::Oracle.

Installing DBI

Installing DBI is straightforward. (For information about installing CPAN modules, see the chapter about CPAN.)

Installing DBD::Oracle

When installing DBD::Oracle, there are a few things that you need to prepare.

Getting the correct version

First of all, when installing DBD::Oracle, make sure that you get a version by Pythian. Pythian maintains DBD::Oracle since version 1.17 (February 2006).

Preparing environment variables

You need to create the environment variables ORACLE_USERID and ORACLE_SID. They are needed to test the connection to your database after DBD::Oracle is installed. The value for ORACLE_USERID must be a valid username and password for your database. ORACLE_SID must be set to database name as it appears in TNSNAMES.ORA. On a Unix-like system run:

export ORACLE_USERID="username/password"
export ORACLE_SID="DATABASE_NAME"

On Windows, you only need ORACLE_USERID:

set ORACLE_USERID="username/password@DATABASE_NAME"

When you have finished defining these environment variables, install DBD::Oracle from CPAN the usual way.

Using DBI

Here is a very simple test script to get you going.

use strict;
use warnings;

# There is no need to use DBD::Oracle. The DBD is loaded automatically later.
use DBI;

# Connecting to the database. Take the SID from TNSNAMES.ORA.
# Here the DBD is loaded.
# $dbh will be the database handle - a variable through which
# you connect to your database.
my $dbh = DBI->connect("dbi:Oracle:SID", "username", "password", { RaiseError => 1 });

# A simple date fetch

# Prepare the SQL statement.
# $sth is a statement handle - an environment for running an SQL statement.
my $sth = $dbh->prepare('select sysdate from dual'); # note no ';' at the end of the SQL

# Execute the SQL statement; don't print it yet
$sth->execute;

# This "loop" prints all the rows (actually just one, in this case)
while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}

# A real SELECT statement.
# Replace my_favourite_table with a name of a table from your database.
$sth = $dbh->prepare('select * from my_favourite_table');

$sth->execute;

# This is a real loop, that prints all the rows.
# It's very rudimentary; see the DBI documentation
# for better tools for pretty printing.
#
# $sth->fetchrow_array returns a Perl array,
# in which every member represents one of the retrieved columns.
# In turn, every row is an array of values.
while (my @row = $sth->fetchrow_array) {

    # replace undefined values (NULLs in the db) with the string "NULL"
    @row = map { defined($_) ? $_ : "NULL" } @row;

    my $row_as_string = join("\t", @row);

    printf "%s\n", row_as_string;
}

# A real SELECT statement using a hash reference and place holder.
# Replace my_favourite_table with a name of a table from your database.
$sth = $dbh->prepare('select * from my_favourite_table where my_field = ?');

my $field_value = 'australia';

$sth->execute($field_value);

# Here is the loop again, this time we use fetchrow_hashref
# which makes our code more resistant to breaks due to schema changes.
# It also spares us from remember which location a field is
# positioned in an array.

# Recall that %{$var} de-references the hashref $var
# and that $var->{field} specifies the key 'field'

while (my $row = $sth->fetchrow_hashref) {
    foreach my $next_field (keys %{$row}) {
        if (defined $row->{$next_field}) {
            print $row->{$next_field};
        }
        else {
            print 'NULL';
        }
        print "\t";
    }
    print "\n";
}

# gracefully disconnect from the database
$dbh->disconnect();