Perl Cookbook

Executing an SQL Command Using DBI and DBD

Problem

You want to send SQL queries to a database system such as Oracle, Sybase, mSQL, or MySQL, and process their results.

Solution

Use the DBI (DataBase Interface) and DBD (DataBase Driver) modules available from CPAN:

use DBI;

$dbh = DBI->connect('DBI:driver:database', 'username', 'auth',
            { RaiseError => 1, AutoCommit => 1});
$dbh->do($SQL);
$sth = $dbh->prepare($SQL);
$sth->execute();
while (@row = $sth->fetchrow_array) {
    # ...
}
$sth->finish();
$dbh->disconnect();

Discussion

DBI acts as an intermediary between your program and any number of DBMS-specific drivers. For most actions you need a database handle ($dbh in the example). This is attached to a specific database and driver using the DBI->connect call.

The first argument to DBI->connect is a single string with three colon-separated fields. It represents the data source—the DBMS you’re connecting to. The first field is always DBI, and the second is the name of the driver you’re going to use (Oracle, mysql, etc.). The rest of the string is passed by the DBI module to the requested driver module (DBD::mysql, for example) where it identifies the database.

The second and third arguments authenticate the user.

The fourth argument is an optional hash reference defining attributes of the connection. Setting PrintError to true makes DBI warn whenever a DBI method fails. Setting RaiseError is like PrintError except that die is used instead of warn. AutoCommit says that you don’t want to deal ...