Web Database Connectivity with Perl
Presentation by Ronald Blaschke (11/19/1999)
System Architecture
CGI
- Interface described on the CGI RFC Project
Page
- Used by HTTP server as interface to external programs
- HTTP server recognizes incoming CGI request
- -> sets some environment variables for the requested process
- -> spawns the requested process
- -> connects the TCP/IP connection to the programs STDIO
DBI
- Source available through
CPAN
- Win32 binary available from
ActiveState
- General DB Interface module and specific DB Driver required
The Form
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<HTML>
<HEAD>
<TITLE>Course Query</TITLE>
</HEAD>
<BODY>
<H1>Course Query</H1>
<FORM METHOD="GET" ACTION="/query.pl"
ENCTYPE="application/x-www-form-urlencoded">
<P>What's your name? <INPUT TYPE="text" NAME="name" >
<P><INPUT TYPE="submit" NAME=".submit">
<INPUT TYPE="reset">
</FORM>
<HR>
</BODY>
</HTML>
Example
#!/Perl/bin/perl.exe
## import CGI and its HTML methods
use CGI qw/:standard start_ul end_ul/;
## import Database Interface module
use DBI;
## this is called if a fatal exception is thrown
sub last_breath
{
print p, "Sorry, the database is currently down! ",
"Please try again later.";
print p, "But you may enjoy the error message if you want to: ",
$_[0];
print end_html;
}
## take a last breath before dying
$SIG{__DIE__} = \&last_breath;
## the HTTP header
## Content-Type: text/html
print header;
## the HTML form
print start_html('Course Query'),
h1('Course Query'),
start_form(-method=>"GET"),
p,"What's your name? ",textfield('name'),
p,submit,
reset,
end_form,
hr;
## param returns the list of supplied parameters; the empty list
## is false in a boolean context
if (param()) {
$name = param('name');
print p,"Your name is ",em($name);
## connect to database with empty username and password,
## abort script (die) on any error
$dbh = DBI->connect("dbi:ODBC:School", "", "", {RaiseError => 1});
## check if student is in database
## prepare the SQL statement
$sth = $dbh->prepare("SELECT SSN FROM Student WHERE Name='$name'");
## execute it on the database
$sth->execute();
## execute single fetch; returns the data as an array (which is true
## in a boolean context), or the empty list (which is false in a boolean
## context) if no more rows are found.
if (($SSN)=$sth->fetchrow) {
## get course list from database
$sth = $dbh->prepare("SELECT Course.Title FROM Course, Enroll ".
"WHERE Course.ID=Enroll.ID AND Enroll.SSN=$SSN");
$sth->execute();
print p,"Your selected courses are:";
print start_ul;
## this loops over all selected database rows
while(($course)=$sth->fetchrow) {
$courses++;
print li("$course");
}
print end_ul;
print "Found $courses courses in database.";
}
else { ## no row returned, therefore no such name in database
print p,"No such student in database.";
}
print hr;
## close connection to database
$dbh->disconnect();
}
print end_html;
References