PerlStalker's SysAdmin Notes and Tools

DBI.pm

DBI.pm is a Perl module that provides an abstract interface to most of the popular SQL databases. Its platform independant design allows a programmer to use the same program with different back-end databases.

Before a programmer can use the DBI with his/her favorite database, s/he must install the appropriate driver module. Drivers, like most Perl modules, can be downloaded from the CPAN.

Connecting and Disconnecting

Connecting to a database is simple. Simply call the connect class method as demonstrated below.

$dbh = DBI->connect($data_source, $username, $password)
	or die $DBI::errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr) 
	or die $DBI::errstr;

There us no standard for the format of $data_source except that it must begin with dbi:DriverName:. Everything after DriverName is up to the author of the driver. $username and $password are the user name and password of the user on the database. Connectio attributes can be passed in through the hash reference \%attr. See the DBI docs for a description of the possible attributes. connect returns a handle to the database that is then used whenever the program needs to talk to the database.

Disconnecting from the database is done through the disconnect method of the database handle. Ex. $rc = $dbh->disconnect or warn $dbh->errstr;

#!/usr/bin/perl -w
# Demo of connecting and disconnecting from a database

use DBI;
use strict;

# The db to connect to
# NB: For this example I will connect to
# a MySQL database named Fred on the fictional
# host db.host.com.
my $data_source = 'dbi:mysql:Fred:db.host.com';
my $db_user = 'joeBob'; # the DB user name
my $db_passwd = 'g00dPa$$wd'; # the password

# Connect and get the handle to the db.
my $dbh = DBI->connect ($data_source, $db_user, $db_passwd);

# Insert Perl DB magic here.

$dbh->disconnect;

Constructing SQL Statements

SQL statements are constructed as standard Perl strings. Variables can be interpolated inside the double quotes to create dynamic statments.

SQL statements can be constructed as one string or through concatonation inside a selection statement or loop. This gives programmers the abillity to create very large statements quickly and use the full power of Perl and it's regular expressions to create the statement.

#!/usr/bin/perl -w
# Demo on constructing SQL statements.

use DBI.pm;
use strict;

# connect to db and get input and such
# up here.

# Create a SELECT inside double-quotes (")
my $name = 'Natasha'; # this could be from the user.
my $sql = "DELETE from users where name = \"$name\";";

# SQL magic here

# Create a SELECT statement using a loop
my $sql = "SELECT * from table1 where ";
for my $i (0 .. 117000)
{
    # @data is defined up above somewhere
    $sql .= "field$i = $data[$i]";
    $sql .= ', ' unless $i == 11700;
}
$sql .= ';'; # don't forget the ; on the end of the SQL

# SQL magic here

Executing INSERT, UPDATE, etc.

Statements that do not return results can be executed by the `do' method. `do' returns the results of the statements. This value can be a Boolean for INSERTs or the number of rows affected for UPDATEs and DELETEs. 'do' takes a sting containing the SQL statement to execute.

This is most useful for non-select statements and for statements that do not need to be repeated or cannot be prepared in advance.

#!/usr/bin/perl -w
# demo showing the use of
# the do method

use DBI;
use strict;

# Get a handle to the db.
my $dbh = DBI->connect(...);

# Execute a SQL statement.
my $sql = "DELETE from Table4 where ";
$ sql .= "field2 like 'Something';";
my $rc = $dbh->do ($sql) or
    die "SQL Error: ".$dbh->errstr."\n";

# $rc now has the result code from the SQL

The SELECT Statement

The SELECT statement is special among SQL statements. It is the only statement that returns data. However, every database returns the data in a different way. Perl solves this problem through the DBD drivers which return data in any of four data structures selectable by the programmer. We will examine each of these in more detail in the following section.

Using a SELECT statement with DBI.pm is different from the other SQL statments as well. You need to get a statement handle from the database using the `prepare' method. The statement is executed by calling the `execute' method of the statement handle. The results can then be retrieved by calling the fetchrow_* methods of the statement handle.

#!/usr/bin/perl -w
# Demonstrates the use of the
# SELECT statement and the DBI
# statement handle.

use DBI;
use strict;

# Create the database handle
my $dbh = DBI->connect(...);

my $sql = "SELECT * from Table4;";

# Create the statement handle
my $sth = $dbh->prepare($sql);

# execute the statement
$sth->execute or
    die "Oops: ".$sth->errstr."\n";

# Everything has worked up to this
# point. Now you'd want to get the
# data. We'll discuss that later.

Arrays and Array References

DBI.pm can return a record as an array or as a reference to an array. The elements of the array are the columns of the database in the order specified in the SELECT.

The fuctions `fetchrow_array' and `fetchrow_arrayref' return the record or false if there is no more data. The array reference is useful if you plan to store the record in another data structure such as a hash. Arrays are better for large amounts of data since hashes require more memory space to store the keys.

#!/usr/bin/perl -w
# Demonstrates the fetchrow_array*
# methods of the statement handle.

use DBI;
use strict;

# connect to the DB
my $dbh = DBI->connect(...)'

# get the statement handle
my $sql = "SELECT * from Table46;";
my $sth = $dbh->prepare($sql);
# execute the select
my $sth->execute or die $sth->errstr;

# Fetch row as an array
my @results
while (@results = $sth->fetchrow_array)
{
    # do stuff with @results
}
$sth->finish;

# or get the results as an array ref.
my $sth = $dbh->prepare($sql);
# execute the select
my $sth->execute or die $sth->errstr;

# Fetch row as an array reference
my $results; 
while ($results = $sth->fetchrow_arrayref)
{
    # do stuff with $results like build a hash
    $data{$results->[0]} = $results;
}
$sth->finish;

Hash References

DBI.pm can return records as a hash reference by calling the `fetchrow_hashref' method of the statement handle. The keys are the column names and the values are the associated data from the database. Hashes make for more readable code and makes it easier to access specific fields in tables with a large number of columns.

#!/usr/bin/perl
# Demonstrate fetchrow_hashref

use DBI;
use strict;

# Create the database
my $dbh = DBI->connect(...);

my $sql = "SELECT * from Table1;";
# get the statment handle
my $sth = $dbh->prepare($sql);

# get the results
$sth->execute or
    die $sth->errstr;
my $results;
while ($results = $sth->fetchrow_hashref)
{
    # do stuff with results. ex:
    $data{$results->{'key'}} = $results;
}
$sth->finish;

# Other code ...

Array of Array References

One of the more common tasks of Perl database programmers is fetching all of the selected records and placing them in to an array. DBI.pm provides this with the method `fetchall_arrayref'. It returns an array with each element representing a row from the database stored as an array reference.

#!/usr/bin/perl -w
# Demonstrate fetchall_arrayref

use DBI;
use strict;

# Connect to the db
my $dbh = DBI->connect(...);

my $sql = "SELECT * from Table36;";
my $sth = $dbh->prepare ($sql);

# Execute the SQL
$sth->execute or die $sth->errstr;
my @results = $sth->fetchall_arrayref;

# close the statement handle
$sth->finish;

# Other cool code...