Use the Perl DBI for connecting to a MySQL database

by Contributor Melonfire | Nov 09, 2005 9:22:00 PM

Tags: Scripting languages, Programming languages, Development tools, OPEN SOURCE, Databases, Contributor Melonfire, Perl, Perl DBI, MySQL, database

Takeaway: This tutorial shows you how to use Perl to communicate with MySQL, by introducing you to the important methods supported by the Perl database interface (DBI).

One of Perl's coolest modules is the Perl database interface (DBI). This module offers a unified interface to different databases, providing a series of generic functions that are internally translated into native function calls. This makes it extremely easy to take advantage of a database and create dynamic Web pages using Perl.

A commonly-used database for Web sites is MySQL, a free, open-source SQL implementation. This tutorial shows you how to use Perl to communicate with MySQL, by introducing you to the important methods supported by the DBI, and providing a simple script template for you to use in your development. It assumes that you have a working MySQL and Perl installation.

Download and install

To begin, download and install the Perl DBI module and the MySQL DBD driver by running the following commands at your Perl prompt:

Note: You can also manually download and install the DBI and the MySQL DBD.

The Perl DBI and MySQL DBD should now be installed to your system.

Then, create an example table for your SQL queries, by entering the following commands at the MySQL client prompt:

mysql> CREATE TABLE users (id INT(4) PRIMARY KEY, username VARCHAR(25), country VARCHAR(2));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO users VALUES (1, 'john', 'IN'), (2, 'tom', 'US'), (3, 'layla', 'US');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

Once the table has been created, proceed to build a script template using DBI methods (see Listing A).

Listing A

#!/bin/perl

# load module
use DBI;

# connect
my $dbh = DBI->connect("DBI:mysql:database=db2;host=localhost", "joe", "guessme", {'RaiseError' => 1});

# execute INSERT query
my $rows = $dbh->do("INSERT INTO users (id, username, country) VALUES (4, 'jay', 'CZ')");
print "$rows row(s) affected\n";

# execute SELECT query
my $sth = $dbh->prepare("SELECT username, country FROM users");
$sth->execute();

# iterate through resultset
# print values
while(my $ref = $sth->fetchrow_hashref()) {
    print "User: $ref->{'username'}\n";
    print "Country: $ref->{'country'}\n";
    print "----------\n";
}

# clean up
$dbh->disconnect();

Four Steps

There are four simple steps to follow when executing an SQL query with the Perl DBI.

1. Begin by initializing a database handle with a call to the connect() method. This method accepts connection parameters as a string containing the database type ("mysql"), the host name ("localhost") and the database name ("db2"). The database user name ("joe") and password ("guessme") is also provided to the connect() method as the second and third argument
2. Create the SQL query string, and execute it with either the do() method or the prepare() and execute() methods. Use the do() method for one-off INSERT, UPDATE or DELETE queries, but use the prepare() and execute() methods for SELECT queries. The result object of these methods will be different depending on the query type and whether or not it was successful. Successful SELECT queries return a result object; successful INSERT/UPDATE/DELETE queries with do() return the number of rows affected; and unsuccessful queries return an error.
3. For SELECT queries, the result object may be processed further to extract data from it. Used in a loop, the fetchrow_hashref() method retrieves each record as a Perl hash. You access individual fields of the record by calling the appropriate key of the hash.
4. End the session with a call to the disconnect() method.

This script template will save you some time when you next sit down to write MySQL database connection code in Perl. Happy coding!

Print/View all Posts Comments on this article

Perl and MySQL Mark W. Kaelin | 11/09/05

what language? Jaqui | 11/09/05