Perl Cd Bookshelf [Electronic resources] نسخه متنی

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)