Home
 

Web Database Connectivity with Perl

Presentation by Ronald Blaschke (11/19/1999)

System Architecture

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