DBI is the Database Independent module and was written by Tim Bunce. It is a collection of APIs that allow a programmer to connect to and access a database. As the name implies, the module allows us to write programs to access a database independent of the type of database. We can write a program to query an Oracle database, or a Sybase database, or MySQL, or Postgres, or ODBC, and the list goes on and on. All we need on our computers is the DBI module and the appropriate Database Driver (DBD).

Note  Each of the mentioned databases has its own dialect of SQL. Most implement the basic commands such as INSERT and SELECT in a similar way, but when it comes to the details of specific commands, they are sometimes implemented slightly differently from database to database. Keep this in mind if you are creating a Perl script that you want to port from one type of database to another—use the common form of each command even if a database has a nifty feature that you can use that is not supported elsewhere.

We are using MySQL server in this chapter, so we need to install DBI and the MySQL DBD modules.

Installing DBI and the DBD::mysql

The first step to using DBI is to install the appropriate modules. The first module we need is DBI. As this is being written, the latest version of DBI on CPAN is 1.42, but as usual this version may not be the same by the time you read this.

Follow the instructions in Chapter 13 on installing modules to install DBI . When successful, it is time to install the MySQL driver. The name of this module is DBD::mysql .

Connecting to the MySQL Database

Our first Perl program will simply connect to the MySQL database. If it works correctly, we know that DBI and DBD::mysql were installed correctly, and the real fun can then begin. Let’s look at an example ( connect.pl ):

#!/usr/bin/perl -w
# connect.pl

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

print "connect successful!\n";

$dbh->disconnect();

After the shebang, comment, and use strict; , we use the DBI module. Then we see a call to the DBI->connect() method. When we talked about object-oriented programming in Chapter 12, we mentioned that most modules use the method new() as their constructor. DBI , however, uses connect() . This is fine—any method name that we choose can be the constructor, and since to construct a DBI object we must connect to a database, connect() seems a logical choice.

There are three arguments to connect() : the DSN, also known as the data source name, the username of the user, and their password. In this invocation, the data source name is

"DBI:mysql:musicians_db"

All data sources will start with DBI , followed by a colon, the term mysql since we are using the MySQL server and DBD::mysql , a colon, and the database to which we are connecting, here musicians_db .

Note  Let’s say that one day we want to port our database from MySQL to some other database server such as Oracle. In this script, all we need to do is change the text mysql in the data source to oracle . Provided that DBD::oracle is installed on our machine and we don’t use any MySQL-specific queries, the script will work perfectly. Talk about portable!

The return value of DBI->connect() is an object that we can use to do things with the data base. We call this the database handler so we name it $dbh . After the call to DBI->connect() , we check the value of $dbh .

die "connect failed: " . DBI->errstr() unless $dbh;

This makes sure $dbh has a true value. If DBI->connect() fails, it returns a false value to $dbh , so we die() , complaining that something went wrong with the database connection. The function DBI->errstr() will report the error of whatever just went wrong, so as a help to the user we will include this information in the string that die() prints.

If all is well, we print a cheerful message and disconnect from the database.

print "connect successful!\n";

$dbh->disconnect();

We use the disconnect() method to disconnect from the database. This is not really necessary since Perl will disconnect us when the script terminates, but it is still a polite thing to do.

Here is what happens if we execute this program:

$ perl connect.pl
connect successful!
$

Now that we can connect, it is time to execute an SQL query.

Please check back next week for the conclusion to this article.