Dabbling in Live Databases: Pt 3 - Page 2

Preparing for GUFE

If you've followed along either of the last two articles in the Dabbling with Live Databases mini-series, you're probably prepared for GUFE already. GUFE requires that your host system is installed with Perl, the DBI module, and the DBD module for the particular database system you are using. For instance, on a Windows machine with Microsoft Access, you would need the DBD::ODBC module; on a Un*x system with MySQL you would need the DBD::MySQL module. If none of this makes sense, it is very important that you backtrack to the first two installments in this mini-series.

Caveat: if GUFE seems to stumble, especially if it fails to provide a list of available tables to view, you probably do not have the latest DBI and DBD modules installed. Be sure that you do, and install them if necessary, because older versions of these modules do not support the ability to request a list of available tables.

Let's Walk GUFE

The bulk of this article is a walkthrough of the GUFE Perl script. In doing so, we'll touch on a variety of Perl issues, from those encountered last month to some from much earlier in the Perl You Need to Know series. While we will highlight segments of the GUFE script, you may wish to consult the entire unmodified code. Some of the code lines are quite long and may need to be wrapped on this web page -- the unmodified reference code contains the original line lengths.

First, it is worth noting that GUFE relies on the web template technique that we saw in Part 4 of the Perl You Need to Know. Briefly, this means that GUFE "inserts" its output into a pre-existing HTML document. This relieves GUFE of the need to recreate the entire web page on each call, and provides the web designer with much greater flexibility in designing the surrounding elements of the page. Simply put, GUFE dumps its output in place of a <!--FRONTEND--> comment found within the source of the HTML file.

Taking an overview of GUFE we can see that it is quite simple. GUFE is built mostly of Perl subroutines, each of which performs a focused action. Once the connection to the database has been established, GUFE translates CGI queries (which are created by the browser, usually based on the settings of form fields) into SQL statements. These SQL statements are passed to the database, and GUFE retrieves the results, formatting them for the screen. Various results, such as field labels, are hyperlinked with rigged CGI queries that will, when clicked, intentionally cause GUFE to generate certain SQL queries (for instance, the field labels are rigged with sort parameters which in turn are translated by GUFE into SQL statements with sorting clauses).

The easiest way to understand GUFE is from the inside out. We'll walk through GUFE in several stages, starting, of course, from the top.

use CGI;
use DBI;

#setup database parameters
my $DBD="mysql";
my $dbase="Clients";
my $dbuser="";
my $dbpassword="";

#prepare for output to browser
$cgiobj=new CGI;
print $cgiobj->header;

GUFE begins by including our two crucial modules, CGI for interaction with the web browser, and DBI for interaction with the database.

For convenience, we assign several connection parameters for the database: the DBD protocol ("ODBC" in the case of Microsoft Access), the name of the database, and a username/password if needed to connect to the database. Some systems are case sensitive (Un*x), and some are not (Windows); it's best to pay attention to case. For example, the DBD protocol for MySQL must be all lowercase "mysql" -- you can find the proper syntax from the DBD module documentation for your database.

Standard CGI practice leads us to establish a handle for this instance of the CGI object, $cgiobj, and we output a standard HTML header to prepare the browser for incoming data.