Perl Programming/DBI - Perl Database Interface

From Wikibooks, the open-content textbooks collection

Jump to: navigation, search
Previous: CPAN Index Next: CGI

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

Contents

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

[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 maintains 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")
  or die "Couldnt connect to database: $DBI::errstr";
 
# 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!
    or die 'Couldnt prepare statement: ' . $dbh->errstr;
 
# Execute the SQL statement; don't print it yet
$sth->execute or warn 'Execute failed: ' . $dbh->errstr;
 
# This "loop" prints all the rows (actually just one, in this case)
while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}
 
$sth->finish():
 
# 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')
    or die 'Couldnt prepare statement: ' . $dbh->errstr;
 
$sth->execute or warn 'Execute failed: ' . $dbh->errstr;
 
# 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";
}
 
$sth->finish():
 
# 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 = ?')
    or die 'Couldnt prepare statement: ' . $dbh->errstr;
 
my $field_value = 'australia';
 
$sth->execute($field_value) 
  or warn "Execute failed, field_value: $field_value errstr: " . $dbh->errstr;
 
# 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 $next_row_fields = $sth->fetchrow_hashref) {
    foreach my $next_field (keys %{$next_row_fields}) {
        if (defined $next_row_fields->{$next_field}) {
            print $next_row_fields->{$next_field};
        }
        else {
            print 'NULL';
        }
        print "\t";
    }
    print "\n";
}
 
$sth->finish():
 
 
# gracefully disconnect from the database
$dbh->disconnect();
Previous: CPAN Index Next: CGI
Retrieved from "http://en.wikibooks.org/wiki/Perl_Programming/DBI_-_Perl_Database_Interface"