Perl Programming/DBI - Perl Database Interface

From Wikibooks, the open-content textbooks collection

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/.

[edit] 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 driver 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.

[edit] Installing DBI

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

[edit] Installing DBD::Oracle

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

[edit] Getting the correct version

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

[edit] 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.

[edit] 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");
 
# 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"); # NOTICE - no ; in the end!
 
# 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 rows.
# In turn, every row is an array of values.
while (my @next_row_fields = $sth->fetchrow_array) {
    foreach my $next_field (@next_row_fields) {
        if (defined $next_field) {
            print "$next_field";
        }
        else {
            print "NULL";
        }
        print "\t";
    }
    print "\n";
}
 
exit;
 
__END__