SELECT Queries and Perl - Introduction to DBI (
Page 4 of 4 )
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.