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):
Connecting to MySQL
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
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
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
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
The output of this script looks just like the output from Listing 8.1.