Introduction to Databases for the Web: Pt. 3

Monday Oct 19th 1998 by Selena Sol

Enough theory, time for action. Part three in this introductory series introduces you to some of the main components you might want to use to get your database up and running on the Web.

Server Side Database Communication with CGI

So far in this tutorial we have remained pretty airy. We have learned lots of "database theory" and "abstract" SQL communication protocols.

But what you probably came to learn was how you could actually put a real-live database on the web!

Well, to be fair, understanding all that abstract stuff that we have discussed so far is necessary for you to be able to do what you want to do.

Unfortunately, putting your database on the web turns out to be a pretty complex feat involving all sorts of technologies. So, there are a bunch of things you needed to learn in order to get the whole kit-and-caboodle operational.

However don't worry, in this part, we are going to put it all together and get your data webified.

Nevertheless, before we get into the nitty gritty lets recap a bit and also provide an overview for what we are going to do in this part.

Chains of Communication

As you recall from Part One, a Web Database follows the client-server database model. A Database Engine sits on some central computer somewhere and serves data to multiple web-based clients (perhaps lots of customers using Netscape Navigator).

Because we are dealing with web-based clients however, we must also have a Web Server that handles requests from web-browsers and then forwards them to the Database. Likewise, the web server will wait for the database to respond and then pass on that response to the waiting web browsers. The whole interaction is much simpler as a picture.

