Recipe 14.9 Executing an SQL Command Using DBI

14.9.1 Problem

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

14.9.2 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($NON_SELECT_SQL_STATEMENT);

$results = $dbh->selectall_arrayref($SELECT_SQL_STATEMENT);

$sth = $dbh->prepare($SQL_SELECT_STATEMENT);
$sth->execute( );
while (@row = $sth->fetchrow_array) {
    # ...
}

$dbh->disconnect( );

14.9.3 Discussion

The DBI module abstracts away the different database APIs, offering you a single set of functions for accessing every database. The actual work of connecting to a database, issuing queries, parsing results, etc. is done by a DBD module specific to that database (e.g., DBD::mysql, DBD::Oracle, etc.).

All work with databases via the DBI is done through handles. A handle is simply an object, created by calling DBI->connect. 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. This DSN (Data Source Name) identifies the database you're connecting to. The first field is always dbi (though this is case-insensitive, so DBI will do just as well), 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. PrintError controls whether DBI warns when a DBI method fails (the default is true; setting it to a false value keeps DBI quiet). Setting RaiseError is like PrintError except that die is used instead of warn. AutoCommit controls transactions, and setting it to true says that you don't want to deal with them (see Recipe 14.11).

At the time of this writing, there were DBD modules for all common databases (MySQL, Oracle, PostgreSQL, Informix, DB2, SQLServer), many fringe ones (XBase, SQLite), and several non-databases. For a list, see http://search.cpan.org/modlist/Database_Interfaces/DBD.

Here are some sample DSNs:

dbi:Oracle:tnsname
dbi:Oracle:host=foo.bar.com;sid=ORCL
dbi:Oracle:host=foo.bar.com;sid=ORCL;port=1521
dbi:mysql:database=foo;host=foo.bar.com;port=3306;mysql_compression=1
dbi:Pg:dbname=foo;host=foo.bar.com;options=-F

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. The quickest way to perform a query that returns rows of data is with the selectall_arrayref and selectall_hashref methods:

$rows = $dbh->selectall_arrayref("SELECT isbn,title,author FROM books");
print $row[0][1];            # prints title from first row

$rows = $dbh->selectall_hashref("SELECT isbn,title,author FROM books", "isbn");
print $rows->{596000278}[2]; # prints "Programming Perl"

The database system uses the second and third arguments, the username and password, to authenticate the user.

Sometimes your query will generate many rows of results, but you're only interested in one column. The selectcol_arrayref method is designed for just such a case: it turns a series of one-column rows into a reference to a simple Perl array of values:

$books = $dbh->selectcol_arrayref("SELECT title FROM books");
print $books[3]; # prints the title of the fourth book

If you don't want to read all results into memory at once, or you want to efficiently reuse queries, 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). This is used in Recipe 14.12.

If you know your result will return only a single row, use the selectrow_* methods:

@row = $dbh->selectrow_array("SELECT title,author FROM books WHERE 
isbn='596000278'");
print $row[1];              # prints author of first book returned

$row = $dbh->selectrow_arrayref("SELECT title,author FROM books WHERE 
isbn='596000278'");
print $row->[1];            # prints author of first book returned

$row = $dbh->selectrow_hashref("SELECT title,author FROM books WHERE 
isbn='596000278'", "title");
print $row->{author};       # prints author of first book returned

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.

This indicates that you have not fetched all of the data returned by a SELECT statement. In the few rare cases where this does not indicate a problem, and you don't want to use one of the selectrow_* methods, then the finish method can be used to discard the unfetched data and mark the statement handle as inactive.

The DBI module comes with a FAQ (the DBI::FAQ(3) manpage, kept up to date at http://dbi.perl.org) and regular documentation (perldoc DBI). The driver for your DBMS also has documentation (DBD::mysql(3), 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-5 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.

Example 14-5. dbusers

  #!/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);
  }
  $rows = $dbh->selectall_arrayref("SELECT uid,login FROM users WHERE uid < 50");
  foreach $row (@$rows) {
      print join(", ", map {defined $_ ? $_ : "(null)"} @$row), "\n";
  }
      
  $dbh->do("DROP TABLE users");
  
  $dbh->disconnect;

14.9.4 See Also

The documentation for the DBI and relevant DBD modules from CPAN; http://dbi.perl.org/ and http://search.cpan.org/modlist/Database_Interfaces; Programming the Perl DBI, by Alligator Descartes and Tim Bunce (O'Reilly)