Troubleshooters.Com presents

November 1998 Troubleshooting Professional Magazine

Copyright (C) 1998 by Steve Litt, All rights reserved. Material provided as-is, use at your own risk.

Post Doctorate: Database Enabled Web App

By Steve Litt, Steve Litt's email address
I'd like to thank Alligator Descartes (descarte@arcana.co.uk) for his definitive DBI::DBD website (link in URL's section) for giving me a starting point to Perl DBI::DBD. Thanks also to Red Hat for the documentation on their install CD, which describes setting up PostgreSQL and the use of psql.

Note: Make it easy on yourself. Make sure that when you installed Linux you configured the network during installation, installed everything during installation, and during installation set postgreSQL to run on bootup. If not, your easiest path is probably to take the time to do the preceeding tasks in this magazine over again. PostgreSQL is a serious administration task, and the little documentation you'll find on it isn't as clear as you might like it.

Get psql Working for User  postgres

If your Linux installation was done as recommended, you should find a /var/lib/pgsql with little or nothing in it, and a /usr/lib/pgsql with several files. While the latter must have been setup by a package or installation, the former can simply be created or recreated. In fact, it may be best to start with /var/lib/pgsql completely empty.

If your Linux installation was done as recommended, you should also find that there's a user postgres. In userconf->Normal->User_accounts->postgres->Base_info, this user looks like this:
  
Login name               postgres______________________
Full name                PostgreSQL Server_____________
group                    postgres_____________________@
Supplementary groups     ______________________________
Home directory(opt)      /var/lib/pgsql________________
Command interpreter(opt) /bin/bash____________________@
User ID(opt)             100___________________________

Group postgres simply has a name and an id in userconf.

If postmaster is being run (you can find out with ps ax | grep postmaster), then it's almost certainly being run from the postgres uid. If uid postmaster hasn't been created, you can do it with adduser then passwd then userconf, but it might be better to reinstall, because you're probably not running postmaster at bootup.

Assuming all the above is correct, you're ready to start.

Create the Login Script for Uid postgres

Log in as postgres. If you don't know the password, open up a shell as root and change the password with passwd. Then log in as postgres, and add the following lines to /var/lib/pgsql/.bash_profile: 
PGDATA=/var/lib/pgsql
PGLIB=/usr/lib/psql
export PGDATA PGLIB
If necessary, create the file /var/lib/pgsql/.bash_profile. When you're done, log out and log back in. These two commands should show what you'd expect from that script: 
echo $PGDATA
echo $PGLIB
If they don't show what you'd expect from the .bash_profile file, troubleshoot. Note that every single user who needs access to PostgreSQL must have those three lines in his or her login script.
 

Initialize the DBMS

WARNING: Absolutely, positively do NOT do this as user root. Do it as postgres only!

Do this command: 

initdb
If it gripes about 'does not find the file '/usr/lib/psql/local1_template1.bki.source', suspect a bad $PGLIB. Check that the environment variable is spelled correctly, and that it points to the location of local1_template1.bki.source. It will probably be /usr/lib/pgsql. If not, use the locate command to find it. If you need to change your .bash_profile file, be sure to log out and log in as postgres, or at least do the
source .bash_profile
command.

Once the initdb runs, you should find several files in your /var/lib/pgsql directory:

All these files were created by the initdb command.

If initdb gripes about PG_VERSION already exists, create an empty /var/lib/pgsql, chown it to postgres.postgres, re-do the .bash_profile, and try again. It should work.

Create a database

Now do this command: 
createdb mydb
If it gripes about your not having rights, make sure you're logged in as postgres, and if not, try again as postgres. Otherwise, read carefully and troubleshoot. Once the create went OK, we can go into interactive sql.

Work with command line interactive SQL

Do this command: 
psql mydb
This will run interactive sql against the mydb database. If it gripes, try this: 
psql template1
If that works, something went wrong with your createdb statement. if the psql template1 command also fails, read error messages and troubleshoot. Once the psql mydb command succeeds, you'll get the following prompt: 
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL


   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: mydb

mydb=>
That prompt tells you several important things. First, the help command is \?. Second, you can quit out of psql with the \q command. Third, every sql statement must be terminated with a semicolon or a \g. If you forget, the command simply will not run, and the next line will have a hyphen in front of the greater_than, instead of an equal sign. If that happens, you can simply put a semicolon and hit enter to execute the command. However, if you type anythinge else and then put the semicolon, you'll get a syntax error. That's OK, just start over again.

Create a table

From the mydb=> prompt, type in this SQL command: 
create table mytable(lastname char(16), firstname char(12), email char(40));
If you can't create the table, and you're logged in as postgres, it's probably an SQL problem.

