Web Based Form with DBI

[Condle, Joseph P]

I am trying to input data from an html form.  I can insert data from a straight dbi 
script without a problem. I can get data from the form without having the dbi piece.  
But I can not merge the two and get it to work.  When I run the following script I get 
the following errors but the record get inserted into my database.  The printf's are 
to see whethere I have data or not.  According to the files written I do not have 
data, but I am still inserting a recored.  
I am running Perl 5.6.1, solaris 8, Oracle 8.17, DBI1.21 and DBD 1.12.  This is my 
first DBI web form script.


The Errors
>From the html form netscape Error  The document contained no data.  Try again later, 
>or contact the server's administrator

The error_log of the apache web server
Printf() on closed filehandle JOE at insert.pl line 59.
Printf() on closed filehandle JOE at insert.pl line 61.
Printf() on closed filehandle JOE at insert.pl line 62.
Printf() on closed filehandle JOE at insert.pl line 63.
Printf() on closed filehandle JOE at insert.pl line 64.
Vilolated (DBD ERROR: OCIStmtExecute at insert.pl line 77
Issuing rollback() for database handle being destroyed without explicit disconnect().

Thanx in advance  


#!/usr/bin/perl -w
#
# use strict;
$ENV{'PATH'} = 
'/u01/app/oracle/product/817/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/local/bi
n:/usr/ucb:/usr/openwin/bin';
$ENV{'CLASSPATH'}= 
'/u01/app/oracle/product/817/JRE/lib:/u01/app/oracle/product/817/jlib
:/u01/app/oracle/product/817/jlib';
$ENV{'LD_LIBRARY_PATH'} = '/u01/app/oracle/product/817/lib';
$ENV{'ORACLE_BASE'} = '/u01/app/oracle';
$ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/817';
$ENV{'ORACLE_SID'} = 'chp';

use DBI qw(:sql_types);

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

read (STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

@pairs = split(/&/, $buffer);

foreach $pair (@pairs)
{
  ($name, $value) = split(/=/, $pair);
  $value =~ tr/+/ /;
  $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("c", hex($1))/eg;
  $FORM{$name} = $value;
}

open(JOE, "> preOra.txt");
printf JOE "Begining \n";
printf JOE "ssn %s is %s \n",$FORM{'ssn'};
printf JOE "fname %s is %s \n",$FORM{'fname'};
printf JOE "lname %s is %s \n",$FORM{'lname'};
printf JOE "city %s is %s \n",$FORM{'city'};
printf JOE "state %s is %s \n",$FORM{'state'};
printf JOE "zip %s is %s \n",$FORM{'zip'};
close(JOE);

my $dbh = DBI->connect ( 'dbi:Oracle:xxxxx',
                        'xxxxxx',
                        'xxxxxx',
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                       ) || "Database connection not made :$DBI::errstr";

my $ssn    = $FORM{'ssn'};
my $fname  = $FORM{'fname'};
my $lname  = $FORM{'lname'};
my $city   = $FORM{'city'};
my $state  = $FORM{'state'};
my $zip    = $FORM{'zip'};

open(DAN, "> postOra.txt");
printf JOE "ssn %s is %s \n",$ssn; 
printf JOE "fname %s is %s \n",$fname; 
printf JOE "lname %s is %s \n",$lname; 
printf JOE "city %s is %s \n",$city; 
printf JOE "state %s is %s \n",$state; 
printf JOE "zip %s is %s \n",$zip; 
close(DAN);

my $sql = qq{ INSERT INTO employee VALUES (?, ?, ?, ?, ?, ?)};
my $sth = $dbh->prepare( $sql );

$sth->bind_param(1,$ssn,SQL_VARCHAR);
$sth->bind_param(2,$fname,SQL_VARCHAR);
$sth->bind_param(3,$lname,SQL_VARCHAR);
$sth->bind_param(4,$city,SQL_VARCHAR);
$sth->bind_param(5,$state,SQL_VARCHAR);
$sth->bind_param(6,$zip,SQL_VARCHAR);

$sth->execute();
$dbh->commit;

if($@)
{
  warn "Database error: $DBI::errstr\n";
  $dbh->rollback(); #just die if rollback is failing
}
#my( $ssn, $fname, $lname, $city);
#$sth->bind_columns (undef, \$ssn, \$fname, \$lname, \$city);

#while ( $sth->fetch() )
#{
#  print "$ssn, $fname, $lname, $city\n";
#}

$sth->finish();
$dbh->disconnect();