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 with transactions (smaller DBMSs don't support them, and if you're using a larger DBMS then you can read about transactions in the DBMS documentation).

You can execute simple SQL statements (those that don't return rows of data) with a database handle's do method. This returns Boolean true or false. SQL statements that return rows of data (like SELECT ) require that you first use the database handle's prepare method to create a statement handle. Then call the execute method on the statement handle to perform the query, and retrieve rows with a fetch method like fetchrow_array or fetchrow_hashref (which returns a reference to a hash mapping column name to value).

Statement handles and database handles often correspond to underlying connections to the database, so some care must be taken with them. A connection is automatically cleaned up when its handle goes out of scope. If a database handle goes out of scope while there are active statement handles for that database, though, you will get a warning like this:

disconnect(DBI::db=HASH(0x9df84)) invalidates 1 active cursor(s) 
    at -e line 1.

The finish method ensures the statement handle is inactive (some old drivers need this). The disconnect method, er, disconnects from the database.

The DBI module comes with a FAQ ( perldoc DBI::FAQ ) and regular documentation ( perldoc DBI ). The driver for your DBMS also has documentation ( perldoc DBD::mysql , for instance). The DBI API is larger than the simple subset we've shown here; it provides diverse ways of fetching results, and it hooks into DBMS-specific features like stored procedures. Consult the driver module's documentation to learn about these.

The program in Example 14.7 creates, populates, and searches a MySQL table of users. It uses the RaiseError attribute so it doesn't have to check the return status of every method call.

#!/usr/bin/perl -w
# 

dbusers - manage MySQL user table
use DBI;
use User::pwent;

$dbh = DBI->connect('DBI:mysql:dbname:mysqlserver.domain.com:3306',
                    'user', 'password',
                    { RaiseError => 1, AutoCommit => 1 })

$dbh->do("CREATE TABLE users (uid INT, login CHAR(8))");

$sql_fmt = "INSERT INTO users VALUES( %d, %s )";
while ($user = getpwent) {
    $sql = sprintf($sql_fmt, $user->uid, $dbh->quote($user->name));
    $dbh->do($sql);
}

$sth = $dbh->prepare("SELECT * FROM users WHERE uid < 50");
$sth->execute;

while ((@row) = $sth->fetchrow_array) {
    print join(", ", map {defined $_ ? $_ : "(null)"} @row), "\n";
}
$sth->finish;
    
$dbh->do("DROP TABLE users");

$dbh->disconnect;

See Also

The documentation for the DBI and relevant DBD modules from CPAN; http://www.hermetica.com/technologia/perl/DBI/ and http://www.perl.com/CPAN/modules/ by-category/07_Database_Interfaces/