Perl for System Administration

Using the DBI Framework

Here are the basic steps for using DBI. For more information on DBI, see Programming the Perl DBI by Alligator Descartes and Tim Bunce (O’Reilly).

Step 1: Load the necessary Perl module

Nothing special here, you need to just:

use DBI;

Step 2: Connect to the database and receive a connection handle

The Perl code to establish a DBI connection to a MySQL database and return a database handle looks like this:

# connect using to the database named $database using given 
# username and password, return a database handle
$database = "sysadm";
$dbh = DBI->connect("DBI:mysql:$database",$username,$pw);
die "Unable to connect: $DBI::errstr\n" unless (defined $dbh);

DBI will load the low-level DBD driver for us (DBD::mysql) prior to actually connecting to the server. We then test if the connect( ) succeeded before continuing. DBI provides RaiseError and PrintError options for connect( ), should we want DBI to perform this test or automatically complain about errors when they happen. For example, if we used:

$dbh = DBI->connect("DBI:mysql:$database",
                    $username,$pw,{RaiseError => 1});

then DBI would call die for us if the connect( ) failed.

Step 3: Send SQL commands to the server

With our Perl module loaded and a connection to the database server in place, it’s showtime! Let’s send some SQL commands to the server. We’ll use some of the SQL tutorial queries from Appendix D for examples. These queries will use the Perl q convention for quoting (i.e., something is written as q{something} ...