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 asINSERTandSELECT 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 installDBIand the MySQLDBDmodules.
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 installDBI. When successful, it is time to install the MySQL driver. The name of this module isDBD::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, anduse strict;, weusetheDBI module. Then we see a call to theDBI->connect()method. When we talked about object-oriented programming in Chapter 12, we mentioned that most modules use the methodnew()as their constructor.DBI, however, usesconnect(). This is fine—any method name that we choose can be the constructor, and since to construct aDBIobject we must connect to a database,connect()seems a logical choice.
There are three arguments toconnect(): 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 withDBI, followed by a colon, the termmysqlsince we are using the MySQL server andDBD::mysql, a colon, and the database to which we are connecting, heremusicians_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 textmysqlin the data source tooracle. Provided thatDBD::oracleis installed on our machine and we don’t use any MySQL-specific queries, the script will work perfectly. Talk about portable!
The return value ofDBI->connect()is an object that we can use to do things with the database. We call this the database handler so we name it$dbh. After the call toDBI->connect(), we check the value of$dbh.
die "connect failed: " . DBI->errstr() unless $dbh;
This makes sure$dbhhas a true value. IfDBI->connect()fails, it returns a false value to$dbh, so wedie(), complaining that something went wrong with the database connection. The functionDBI->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 thatdie()prints.
If all is well, we print a cheerful message and disconnect from the database.
print "connect successful!\n";
$dbh->disconnect();
We use thedisconnect()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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X). Check it out today at your favorite bookstore. Buy this book now.
|
|