(Note that since you are a web developer, I am assuming that you understand the interaction between web browsers and web servers. If you don't understand this, or if you need a refresher, check out Web Programming 101 at WDVL!)

Web Browser-Web Server-Database

Of course, the problem with the above model is that it does not exactly tell the whole story. Actually, though Web Servers are built to talk to Web Browsers, they are not built to talk to Databases. Thus, in order for the Web Server to talk to a Database, it requires a helper (sometimes called Middle Ware).

The most basic type of Middle Ware is a CGI script that is written to translate requests from the Web Server to a format that the Database can understand, and to translate Database responses into something the Web Server can send back out to the Web Browser and that the person using the web browser can understand.

Web Browser-Web Server-CGI Script-Database

The CGI Script will be responsible for understanding what the Web Server is saying and will also be responsible for knowing how to talk to the Database. This type of CGI script is seriously multilingual!

Web Browser-Web Server-CGI Script-Database

Of course, the CGI script can be written in any computer programming language and can use all sorts of inventive or proprietary methods to talk to the Database.

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!

[No abstractions to Database]

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!

[Web Browser-Web Server-CGI Script-Database]

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!


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', 

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', 
$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', 
$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:


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.

General DBI Class Methods
Name Usage Description
connect() $dbHandle = DBI-> connect (DBI:$driverName:$dataSource, $usrName, $passwrd);

$dbHandle = DBI-> connect ($dataSource, $usrName, $passwrd, $driverName);

This method establishes a connection to the specified Data Source and returns a database handle object.

Note that if you define the driver name as the fourth parameter, you need not tag the DBI:$driverName along with the $dataSource name.

available_drivers() @drivers = DBI-> available_drivers();

@drivers = DBI-> available_drivers ($warnFlag);

As you can see, this method returns an array of drivers that the module can see. It gets the list by searching through the @INC array for DBD modules. If you pass a true value as a parameter, warnings about hidden drivers will be quelled.
data_sources() @dataSources = DBI-> data_sources($driver); Returns a list of the data sources available for a given driver if the driver supports the method.
trace() DBI->trace ($level);

DBI->trace ($level, $file);

Provides tracing functionality such that the $file is appended to with trace information. A Level 0 rating disables tracing and a level 2 tracing provides a detailed trace.
neat() $neatvalue = DBI::neat($value, $maxLength); Formats the specified value such that strings are quoted, undefined values are replaced with "undef", unprintable characters are replaced with a ".", and string will be truncated and ended with "..." if longer than $maxLength
neat_list() $delimitedList = DBI::neatList(\@listRef, $maxlength, $delimiter); Formats an array by calling neat() on each element and forming a string by joining the elements with the specified delimiter.
dump_results() $dbRows = DBI::dump_results ($statementHandle, $maxLength, $lineSeparator, $fieldSeparator, $fileHandle); Gets all the rows from the statement handle object, calls neat_list() on each row and prints the results to $filehandle that is <STDOUT> by default.
General Handle Methods
Name Usage Description
err() $result = $handle->err(); Gets the error code returned from the database.
errstr() $result = $handle->errstr(); Gets the error message returned from the database.
state() $state = $handle->state(); Gets the SQLSTATE error code.
trace() $handle->trace ($level);

$handle->trace ($level, $file);

Provides tracing functionality such that the $file is appended to with trace information. A Level 0 rating disables tracing and a level 2 tracing provides a detailed trace.
Database Handle Methods
Name Usage Description
prepare() $statementHandle = dataSourceHandle -> prepare($sql); Prepares a statement for execution.
do() $rowCount = $databaseHandle -> do($sql); Prepares and executes an SQL statement and returns the number of rows returned.
commit() $rowCount = $databaseHandle -> commit(); Makes permanent the last set of database changes if supported.
rollback() $rowCount = $databaseHandle -> rollback() Undoes uncommitted changes if supported.
disconnect() $rowCount = $databaseHandle ->disconnect() Disconnects from the database.
ping() $rowCount = $databaseHandle - > ping() Tries to figure out if the database server is still available.
quote() $sql = $databaseHandle - > quote($string); Spiffys up a string for an SQL statement.
Statement Handle Methods
Name Usage Description
execute() $value = $statementHandle -> execute(); Executes a prepared statement.
fetchrow_arrayRef() $arrayRef = $statementHandle -> fetchrow_arrayref(); Gets the next row of data as a reference to an array holding the column values.
fetchrow_array() @array = $statementHandle -> fetchrow_array(); Gets the next row of data as an array.
fetchrow_hashref() $hashRef = $statementHandle -> fetchrow_hashRef(); Gets the next row of data in which the keys to the hash reference are the column names and the values are the column values.
fetchall_arrayref() $reference = $statementHandle -> fetchall_arrayref() Gets all the rows as references in a referenced array.

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.

Getting the Pieces

So now that we understand what it is that we are building, let's continue by assembling the tools and raw material.

As I said before, we are going to demonstrate this process on a Windows 95 machine. I chose to demonstrate on a Windows box because I know that even if you are a UNIX developer, you have access to a Windows machine that you can use to develop a test site. Of course, most likely, you develop on a Windows system. Either way, everything we go over can be performed on any platform, just with different software packages. I will try to point out where things might be different as we go along.

Installing Perl

The first thing you will need to do is download Perl 5. You can do this easily, by going to www.perl.com There are two versions of Perl available and the differences are explained at www.perl.com. However, I use Gurusamy Sarathy's binary version of Perl for extra module support.

If you are using UNIX, you can download the .tar.gz (Note if you are using UNIX it is almost assuredly already installed and/or installable by your systems administrator. You probably should not do it yourself).

If you are using Windows or Macintosh, download the ZIP file. In any case, unzip utilities for all the platforms are available at www.shareware.com.

When you have downloaded the zipped file, you should uncompress it on your local hard drive. On Windows, I tend to extract it into a directory like c:\Perl\Perl5.004\. But the directory you use does not really matter much.

Windows Explorer example

Note for Windows and UNIX users, you might want to add the Perl executable to your path, but it is not necessary for this tutorial. In case, you want to add it to your path, here is an example of the line I use in my autoexec.bat file. In UNIX you would typically define the PATH in .login, though it is better to install Perl in a standard directory that is already defined in your PATH such as "/usr/local/bin".

Autoexec.bat example

Okay, once Perl is installed, try running a simple Perl program to make sure everything is hunky dory. Here is the program I use:

print "hello world";

Test Perl Script

Installing a Web Server: Sambar

Now that you have Perl installed and running, it is time to download a Web Server that you can use locally to test CGI scripts.

But how can I run a web server on my computer that is not hooked up to the web?

Well, you "can" run a web server locally, but it will only be useful for testing and development. That is, you will create a network composed of one computer and then use your web browser to access the web server!

You can easily pick up a free web server for any operating system you are using. For UNIX and Windows I recommend Apache. You can also use the Sambar Web Server for Windows. In the case of this tutorial, I downloaded Sambar from www.sambar.com because it is so incredibly easy to install. However, which Web Server you choose will not make a difference for this tutorial. They will all work like Sambar for our purposes.

Once you download the self-extracting executable file from www.sambar.com, you run the setup program and the server is installed. There is not much else to it. The server is fairly featureless, but for testing purposes that can be just what the doctor ordered.

Once that is done, try running Sambar! You should see the status window. Once that is done, you can try connecting to your personal web server. Just use your IP address in the location field in Netscape or Internet Explorer or use "localhost". You can see in the following image, that my Netscape document request is being registered and handled by Sambar.

[Sambar and Netscape]

Running CGI Applications on a Single Station Local Area Network

You still with me? Okay the next thing we need to do is try running some CGI applications with your new server. To do so, simply dump a simple CGI script in the cgi-bin directory under the sambar directory. Here is one I use:

Click here for code example 1.

Setting up a Sample Database

Next, you need to make sure that you have set up a sample database to test on. In the previous parts we have introduced MSAccess which is a good database to practice with since it is fairly lightweight and user-friendly. Of course, as we have said before, what database you use will not really matter. I am just using MSAccess to demonstrate.

Go ahead and setup a database with two tables: Employees and Customers with some sample data in each. In fact, here are the tables I am using for your reference:

[Access Tables]
(Click image for full size)

When you are done with data entry, you'll need to register your database with your operating system. On Windows, you simply need to use the 32 bit ODBC Control panel.

[ODBC Control Panel]

The "Add Wizard" will walk you right through assigning a system ODBC name to the Access database you setup. Notice that I made four of them: "Access", "db1", "Extropia", and "MyCompany".

[ODBC Setup]

Putting it all together with a DBI-AwareCGI Script

I have prepared a little DBI script for us to walk through. You can copy this over to your system, change the setup variables and it should run on your system as well. The code of this should all be straight forward and you should have all read the Perl tutorials at Web Ware, so I won't spend much time documenting the code.

     # First we identify the location of 
     # the Perl interpreter. You will need 
     # to change this line to reflect the
     # location of Perl on your system.


     # Next we will tell Perl that we are
     # going to USE the DBI and CGI 
     # modules.
     # We will use the CGI module mainly
     # to handle incoming form data.
     # The CGI::CARP module has a nice
     # feature called "fatalsToBrowser"
     # that sends error messages to the
     # web browser window so that the
     # user does not get a meaningless 
     # 500 Server Error message.
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

     # Setup some implementation 
     # specific variables. $dbname
     # is going to be the Data 
     # Source name that you 
     # assigned to your database in
     # the 32Bit ODBC Control 
     # Panel plus the DBI:ODBC
     # pre-tag.  I did not setup
     # any security on my database
     # since it is only for local
     # testing, so we can leave 
     # those values blank.

$dbName     = "DBI:ODBC:MyCompany";
$dbUserName = "";
$dbPassword = "";

     # We will create a new CGI 
     # object and use it to send
     # out the HTTP header and
     # to parse out the incoming 
     # form variables "requestType"
     # and "sql".  You will see 
     # where those form variables 
     # come from in just a bit.

$dataIn       = new CGI;
$requestType  = $dataIn->param('requestType');
$sql  = $dataIn->param('sql');

     # Next we will check to see 
     # if there is a value coming
     # in from a form for the
     # variable "sql".  If there
     # is no value, then we know
     # that the user has not yet
     # seen the submission form.
     # In that case, we will send
     # them the HTML form so they
     # can actually submit a value
     # for "sql". The following 
     # screen shot shows you what
     # will be returned.

[SQL Entry Form]

if ($sql eq "")
    print qq!
             TEXT = "#000000">
             ACTION = "dbi_demo.cgi">
    <TABLE BORDER = "1">
    <TH>Enter SQL 
                        SIZE = "40" 
                        NAME = "sql">
                        NAME = "requestType" 
                        VALUE = "Submit SQL">

     # If there was a value for $sql, we know
     # that the user has already seen the
     # HTML form and has submitted some
     # SQL for us to process.  In that case,
     # we will open a connection to the 
     # database, execute the SQL, gather
     # the result set and display it
     # to the user.  In the case of a
     # simple SELECT, we will display
     # the results in an HTML table.
     # If, on the other hand, the SQL
     # was a DELETE, MODIFY or INSERT,
     # we will let them know that the
     # operation was successful.
     # Notice that you need to do a lot
     # of dereferencing with the returned
     # rows :)

[SQL Entry Form]

    $dbh = DBI->connect($dbName, 
    $dataObject = $dbh->prepare($sql);
    @dbRows = $dataObject->
    if ($sql =~ /^SELECT/i)
        print qq!
        <TITLE>SQL Statement 
        <BODY BGCOLOR = "#FFFFFF" 
                 TEXT = "#000000">
        <TABLE BORDER = "1">!;
        foreach $rowReference (@dbRows)
            foreach $columnReference 
                print qq!<TR>!;
                foreach $column 
                    print qq!<TD>
                print qq!</TR>!;
        print qq!
        print qq~Your SQL Query has been 
               processed, please hit the 
               back button and submit a 
               SELECT to see the changes!~;

Well, however simple that application is, it should be enough to get you started. You will have to design your own application logic for a more useful database tool, but all the components for a full featured application are demonstrated here.

As it so happens, I wrote a more complex application that you can use if you'd like. This application handles inserts, selects, updates and deletes in a much more user-friendly way. If you want to see the code, click here. Otherwise check out the screen shot of the interface.

[Demo Application]

Mobile Site | Full Site