Introduction to Databases for the Web: Pt. 3 - Page 2

Using Perl 5 and the DBI Module to Communicate With Databases

However, for purposes of demonstration, in this part we will show an example of writing a CGI script in Perl 5, using the DBI (Database Independent Interface) module supplied for free with the normal Perl 5 distribution.

As an example, our DBI-aware Perl 5 CGI Script will be used to access an MS Access Database on a Windows 95 system using a local web server called Sambar.

Of course, as we will see in just a bit, the fact that we use Perl 5 and the DBI module means, that the code we develop here will work on UNIX, NT or Macintosh without any modifications.

It will also work "out of the box" for most commercial and public domain databases available for the web including Oracle, Sybase, Informix, Postgres, DB2, etc.

Finally, you should know that you need not use Sambar as your Web Server. Any Web Server that can handle CGI will do. I am just using Sambar because it is free and it is what I happened to download. In retrospect, I would probably recommend Apache since it is available on so many platforms.

In short, the beauty of Perl and of the DBI module is that they are both designed to be highly cross platform and non-proprietary.

(Note that since you are a web developer, I am assuming that you already know Perl 5 for CGI. If you don't, or if you need a refresher, check out Web Ware at WDVL! The e-zine has tutorials on Perl and Perl 5 for web developers.)

The DBI Module

The DBI Module is an incredibly useful tool because it abstracts away the need to learn how to communicate with every type of database out there. Consider what a pain it would be if you had to write a CGI script for every proprietary database engine.

Now imagine how much extra work you would have if the management decided to change database vendors on you after you had already written your code!

What the DBI module does for you is to provide a single interface to send SQL commands to any proprietary database. Essentially, you write all your code to a generic standard, and move it from database to database without having to re-code your application.

The secret to the DBI module is the extensive library of DBD (Database Dependent) modules that come with the standard distribution of Perl 5. These DBD modules provide the code that hooks up DBI to a proprietary database driver.

Of course, since these DBD modules are already written for almost any Database you would consider using, you needn't do any of that hard work. All you need to worry about is satisfying the requirements of the DBI interface. Everything else is handled behind the scenes!

It looks pretty complex but believe me, it isn't. And it makes your life as a programmer incredibly easy. That is, although the communication takes place through so many actors, you, as the programmer, need not bother with the details of that communication. You talk to the DBI module, and the DBI module handles the multitude of proprietary database blue meanies by itself.

Fortunately, dozens of great module writers have done all that hard work for you. You'll see just how easy it is when we start going over code!

The DBI API

So how do you use the DBI module?

Well, as with any Perl 5 module, you simply use the "USE" keyword.

Once, the DBI module is loaded, you can then instantiate a database connection. Establishing a connection involves creating a "database handle" object by using DBI to connect to a database given a data source name, database user, database password, and a database driver.

Consider the following example in which we connect to a Database with the data source name of "MyCompany" using the username "selena", password "12mw_l", and the ODBC driver. Notice that the ODBC driver name is tagged to the beginning of the Database name.

use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany', 
                          'selena", 
                          '12mw_l'));

We'll show you how to setup a database name etc. when we cover Access later. However, you can imagine that regardless of what database you use on what system, you will be able to define these values and use them.

Once you have created a database handle object, you can do things with the object. Most likely, you will be sending SQL statements to the database via the database handle. To do so, you create a statement handle object by calling the prepare() method on the database handle object and then call the execute() method on the statement handle object. Consider the following code:

use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany', 
                     'selena", 
                     '12mw_l'));
$sql = "SELECT * FROM Employees";
$statementHandle = $dbHandle->prepare($sql);
$statementHandle->execute() || 
               die $statementHandle->errstr;

Once the sql has been sent to the database, the DBI module will store the results. To get to the results, you can use any number of useful statement handle methods. One of the most common methods is the fetchall_arrayref() method that returns all the returned database rows in a reference to an array of references to rows. Consider the following:

use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany', 
                     'selena", 
                     '12mw_l'));
$sql = "SELECT * FROM Employees";
$statementHandle = $dbHandle->prepare($sql);
$statementHandle->execute() || 
              die $statementHandle->errstr;
$arrayRef = $statementHandle->fetchall_arrayref;

Of course, once you have the reference to the array, you can dereference everything to access the data.

Finally, when you have massaged all the data, you close the database connection with the database handle object's disconnect() method:

$dbh->disconnect();

At the end of this part, I have included several examples of CGI script using DBI so you can see how you might build an application around the methods. Further, fantastic documentation for DBI can be found at http://www.hermetica.com/. However, I will also include a cursory API reference summary (based upon the DBI documentation by Tim Bunce) of the most used methods here so you can get a feel for what is available.

Note that there are all sorts of other more complex methods such as binding and error handling, but you should consult the documentation and the DBI tutorial referenced above. These topics are a bit beyond the scope of this tutorial.