The Perl API

MySQL connectivity in Perl is performed using the Database Interface (DBI) and a Database Driver (DBD). Perl version 5.6.0 or later is required.

If you do not already have DBI installed, use cpan to download and install it:

shell> cpan cpan> install DBI 

On Windows platforms using the ActivePerl distribution, use the ppm.bat script to install Perl modules:

C:\perl\bin> ppm.bat ppm> install DBI 

To add the MySQL database driver, install the DBD::mysql module in the same way.

cpan> install DBD::mysql 

Perl scripts that use the MySQL DBD include the following line (note the unusual capitalization of Mysql that is used in the Perl API):

use Mysql; 

Connecting to MySQL

$dbh = Mysql->connect(host, dbname,                      user, password);

The connect method is performed on the Mysql object and a database handle is returned. For localhost connections, use undef as the first argument. A single DBI method calls both mysql_init() and mysql_real_connect() in the underlying C API.

Executing a Query

$sth = $dbh->query(query);

To execute a query, call the query() method on a database handle with the SQL statement as its argument. A statement handle is returned.

Fetching Data from a Result Set

@row = $sth->fetchrow;

When the fetchrow method is called on a statement handle, a row of data from the result of a query is returned as an array. The first time fetchrow is invoked, the first row from the data set is returned, with subsequent calls returning each row in turn. When no more data is available, the method returns NULL.

The numrows and numfields methods return the number of rows and columns returned for a statement handle.

Displaying Error Messages

$errno = $dbh->errno; $errstr = $dbh->errstr;

When the errno and errstr methods can be called on a database handle, they return the error number and error message of the most recent query executed using that connection.

To find connection errors, use the same methods on the Mysql object:

$errno = Mysql->errno; $errstr = Mysql->errstr; 

Closing a Connection

There is no specific DBI method to close a database connection. Resources are de-allocated automatically when the program exits, but if you want to free resources before the end of a problem, you can simply use the undef command on the handle.

A Sample Perl Script

Listing 8.2 is a sample program that establishes a database connection, executes a query against the sample database, and outputs the result in a tabular format.

Listing 8.2. Executing a Query Using Perl DBI

 #!/usr/bin/perl use Mysql; /* Establish a database connection */ $dbh = Mysql->connect(undef, "dbname",                      "username", "password")    or die ("Failed to connect to database: Error "                                  . Mysql->errstr); /* Execute a query */ $sql_statement = "SELECT book_id, cond, title                   FROM book"; $sth = $dbh->query($sql_statement)   or die ("Error executing query: Error " .                                 $dbh->errno); /* Loop through the result set to display it */ while (@row = $sth->fetchrow) {   for($i=0; $i<$sth->numfields; $i++) {     print $row[$i] . "\t";   }   print "\n"; } 

The output of this script looks just like the output from Listing 8.1.