Perl DBI

Summary: in this tutorial, you are going to learn how to use Perl DBI (Database Independent) module to interact with to various databases.

Perl DBI module provides a useful and easy-to-use API that allows you to interact with many of databases including Oracle, SQL Server, MySQL, Sybase, etc. In this tutorial, we are going to show you to interact with the MySQL database.

For a complete tutorial on Perl and MySQL, you can check it out the Perl MySQL tutorial.

Installing DBD::mysql Module

Before installing the DBD::mysql module, please ensure that you have MySQL server installed on your system.

First, you need to install MySQL driver to connect to MySQL databases. There are several ways to install a module. We are going to use CPAN shell to install the DBD::mysql module.

First, type the following command:

C:\>perl -MCPAN -e shell

cpan shell -- CPAN exploration and modules installation (v1.9800)
Enter 'h' for help.Code language: Perl (perl)

Second, to download and install  DBD::mysql module, you use the following command:

cpan> install DBD:mysqlCode language: Perl (perl)

You will see the installation progress via command line.

Connecting to MySQL database server

We will create a sample database called classicmodels that we download from the following link MySQL Sample Database.

To connect to MySQL database, you use the following syntax:

my $dbh = DBI->connect("DBI:mysql:classicmodels",'root','');Code language: Perl (perl)

You pass three parameters to the  DBI->connect() method:

• The first one is the data  source name. The database you are connecting is classicmodels.
• The second and the third parameters are username and password that you use to connect to MySQL. In this case, username is root and password is blank.

The DBI->connect() returns an object that you will use to interact with the database if the connection is established successfully.

If DBI->connect() fails, the $dbh is false. You can do a quick test after calling the DBI->connect().

if(!$dbh){
 die "failed to connect to MySQL database DBI->errstr()";
}else{
 print("Connected to MySQL server successfully.\n");
}Code language: Perl (perl)

You should always disconnect from the database server once you are no longer work with it. To disconnect from the database server, you use the disconnect() method of $dbh:

$dbh->disconnect();Code language: Perl (perl)

Now, you know how to connect to and disconnect from MySQL database server. Let’s execute some SQL queries.

Executing SQL queries with Perl DBI

Executing simple SQL query

If you don’t know anything about SQL, we recommend that you follow the SQL tutorial to get a basic concept.

The following program selects data from the employees table and displays the last names, first names and extensions of employees.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("DBI:mysql:classicmodels",'root','');

die "failed to connect to MySQL database:DBI->errstr()" unless($dbh);

# prepare SQL statement
my $sth = $dbh->prepare("SELECT lastname, firstname, extension FROM employees")
                   or die "prepare statement failed: $dbh->errstr()";

$sth->execute() or die "execution failed: $dbh->errstr()"; 

my($lname,$fname,$ext);

# loop through each row of the result set, and print it
while(($lname,$fname,$ext) = $sth->fetchrow()){
   print("$lname, $fname\t$ext\n");                   
}

$sth->finish();
$dbh->disconnect();Code language: Perl (perl)

How it works.

• First, you connect to the MySQL database using the DBI->connect() method.
• Second, you use  prepare() method of the database handler object, that accepts an SQL statement as an argument. The  prepare() method compiles the SQL statement and prepares it for execution. If the  prepare() method succeeds, it returns a state handler object $sth, otherwise, it returns false. To get the error message, you can use the  errstr() method.
• Third, you execute the query using the execute() method. The result is stored in the state handler object $sth.
• Fourth, you use the fetchrow() method of the state handler object to return the next row of the result set returning from the query. You copy the data to the corresponding variables $lname,  $fname and $ext.
• Fifth, we call the finish() method  of the state handler to adhere to good programming practices.
• Finally, we disconnect from the database server using the  disconnect() method.

Executing simple SQL query with placeholder

In this example, we are going to develop more a complex example of querying data:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("DBI:mysql:classicmodels",'root','');

die "failed to connect to MySQL database:DBI->errstr()" unless($dbh);

# prepare SQL statement
my $sql = <<EOF;
 SELECT lastname, firstname, extension 
 FROM employees 
 WHERE lastname = ? OR firstname = ?
EOF

my $sth = $dbh->prepare($sql)
                 or die "prepare statement failed: $dbh->errstr()";

my($lname,$fname,$ext);
my($name, $answer);

print("\nPlease enter the employee firstname or lastname:");
while(<STDIN>){
   $name = $_;
   chomp($name);
   $sth->execute($name,$name) or die "execution failed: $dbh->errstr()"; 
   # loop through each row of the result set, and print it
   while(($lname,$fname,$ext) = $sth->fetchrow()){
      print("$lname, $fname\t$ext\n");                   
   }

   print("\nDo you want to continue? (Y/N)");
   $answer = <STDIN>;
   chomp($answer);
   last if $answer eq 'N';

   print("\nPlease enter the employee firstname or lastname:");
}

$sth->finish();
$dbh->disconnect();Code language: Perl (perl)

How it works.

• We put placeholders (?) to the SELECT statement so that we can pass parameters when we call the execute() method.
• The program asks you to enter either first name or last name of the employee. Based on the input, it looks up the employee from the  employees table and display the first name, last name, and extension.

The following screenshot demonstrates our test of the program:

In this tutorial, you’ve learned how to interact with the MySQL database server using Perl DBI module.