#!/opt/bin/perl
#Process.cgi: This is the program
# that insert, delete, and update
# the appointment table.
# It accepts the input from insert.html,
# delete.html, and update.html respectively
use strict;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use vars qw($cgi $dbh);
$ENV{"ORACLE_HOME"} = '/opt/oracle/9.0.1';
$cgi=new CGI;
my $sth;
#print out the header
print $cgi->header;
print "
\n";
print "";
print "Calendar of March 23, 2006
";
$dbh = DBI->connect("dbi:Oracle:TRCH", "usrname", "passwrd")
or die "Connecting: $DBI::errstr";
#'action' is the name of the forms in all the webpage
# passing input
#my $action = "";
#$action=$cgi->param('action');
#Call appropriate subroutine based on the action
#Subroutine is where you make changes to table
if ($cgi->param('action') eq "insert") {
&insert;
}
elsif ($cgi->param('action') eq "delete") {
&delete;
}
elsif ($cgi->param('action') eq "update") {
&update;
}
elsif ($cgi->param('action') eq "view") {
&view;
}
print "";
print "\n";
$dbh->disconnect( );
#Insert subroutine
#Given a time and a chore,
#this subroutine insert
#an entry into appointment table
sub insert {
#Following two line gets the input
my $appTime=$cgi->param('time');
my $chore=$cgi->param('chore');
#Following lines insert new entry
$sth=$dbh->prepare("insert into APPOINTMENT values ('$appTime',
'$chore')") or die "Cannot prepare insert: " . $dbh->errstr( );
$sth->execute( ) or die "Cannot execute insert: " . $sth->errstr( );
$sth->finish( );
#display update
&view;
}
#Delete subroutine
#Given a time,
#this subroutine deletes
#an entry with given time
sub delete {
#This line accepts the time of the entry to delete
my $appTime=$cgi->param('time');
#This line deletes the entry
$sth=$dbh->do("delete from APPOINTMENT where appTime = '$appTime'");
&view;
}
#Update subroutine
#Given a time and the updated chore,
#this subroutine update
#an entry with given time
sub update {
#Following two lines gets the time of the entry
#and the updated chore
my $appTime=$cgi->param('time');
my $chore=$cgi->param('chore');
#following line is the SQL command to update the chore
$sth=$dbh->do("update APPOINTMENT set CHORE = '$chore' where APPTIME = '$appTime'");
&view;
}
#View subroutine
#This subroutines display all the entries
sub view {
#Get all entries
my $sth=$dbh->prepare ("SELECT * FROM appointment");
if (!defined $sth){
print "Unalbe to load data\n";
die;
}
$sth->execute;
#Print the entries
#The fetchrow_array() method gets each
#record and store it in the @row array. foreach loop iterates through
#@row array and store each data in $_
print '';
print 'Time | Chore | ';
my @row;
while (@row=$sth->fetchrow_array()){
print "
";
foreach (@row) {
print "$_ | ";
}
print "
\n";
}
print "
";
}