#!/usr/local/bin/perl5 -w use CGI qw(:standard); # use CGI module for CGI programming use DBI; # use DBI modulde for database connectivity use DBI::DBD; # in order to use this CGI, your database should have the following table # use the following sql statement to create a table # create table product_1 ( # id char(6), # category varchar2(50), # description varchar2(50), # price numeric(10,2), # onhand int, # primary key(id) # ); # global variable # $statement # $dbh # $qfield # $qpattern # set environment variable for connecting to oracle database $ENV{'ORACLE_HOME'}='/opt5/oracle8i/app'; # connection to the database # replace cprjxxxx with your project acount and password as your account password $dbh=DBI->connect("dbi:Oracle:db00","cprjxxxx","password") or die "Can't connect to database"; # below is the main part of this CGI program # refer to lecture notes 3-18 to 3-22 # display upper part of HTML document (the form for you to query and add new product showmenu(); # below is a request dispatcher to see which button the user clicked and # call the corresponding function if (param('lookup') || (param('cancel') && !param('new')) ) { # lookup or cancel button is clicked do_query(); # query one or more records display_result(); # show the result of query } elsif (param('insert')) { # insert button is clicked my $key=param('ins_key'); # retrieve the id input by user in the form if (check_insert($key)) { # check whether it is duplicate key or not edit_item($key); # show a page for editing single record } else { # show error message print qq(

The key is duplicated or longer than 6 character

); } } elsif (param('editall')) { # editall button is clicked do_query(); # query the records that need to edit edit_all(); # show a page for editing all of the selected records } elsif (param('edit')) { # edit button is clicked do_query(); # query the specific items that need to edit edit_item(); # show a page for editing a single element } elsif (param('delete') || (param('new') && param('cancel'))) { # delete button is clicked do_delete(); # delete a record do_query(); # query and show the updated result display_result(); } elsif (param('update')) { # update/updateall button is clicked update_data(); # update record(s) do_query(); # query the updated result display_result(); # display the updated result } # disconnect the connection to the database $dbh->disconnect or warn "Error disconnecting\n"; # show the lower part of HTML document print ""; print end_html; # function to show the menu at the upper part sub showmenu { my @fields = qw(ID Description Category); # array storing different fields print header(); # print the content-type header # print the head of html page print qq( Product information

Product Table

); if (!param('field')) { # user visits the page first time print popup_menu("field", \@fields); print qq(matches ); } else { # user revisit the page $qfield = param('field'); $qpattern = param('query'); print popup_menu("field", \@fields, $qfield); print qq(matches ); } print qq(

); } # function to query database based on user's input sub do_query { my $field; my $pattern; if (param('edit')) { # a link is clickced $field = "ID"; $pattern = param('key'); } elsif ( param('lookup') || param('cancel') || param('update') || param('delete') || param('editall') ) { $field = $qfield; $pattern = $qpattern; } # prepare SQL statement, selecting records that match the search criteria $statement=$dbh->prepare("select * from product_1 where $field like '\%$pattern\%'") or die "Can't prepare SQL statment $DBI::errstr\n"; # execute SQL statement $statement->execute or die "Can't execute SQL statment $DBI::errstr\n"; } #function to display result in table based on data in $statement object sub display_result { # show the header line of the query result table print qq(
); # show the content of the table # the $statement variable contains records selected from the database, it is executed by the do_query function my @row; # for each record, we print it in the table while (@row = $statement->fetchrow_array()) { print qq(); } print "
ID Category Description Price Onhand
$row[0] $row[1] $row[2] $row[3] $row[4]
"; # display Edit All button print qq(

); } # function to allow edit of the row given a key # this function will show a table containing the record to be edit sub edit_item { my @editrow; my @category = qw(printer scanner modem); # display header line of query result table print qq(Edit Single Item ); # display the content of table if (param('insert')) { # user go the edit page becuase he click the insert button # $_[0] is the input parameter of this function, it contains the ID of the product that need to edit print qq(); print "
ID Category Description Price Onhand
$_[0]); print popup_menu("category1", \@category); print qq(
"; # hidden field storing the id of a record # this is used when user click update, and the program need to check which records to update print qq(); print qq(); } elsif ( param('edit') && (@editrow = $statement->fetchrow_array())) { # user go to edit page becuase he click the link in the query result table # show a table for editing a record, with default values shown # the @editrow variable is retrieved in the condition statement print qq( $editrow[0] ); # show the popup menu with default value # remove trailing space by regular expression $editrow[1] =~ s/\s+$//; # show a popup menu with default value as the third parameter # first parameter is the name of that popup menu input field # second parameter is the values inside this popup menu print popup_menu("category1", \@category, $editrow[1]); # display default values of remaining fields print qq( ); print ""; # hidden field needed, as user click update, the form can tell user are editing which record print qq(); } # display the "Update", "Cancel" and "Delete" buttons print qq(

); } # function to edit all of the selected rows # this function is called, when user click edit all button in the query result page # this function will display a table showing all the records that need to be edit in a table format sub edit_all { my @editall; my @category = qw(printer scanner modem); my $i = 1; # display header line of the table print qq(Edit All Selected Item(s) ); # $statement variable already contain all the records that need to be edited, this is done by do_query function # for each records, we display it in a table, that allow user to edit while (@editall = $statement->fetchrow_array()) { print qq(); print qq(); # hidden field $i++; } print "
ID Category Description Price Onhand
$editall[0] ); # remove the trailing spaces by regular expression $editall[1] =~ s/\s+$//; # show a popup menu with default valus indicated in the database record print popup_menu("category$i", \@category, $editall[1]); # display default values of remaining fields # the input field name ends with a counter $i, it allows the CGI program later to get all # records to update print qq(
"; # display the "Update All" and "Cancel" button print qq(

); } # function to update records # this function is called when user press the update/ update all button # this function will retrieve the values containd in a form and # execute update statment to update the record sub update_data { my $id; my $category; my $price; my $description; my $onhand; my $i; # this for loop will get all the records update for ($i=1; param("category$i"); $i++) { $id = param("id$i"); # retrieve the values of id1, id2, ... $category = param("category$i"); $description = param("description$i"); $price = param("price$i"); $onhand = param("onhand$i"); # update a record $statement = $dbh->prepare("update product_1 set category = '$category',". "description = '$description', price = $price, onhand = $onhand". "where id = '$id'" ) or die "Can't prepare SQL statement $DBI::errstr\n"; $statement->execute or die "Cannot update $id - $DBI::errstr\n"; } } # function to delete a record # this function will be called when user press the delete button # this function will perform delete in the database sub do_delete { my $id; $id = param('id1'); $statement = $dbh->prepare("delete product_1 where id = '$id'") or die "Can't prepare SQL statement $DBI::errstr\n"; $statement->execute or die "Can't execute SQL statement $DBI::errstr\n"; } # function to validate the user input id has duplicat key or not # we assume the product table has set primary key as ID sub check_insert { $statement=$dbh->prepare("insert into product_1(ID) values ('$_[0]')") or die "Can't prepare SQL statement $DBI::errstr\n"; if ($statement->execute()) { # return true if there is no duplicated record return 1; } else { # return false if there is duplicated record (execution will fail when DB check that a statement violate the constraint) return 0; } }