View your new empty table

Do this command: 
select * from mytable;
You should see this result: 
mydb=> select * from mytable;
lastname|firstname|email
--------+---------+-----
(0 rows)

mydb=>

Add some data to the table

From the mydb=> prompt, type in this SQL command: 
insert into mytable (lastname, firstname, email) values ('Litt', 'Steve', 'Steve Litt's email address');
That creates a table with three columns, lastname, firstname, and email.
If all goes well, you should get a prompt that looks like this: 
mydb=> select * from mytable;
lastname        |firstname   |email
----------------+------------+----------------------------------------
Litt            |Steve       |Steve Litt's email address
(1 row)

mydb=>

Get out of psql

Just type \q at the mydb=> prompt, and you'll be returned to the Linux command line.

Enable user myuid to do the same thing

Log into Linux as myuid, and type the following command: 
[myuid@linuxhost myuid]$ psql mydb
You'll get the following error message: 
Connection to database 'mydb' failed.
FATAL 1:  SetUserId: user "myuid" is not in "pg_shadow"
[myuid@linuxhost myuid]$
This is because user myuid hasn't been added to the database. So log in as postgres, and use createuser as shown in this session: 
[postgres@linuxhost pgsql]$ createuser
Enter name of user to add ---> myuid
Enter user's postgres ID or RETURN to use unix user ID: 501 ->
Is user "myuid" allowed to create databases (y/n) y
Is user "myuid" allowed to add users? (y/n) y
createuser: myuid was successfully added
[postgres@linuxhost pgsql]$
Note that you just hit Enter on the 501. createuser deduced 501 from the Linux myuid uid. Note also that I answered yes to both questions. For the purposed of this exercise, we're sacrificing caution in favor of ease.

Now log in as myuid and try again: 

[myuid@linuxhost myuid]$ psql mydb
You should now get the psql prompt: 
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: mydb

mydb=>
If not, troubleshoot. Do an insert to table mytable and a select to verify. You should be able to do it. Also try a create table command, and then remove that table with a drop table (careful!). You should be able to do it. Troubleshoot as necessary.

Congratulations

You now have PostgreSQL running, and can prove it with the command line interactive SQL program, psql. Read on...

Get DBI::DBD Working

This is it, ladies and gentlemen. This is what gives Linux a bad name. You must go out on the 'net, grab sourcecode for DBI and the PostgreSQL DBD driver, compile, test, and install them. It's something a user or administrator shouldn't need to deal with.

Or is it? It's like democracy -- you pay for your freedom. Your home is your castle, but if your next door neighbor wants to paint his house chartreuse and purple, and play polka music all day -- his house is his castle likewise. In the same spirit, that need to compile source code that makes your life difficult also guarantees that nobody can ever hijack the program and, well, for instance, saddle it with operating system-specific calls (now who would do that?). Because if they did, you or one of your brilliant programmer friends could go in the source code and remove the offensive code. And the free-software license guarantees that you will get the source code.

I can't help you

Please don't email me with questions about installing DBI::DBD. I don't have time. It's a tough enough task to do realtime, without trying to guess thru email. Please follow all instructions carefully, keeping in mind that some directions are ambiguous. Please don't expect this to be quick or easy.

Here are the prerequisites:

Note that if you followed the instructions in the previous articles, all the above prerequisites are true.

Now set up three source code directories

Set up the following directories, and load them with the mentioned files from the mentioned URL's. Please note that the files listed below are for the Red Hat 5.1 distribution with Perl version  5.004_04 and PostgreSQL version 6.3.2. If you have older versions of Perl or PostgreSQL, read the various version readme's to see the most current one you can use. For each .tar.gz file, from its directory as user myuid, first decompress it with this command: 
gunzip whatever.tar.gz
That should create a new, decompressed file, probably ending in _tar. Then pull its files out of archive with this command: 
tar --extract --file=whatever_tar --verbose
This should put several files into a directory below the current one. Installation is done from those lower directories.

Make and Install

For each of the three directories created in the previous section, you must make and install the software. Each directory has a readme file **READ IT CAREFULLY AND FOLLOW ITS DIRECTIONS**. In general, you'll follow this sequence of commands typed at the command line as user myuid (who must be FULLY enabled as a PostgreSQL user):
  1.     perl Makefile.PL
  2.    make
  3.    make test
  4.    make install (if the tests look okay)

Notes:

If the make test command fails, or if any part of the test fails, you'll want to run make test again, but this time with the TEST_VERBOSE=1 argument. Here's what the command looks like: 
make test TEST_VERBOSE=1
The first three commands must NOT be run as uid root. Instead, they must be a normal user with full rights in PostgreSQL. That's why for this tutorial we're using myuid. As far as I can tell, the first three commands make no changes outside the directory you're in. However, the fourth command, make install, installs the software into the system. make install must be done as user root, once again from the current directory.

You must make and install from each of the three directories listed in the previous section, and do it in THIS SPECIFIC ORDER:

  1. dbisrc
  2. dbdsrc
  3. dbifaq

Write Some Simple Data-Aware Perl Programs

General note:

Don't forget to set each of these programs executable by all, and invoke them with a prepended ./
Be sure you run them as user myuid, and that PostgreSQL allows user myuid to do everything.

hellodriver.pl

#!/usr/bin/perl -w
# hellodriver.pl
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may use this code freely as you wish.
# There is absolutely NO WARRANTEE
# This program tests to see whether the DBI::DBD::Pg 
# driver can be installed. This is the most basic test
# of your DBI::DBD interface to PostgreSQL, a sort of
# ping, if you will. If you can't get this to work, 
# check your DBI::DBD installation, your PostgreSQL,
# and of course make sure you have Perl running.
# Also make sure versions of DBI, DBD, PostgreSQL,
# and Perl are compatible.

# NOTE! DBI->install_driver is unsupported, and not
# recommended for any use other than this simple
# diagnostic! Please note also that because this
# call is unsupported, it may disappear without
# notice, or change in name or functionality!
# Please do not ask anyone for tech
# support for code that includes DBI->install_driver!
use DBI;
$driverhandle = DBI->install_driver( 'Pg' );
if ( !defined $driverhandle )
   {

   die "install_driver failed: $!\n";
   }
else
   {
   print "SUCCESS!\n";
   }    

hellodbhandle.pl

#!/usr/bin/perl -w
# hellodbhandle.pl
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may use this code freely as you wish.
# There is absolutely NO WARRANTEE
# This program tests to see whether your DBI::DBD
# setup can open a database handle. Do not attempt
# this until you've gotten hellodriver.pl running.
# Unlike hellodriver.pl, hellodbhandle.pl actually
# connects to a database, meaning that database
# must be created, and the user through which you
# access that database must have rights to that
# database.
# Note the syntax of DBI->connect. The first arg
# must be a DBMS specific connect string, which
# in PostgreSQL is simply "dbname=xxx", where xxx
# stands for the database name. The second
# argument is the user through whom you wish to
# connect.
use DBI;
$dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
if ( !defined $dbhandle )
   {
   die "DBI->connect failed: $DBI::errstr\n";
   }
else
   {
   print "SUCCESS!\nNow closing the connection...\n";
   $dbhandle->disconnect;
   }

hellocursor.pl

#!/usr/bin/perl -w
# hellocursor.pl
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may use this code freely as you wish.
# There is absolutely NO WARRANTEE
# This program retrieves rows from the mytable
# table of the mydb database one at a time via
# a cursor. Do not attempt this until you've 
# gotten hellodriver.pl and hellodbhandle running.
# If both those run and hellocursor.pl doesn't,
# it's most likely a syntax error in hellocursor.pl.
use DBI;
$dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
if ( !defined $dbhandle )
   {
   die "DBI->connect failed: $DBI::errstr\n";
   }
else
   {
   print "SUCCESS in connecting!\n";

   $cursor = $dbhandle->prepare( "SELECT * FROM mytable order by lastname;" );
   $cursor->execute;
   while ( @row = $cursor->fetchrow )
     {
     print "Row: @row\n";
     }

   # Clean up
   print "Now closing database connection...\n";
   $cursor->finish;
   $dbhandle->disconnect;
   }                  

helloinsert.pl

#!/usr/bin/perl -w
# helloinsert.pl
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may use this code freely as you wish.
# There is absolutely NO WARRANTEE
# This program inserts a row into the mytable
# table of the mydb database via
# an insert statement. Do not attempt this until you've 
# gotten the other hellos running.
# If those run and this doesn't,
# it's most likely a syntax error in this program.

# You can view the results of this call to ./helloinsert.pl
# by running ./hellocursor.pl.
use DBI;
if ( $#ARGV != 2 )

  {
  print "Usage: ./helloinsert.pl <lastname> <firstname> <email>\n";
  print "Be sure to use double quotes to delimit multi-word arguments.\n";
  die;
  }

$dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
if ( !defined $dbhandle )
   {
   die "DBI->connect failed: $DBI::errstr\n";
   }
else
   {
   print "SUCCESS in connecting!\n";

   #Note the manditory \' (literal) quotes in the values. This is an SQL must!
   $returnvalue = $dbhandle->do("
                      INSERT INTO mytable
                      (lastname, firstname, email)
                      VALUES(\'$ARGV[0]\', \'$ARGV[1]\', \'$ARGV[2]\');
                      " );
   if ( !defined $returnvalue )
     {
     die "\$dbhandle->do() failed: $DBI::errstr\n";
     }

   # Clean up
   print "Now closing database connection...\n";
   $dbhandle->disconnect;
   print "View the results of this query with a ./hellocursor.pl call.\n";
   }                  

What you've accomplished

Congratulations! You've interfaced the Perl interpreter that came with Red Hat with the PostgreSQL DBMS that came with Red Hat, using DBI::DBD which you got off the 'net and installed on your Linux machine.

Where you'll go from here

You're most of the way to creating a simple data-enabled web app. The issues you'll face in using the principles you've learned so far in a web app are user issues with the web server, and CGI issues. Also, using the die command in a CGI script is a real bad idea, because it simply gives a "server error" type message.

You're most of the way there. Read on...

Make Your Web App

Oversimplification

The web app we're presenting is an academic oversimplification whose only purpose is learning data interfaces to a Linux/Apache hosted web site. By making the CGI and the database structure and normalization trivial beyond the point of uselfullness, we emphasize the interface, which is what we wanted to accomplish. Have no doubt that anyone knowing database normalization, program design and Perl/CGI can use these oversimplifications to make an industrial strength web app.

Specifically, each field MUST have no spaces. The last name field must have no punctuation of any kind. The user must make sure the last name field is unique between rows -- for simplicity that validation is left out of the app.

helloretrieve.cgi

This is the most elementary "database enabled web app" you can make. Entirely read-only. All it does is display the contents of the mytable table in the mydb database. Create the following helloretrieve.cgi, which will print out the contents of the mytable table in the mydb database. 
#!/usr/bin/perl -w
# helloretrieve.cgi
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may NOT use this code in a training or educational course
# without licensing from Steve Litt.
# However, you may use this code freely in computer programs not
# used as educational material.
# In any case, this copyright notice must remain intact.
# There is absolutely NO WARRANTEE

use DBI;

sub printheader
  {
  print "Content-type: text/html\n\n";
  print "<html><head>";
  print "<meta http-equiv=\"Content-Type\" content=\"text/html>";
  print "<title>helloretrieve.cgi</title></head>\n";
  print "<body bgcolor=\"#FFFFFF\">\n";
  print "<P><h1 align=\"center\">helloretrieve.cgi</h1><P>";
  }

sub printfooter
  {
  print "</body></html>";
  }


# ***** MAIN ROUTINE *****
&printheader;
$dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
if ( !defined $dbhandle )
  {
  print "DBI->connect failed: $DBI::errstr\n";
  }
else
  {
  print "SUCCESS in connecting!<P>\n";

  print "<pre>";
  $cursor = $dbhandle->prepare( "SELECT * FROM mytable order by lastname;" );
  $cursor->execute;
  while ( @row = $cursor->fetchrow )
    {
    print join("  ", @row);
    print "\n";
    }

  print "</pre>\n\n";
  # Clean up
  print "Now closing database connection...\n";
  $cursor->finish;
  $dbhandle->disconnect;
  }
&printfooter;

Troubleshooting:

First, make sure the mytable table in the mydb database actually contains data. Use psql to determine that. Then it's pretty much the same as any CGI.
  1. As user myuid, perl -w helloretrieve.cgi. It should spit out a bunch of html, including data from the database.
  2. As user myuid, ./helloretrieve.cgi.  It should spit out a bunch of html, including data from the database.
  3. As user myuid, ./helloretrieve.cgi > junk.html, then pull up junk.html in a browser. Junk.html should display the contents of the database.
  4. Finally, pull up helloretrieve.cgi in a browser. It should display the contents of the database.
At the point where it stops working, exploit the differences.

helloselect.cgi

This is the same as helloretrieve.cgi, except it puts the data in a drop-down picklist so you can select the record in preparation to edit it with helloupdate.cgi and hellodbwrite.cgi (both to be written later). 
#!/usr/bin/perl -w
# helloselect.cgi
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may NOT use this code in a training or educational course
# without licensing from Steve Litt.
# However, you may use this code freely in computer programs not
# used as educational material.
# In any case, this copyright notice must remain intact.
# There is absolutely NO WARRANTEE

use DBI;
sub printheader
   {

   print "Content-type: text/html\n\n";
   print "<html><head>";
   print "<meta http-equiv=\"Content-Type\" content=\"text/html>";
   print "<title>helloselect.cgi</title></head>\n";
   print "<body bgcolor=\"#FFFFFF\">\n";
   print "<P><h1 align=\"center\">helloselect.cgi</h1><P>";
   }

sub printfooter
   {
   print "</body></html>";
   }



sub printbody
   {
   $dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
   if ( !defined $dbhandle )
     {
     print "DBI->connect failed: $DBI::errstr\n";
     }
   else
     {
     print "SUCCESS in connecting!<P>\n";

     print "<form  action=\"helloupdate.cgi\" method=\"POST\">\n";
     print "<p><select name=\"DROWS\" size=\"1\">\n";
     print "<pre>";


     $cursor = $dbhandle->prepare( "SELECT * FROM mytable order by lastname;" );
     $cursor->execute;
     while ( @row = $cursor->fetchrow )
       {
       print "  <option>";
       print join(" -- ", @row);
       print "</option>\n";
       }

     print "</pre>";
     print "</select></p>\n";
     print "&nbsp;&nbsp;\n";
     print "<input type=\"submit\" name=\"BEdit\" value=\"Edit\">\n";
     print "&nbsp;&nbsp;\n";
     print "<input type=\"submit\" name=\"BAdd\" value=\"Add\">\n";
     print "&nbsp;&nbsp;\n";
     print "<input type=\"submit\" name=\"BDelete\" value=\"Delete\">\n";
     print "</form>\n";

     # Clean up
     print "Now closing database connection...\n";
     $cursor->finish;
     $dbhandle->disconnect;
     }
  }

# ***** MAIN ROUTINE *****
&printheader;
&printbody;
&printfooter;

helloupdate.cgi

Helloupdate.cgi is a kind of traffic cop for the data. Rather than helping the user choose data or writing data, it just gets the proper information and calls the proper routines. Helloupdate.cgi is called from helloselect.cgi, with a record value from the drop-down picklist and one of the three submit buttons: Edit, Add or Delete. If it's delete, helloupdate.cgi displays a confirmation form showing the record the user picked from helloselect.cgi's picklist, and if the user clicks yes, calls hellodbwrite.cgi with an argument of DELETE to delete the record.

If the user clicked Edit on helloselect.cgi, helloupdate.cgi displays a form with data from the helloselect.cgi picklist entry. The form shows a read-only last name (last name is the unique identifier in this simplistic case), and loads the firstname and email fields with the present contents of the record so the user can modify them. Once the user modifies them and clicks submit, helloupdate.cgi calls hellodbwrite.cgi with an argument of EDIT to update the record in the database.

If the user clicked Add on helloselect.cgi, the contents of the picklist are ignored. Helloupdate.cgi presents a blank form with all three fields to be filled in. When the user clicks submit, hellodbwrite.cgi is called with an ADD argument to insert the new record.
  
#!/usr/local/bin/perl
# helloupdate.cgi
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may NOT use this code in a training or educational course
# without licensing from Steve Litt.
# However, you may use this code freely in computer programs not
# used as educational material.
# In any case, this copyright notice must remain intact.
# There is absolutely NO WARRANTEE

use DBI;

sub FixFormString
 {
 my $sz = shift(@_);
 my $leftangle = shift(@_); unless(defined($leftangle)) {$leftangle = q(&lt;)};
 my $odoa = shift(@_); unless (defined($odoa)) {$odoa = '<br>'};
 my $odoaodoa = shift(@_); unless (defined($odoaodoa)) {$odoaodoa = '<p>'};
 $sz =~ (s/%3C/$leftangle/ge); #special handling for left angle bracket
 $sz =~ (s/%26/&amp/g);     #special handling for & sign
 $sz =~ (s/\+/ /g);         #plus signs sent from form as pluses
 $sz =~ (s/%0D%0A%0D%0A/$odoaodoa/g);
 $sz =~ (s/%0D%0A/$odoa/g);
 $sz =~ (s/%(..)/pack("c",hex($1))/ge);
 return($sz);
 }



sub GetFieldAssoc
 {
 my($ref) = shift(@_);
 my(%fieldsx) = %$ref;
 my($ScratchString);
 if ($ENV{"REQUEST_METHOD"} eq 'GET')
    {$ScratchString = $ENV{"QUERY_STRING"}}
 else
    { read(STDIN, $ScratchString, $ENV{"CONTENT_LENGTH"}) }

 my(@record) = split(/&/, $ScratchString);
 my($recordparts);
 foreach $recordparts (@record)
   {
   (my($thekey), my($thevalue)) = split(/=/, $recordparts);
   $fieldsx{$thekey} = $thevalue;
   }
 return(%fieldsx);
 } # end FillFields


sub FixFieldAssoc
 {
 my($ref) = shift(@_);  # it's a reference right now 
 my(%fieldassoc) = %$ref;       # dereference it
 my($name);
 my($value);
 my(@allnames) = keys(%fieldassoc);
 foreach $name (@allnames)
  {
  $name = FixFormString($name);
  $value = FixFormString($fieldassoc{$name}, "leftangle", " ","\<P>" );
  $fieldassoc{$name} = $value;
  }
 return(%fieldassoc);
 }

sub getLastname
 {
 my($returnstring);
 while(<STDIN>)
   {
   chomp($_);
   if($_ =~ m/DROWS\=(.*?)\+\-\-\+/)
    {
    $returnstring = $1;
    last;
    }
   }
 return($returnstring);
 }


sub getRecord
 {
 my($lastname) = @_;

 my($dbhandle) = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
 if ( !defined $dbhandle )
   {
   print "DBI->connect() failed: $DBI::errstr\n";
   }
 else
   {
   print "SUCCESS in connecting!<P>\n";

   $cursor = $dbhandle->prepare( "SELECT * FROM mytable where lastname=\'$lastname\';" );
   $cursor->execute;
   ($lastname,$firstname,$email) = $cursor->fetchrow(); 

   # Clean up
   $cursor->finish;
   $dbhandle->disconnect;
   return("lastname"=>$lastname,
           "firstname"=>$firstname,
           "email"=>$email
         );
   }
 return(0);
 }


sub EditMain
  {
  my(%locfields) = %{$_[0]};
  print "<HTML><HEAD><TITLE>Editing Record...</TITLE></HEAD><BODY>\n";
  print "<C><H1 align=\"center\">Record EDIT Form, helloupdate.cgi</H1></C>\n";

  my($lname) =  $locfields{'DROWS'};
  $lname     =~ m/(.*?) \-\-\ /;
  $lname     =  $1;
  my(%dbfields) = &getRecord($lname);

  print "<form action=\"hellodbwrite.cgi?EDIT\" method=\"POST\">\n";
  print "<input type=\"hidden\" name=\"Hlastname\" value=\"$lname\">\n";
  print "    <p>Last Name: \n";
  print "    <B><FONT SIZE=+1>$dbfields{'lastname'}</FONT></B></p>\n";
  print "    <p>First Name: <input type=\"text\" size=\"16\" name=\"Tfirstname\"\n"; 
  print "    value=\"$dbfields{'firstname'}\"></p>\n";
  print "    <p>Email: <input type=\"text\" size=\"40\" name=\"TEmail\"\n";
  print "    value=\"$dbfields{'email'}\"></p>\n";
  print "    <p><input type=\"submit\" name=\"BSubmit\" value=\"Submit\"></p>\n";
  print "</form>\n";
 
  print "</BODY></HTML>\n";
  }



sub AddMain
  {
  my(%locfields) = %{$_[0]};
  print "<HTML><HEAD><TITLE>Deleting Record...</TITLE></HEAD><BODY>\n";
  print "<C><H1 align=\"center\">Record ADD Form, helloupdate.cgi</H1></C>\n";


  print "<form action=\"hellodbwrite.cgi?ADD\" method=\"POST\">\n";
  print "    <p>Last Name: \n";
  print "    <input type=\"text\" size=\"16\" name=\"Tlastname\"></p>\n";
  print "    <p>First Name: <input type=\"text\" size=\"16\" name=\"Tfirstname\"></p>\n";
  print "    <p>Email: <input type=\"text\" size=\"40\" name=\"TEmail\"></p>\n";
  print "    <p><input type=\"submit\" name=\"BSubmit\" value=\"Submit\"></p>\n";
  print "</form>\n";
 
  print "</BODY></HTML>\n";
  }


sub DeleteMain
  {
  my(%locfields) = %{$_[0]};
  print "<HTML><HEAD><TITLE>Deleting Record...</TITLE></HEAD><BODY>\n";
  print "<C><H1 align=\"center\">Record DELETE Confirmation Form, helloupdate.cgi</H1></C>\n";

  my($lname) =  $locfields{'DROWS'};
  $lname     =~ m/(.*?) \-\-\ /;
  $lname     =  $1;
  my(%dbfields) = &getRecord($lname);

  print "<CENTER><TABLE BORDER COLS=1 WIDTH=\"100%\" BGCOLOR=\"#FF0000\" >\n";
  print "<TR><TD><H1>\n";
  print "Last Name : $dbfields{'lastname'}<br>\n";
  print "First Name: $dbfields{'firstname'}<br>\n";
  print "Email     : $dbfields{'email'}<br>\n";
  print "<H1></TD></TR></TABLE></CENTER>\n";
  print "<form action=\"hellodbwrite.cgi?DELETE\" method=\"POST\">\n";
  print "<input type=\"hidden\" name=\"Hlastname\" value=\"$lname\">\n";
  print "<H2>Delete? \n";
  print "<input type=\"submit\" name=\"BConfirm\" value=\"YES\">\n";
  print "<input type=\"submit\" name=\"BBail\" value=\"NO\">\n";
  print "</H2>\n";
  print "</form>\n";

 
  print "</BODY></HTML>\n";
  }


sub fcnError
  {
  my(%locfields) = %{$_[0]};
  print "<HTML><HEAD><TITLE>Internal Error...</TITLE></HEAD><BODY>\n";
  print "<C><H1 align=\"center\">ERROR: Request Neither Add, Edit nor Delete, helloupdate.cgi</H1></C>\n";

  print "<P>\n";
  print %locfields;
  print "<P>\n";
 
  print "</BODY></HTML>\n";
  }



# ***** MAIN ROUTINE *****
%fields = GetFieldAssoc(\%fields);
%fields = FixFieldAssoc(\%fields);

print "Content-type: text/html\n\n<P>\n";

if(defined($fields{'BEdit'}))
  {&EditMain(\%fields);}
elsif(defined($fields{'BAdd'}))
  {&AddMain(\%fields);}
elsif(defined($fields{'BDelete'}))
  {&DeleteMain(\%fields);}
else
  {&fcnError(\%fields);}

hellodbwrite.cgi

hellodbwrite.cgi does the actual database adds, changes and deletes. It decides on add/change/delete based on the single argument passed it, and it gets the data to add or change, or the last name acting as a key for the delete, from stdin. When it finishes it provides a link back to helloselect.cgi so the user can continue working with the data.
  
#!/usr/local/bin/perl -w
# hellodbwrite.cgi
# Copyright (C) 1998 by Steve Litt, Steve Litt's email address
# You may NOT use this code in a training or educational course
# without licensing from Steve Litt.
# However, you may use this code freely in computer programs not
# used as educational material.
# In any case, this copyright notice must remain intact.
# There is absolutely NO WARRANTEE

use DBI;

sub FixFormString
 {
 my $sz = shift(@_);
 my $leftangle = shift(@_); unless(defined($leftangle)) {$leftangle = q(&lt;)};
 my $odoa = shift(@_); unless (defined($odoa)) {$odoa = '<br>'};
 my $odoaodoa = shift(@_); unless (defined($odoaodoa)) {$odoaodoa = '<p>'};
 $sz =~ (s/%3C/$leftangle/ge); #special handling for left angle bracket
 $sz =~ (s/%26/&amp/g);     #special handling for & sign
 $sz =~ (s/\+/ /g);         #plus signs sent from form as pluses
 $sz =~ (s/%0D%0A%0D%0A/$odoaodoa/g);
 $sz =~ (s/%0D%0A/$odoa/g);
 $sz =~ (s/%(..)/pack("c",hex($1))/ge);
 return($sz);
 }



sub GetFieldAssoc
 {
 my($ref) = shift(@_);
 my(%fieldsx) = %$ref;
 my($ScratchString);
 if ($ENV{"REQUEST_METHOD"} eq 'GET')
    {$ScratchString = $ENV{"QUERY_STRING"}}
 else
    { read(STDIN, $ScratchString, $ENV{"CONTENT_LENGTH"}) }

 my(@record) = split(/&/, $ScratchString);
 my($recordparts);
 foreach $recordparts (@record)
   {
   (my($thekey), my($thevalue)) = split(/=/, $recordparts);
   $fieldsx{$thekey} = $thevalue;
   }
 return(%fieldsx);
 } # end FillFields


sub FixFieldAssoc
 {
 my($ref) = shift(@_);  # it's a reference right now 
 my(%fieldassoc) = %$ref;       # dereference it
 my($name);
 my($value);
 my(@allnames) = keys(%fieldassoc);
 foreach $name (@allnames)
  {
  $name = FixFormString($name);
  $value = FixFormString($fieldassoc{$name}, "leftangle", " ","\<P>" );
  $fieldassoc{$name} = $value;
  }
 return(%fieldassoc);
 }


sub doDelete
  {
  my(%locfields) = %{$_[0]};
  if(defined($locfields{'BConfirm'}) && !defined($locfields{'BBail'}))
    {
    $dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
    if ( !defined $dbhandle )
       {
       print "DBI->connect() failed: $DBI::errstr\n";
       }
    else
       {
       print "SUCCESS in connecting!\n";

       #Note the manditory \' (literal) quotes in the values. This is an SQL must!
       my($statement) = "DELETE FROM mytable where lastname=\'$locfields{'Hlastname'}\';";
       print "<P><B>$statement</B><P>\n";
       $returnvalue = $dbhandle->do($statement);

       if ( !defined $returnvalue )
         {
         print "\$dbhandle->do() failed: $DBI::errstr<P>\n";
         }
       else
         {
         print "Delete succeeded<P>\n";
         }

       # Clean up
       print "Now closing database connection...\n";
       $dbhandle->disconnect;
       }
    }
  }
  
sub doAdd
 {
 my(%locfields) = %{$_[0]};
 $dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
 if ( !defined $dbhandle )
    {
    print "DBI->connect() failed: $DBI::errstr\n";
    }
 else
    {
    print "SUCCESS in connecting!\n";

    #Note the manditory \' (literal) quotes in the values. This is an SQL must!
    my($statement) = "INSERT INTO mytable VALUES(
                           \'$locfields{'Tlastname'}\',
                           \'$locfields{'Tfirstname'}\',
                           \'$locfields{'TEmail'}\');
                         ";
    print "<P><B>$statement</B><P>\n";                         
    $returnvalue = $dbhandle->do($statement);

    if ( !defined $returnvalue )
      {
      print "\$dbhandle->do() failed: $DBI::errstr<P>\n";
      }
    else
      {
      print "Add succeeded<P>\n";
      }

    # Clean up
    print "Now closing database connection...\n";
    $dbhandle->disconnect;
    }
 }
  

  
sub doEdit
 {
 my(%locfields) = %{$_[0]};
 $dbhandle = DBI->connect( 'dbname=mydb', 'myuid', '', 'Pg' );
 if ( !defined $dbhandle )
    {
    print "DBI->connect() failed: $DBI::errstr\n";
    }
 else
    {
    print "SUCCESS in connecting!\n";

    #Note the manditory \' (literal) quotes in the values. This is an SQL must!
    my($statement) = "UPDATE mytable
                      SET firstname=\'$locfields{'Tfirstname'}\',
                      email=\'$locfields{'TEmail'}\'
                      where lastname=\'$locfields{'Hlastname'}\';";
    print "<P><B>$statement</B><P>\n";                      
    $returnvalue = $dbhandle->do($statement);

    if ( !defined $returnvalue )
      {
      print "\$dbhandle->do() failed: $DBI::errstr<P>\n";
      }
    else
      {
      print "Update succeeded<P>\n";
      }

    # Clean up
    print "Now closing database connection...\n";
    $dbhandle->disconnect;
    }
 }
  




# ***** MAIN R0UTINE *****
print "Content-type: text/html\n\n";
print "<HTML><HEAD></HEAD><BODY>\n";

%fields = GetFieldAssoc(\%fields);
%fields = FixFieldAssoc(\%fields);
my($fcn) = $ARGV[0];
if($fcn eq "EDIT")
  {  &doEdit(\%fields);  }
elsif($fcn eq "ADD")
  {  &doAdd(\%fields);  }
elsif($fcn eq "DELETE")
  {  &doDelete(\%fields);  }
else
  {
  print "<P><CENTER><H1>INVALID FUNCTION: Neither EDIT, ADD nor DELETE</H1></CENTER><P>\n";
  }

 print "<P><A HREF=\"./helloselect.cgi\">Enter more data</A><P>\n";
 print "</BODY></HTML>\n";

What you've done

If you completed all the database exercises, you set up a database in PostgreSQL, got into psql, added data, enabled another user (myuid) to access it, installed the DBI::DBD interface between Perl and PostgreSQL, used it to write some data-aware Perl scripts, then wrote a simple web app.

Yes, a web app. helloselect.cgi, helloupdate.cgi plus hellodbwrite.cgi comprise a web app to maintain data in the mytable table you built earlier. The data is on the Linux box, and the user accesses it exclusively through the browser. You've just created that "new technology" everyone's talking about.

Steve Litt is the author of Rapid Learning: Secret Weapon of the Successful Technologist and of the Universal Troubleshooting Process courseware.  He can be reached at Steve Litt's email address.

[ Back to this month's Troubleshooting Professional Magazine ]

[ Back to Troubleshooters.Com ]