and send <BODY> # print start_html("Nile dot Com Title Insertion"); # # Verify the validity of the values from the form # $name =~ /\w/ or later("Name must contain at least one character!"); $price > 0 or later("Price must be a number greater than zero!"); $edition =~ /\w/ or later("Edition must contain at least one character!"); # # Attempt to load the driver and connect to the database # eval { $dbh = DBI->connect("DBI:$DB_type:test_nile", $DB_login, $DB_password); }; # # Check if the eval failed (driver failed to load) # if ($@) { die "Could not install driver ($DBI::err): $DBI::errstr\n"; } # # Check if the connection attempt was successful # defined($dbh) or die "Could not connect to db ($DBI::err): $DBI::errstr\n"; # # Check if the book is already in the title table # if ($tid = is_duplicate( $dbh, $name, $price, $edition )) { later("Book already in DB (id $tid)"); } # # With Oracle, you must get a new unique title_id by SELECT'ing from a # sequence already set up for this class. If you need unique values to # insert into a table as a primary key, you'll need to set up this sequence # yourself. # if ($DB_type =~ /oracle/i) { # # Get the next value from the sequence # $tid = get_next_title_id( $dbh ); # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ INSERT INTO title (title_id, name, price, edition) VALUES ("$tid", "$name", "$price", "$edition") }) or die "Prepare of INSERT: ", $dbh->errstr, "\n"; } else { # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ INSERT INTO title (name, price, edition) VALUES ('$name', '$price', '$edition') }) or die "Prepare of INSERT: ", $dbh->errstr, "\n"; } # # Execute the prepared SQL statement # $sth->execute or die "Execute of INSERT: ", $dbh->errstr, "\n"; # # Now, if we're not using Oracle, we need to find out what the new unqiue # title id the database assigned to us. In MySQL, the DBD places that into # the statement handle object, which we read directly (which is technically # a violation of OO principles). In Access, we haven't found out how to do # this (yet). if ($DB_type =~ /mysql/i) { $tid = $sth->{'insertid'}; } # # We're done with this statement, free up the resources # $sth->finish; # # Disconnect from the database, we're done! # $dbh->disconnect or warn "Disconnect: ", $dbh->errstr, "\n"; # # Print out a nice big header, # print h1("Title Insertion"); # # Inform the user of successful insertion # print "Insertion of \"$name\" at a price of $price was successful.<br>"; print "New Title ID is $tid"; # # Send </BODY></HTML> # print end_html; exit 0; # # This subroutine is used to cleanly exit the program, # sending proper HTML back to the browser # sub later { my( $goof ) = @_; # # Print out a nice big header, # print h1("Insertion Failed"); print $goof; # # Send </BODY></HTML> # print end_html; exit 0; } sub get_next_title_id { my( $dbh ) = @_; my( $sth, $id ); $sth = $dbh->prepare(q{SELECT title_seq.next_val FROM dual}); $sth->execute || die "Select of sequence id: ", $dbh->errstr, "\n"; ($id) = $sth->fetchrow_array; $sth->finish; return $id; } # # This subroutine checks if a title is already in the title table. # It returns 0 if the title is not in the table, and the title id # if it does exist. # sub is_duplicate { my( $dbh, $name, $price, $edition ) = @_; my( $db_tid, $db_name, $db_price, $db_edition ); my( $sth ); # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ SELECT title_id, name, price, edition FROM title WHERE name = '$name' }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; while (($db_tid, $db_name, $db_price, $db_edition) = $sth->fetchrow_array) { if ($price == $db_price and $edition eq $db_edition) { # # We're done with this statement, free up the resources # $sth->finish; return $db_tid; # Cannot be 0! } } # # We're done with this statement, free up the resources # $sth->finish; return 0; }

#! /usr/local/bin/perl # Title Insertion Lab: Extra for Experts Solution # # Begin loading the needed extensions # use DBI; # The DBI extension use CGI qw (:html2 :form); # The CGI extension use CGI::Carp qw (carpout); # The extra carping routine # # The file dbtype.pl sets a variable which determines the type of database # you will use. This file will be found in either /tmp or /temp. # use lib qw(/tmp /temp); require 'dbtype.pl'; # # Set the path, unbuffer STDOUT, and redirect errors to the browser # $ENV{PATH} = join ":", qw(/usr/bin /bin /sbin /usr/sbin /etc); $| = 1; carpout(\*STDOUT); # # Declare variables # my( $dbh, $rv, $sth ); my( $tid, $name, $price, $edition ); # # Accept the CGI request # my( $q ) = new CGI; # # Read values from the form which called us # $name = $q->param("name") || ""; $price = $q->param("price") || 0; $edition = $q->param("edition") || ""; # # Send the HTTP header # print $q->header; # # Set the and send <BODY> # print start_html("Nile dot Com Title Insertion"); # # Verify the validity of the values from the form # $name =~ /\w/ or later("Name must contain at least one character!"); $price > 0 or later("Price must be a number greater than zero!"); $edition =~ /\w/ or later("Edition must contain at least one character!"); # # Attempt to load the driver and connect to the database # eval { $dbh = DBI->connect("DBI:$DB_type:test_nile", $DB_login, $DB_password); }; # # Check if the eval failed (driver failed to load) # if ($@) { die "Could not install driver ($DBI::err): $DBI::errstr\n"; } # # Check if the connection attempt was successful # defined($dbh) or die "Could not connect to db ($DBI::err): $DBI::errstr\n"; # # Check if the book is already in the title table # if ($tid = is_duplicate( $dbh, $name, $price, $edition )) { later("Book already in DB (id $tid)"); } # # With Oracle, you must get a new unique title_id by SELECT'ing from a # sequence already set up for this class. If you need unique values to # insert into a table as a primary key, you'll need to set up this sequence # yourself. # if ($DB_type =~ /oracle/i) { # # Get the next value from the sequence # $tid = get_next_title_id( $dbh ); # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ INSERT INTO title (title_id, name, price, edition) VALUES ("$tid", "$name", "$price", "$edition") }) or die "Prepare of INSERT: ", $dbh->errstr, "\n"; } else { # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ INSERT INTO title (name, price, edition) VALUES ('$name', '$price', '$edition') }) or die "Prepare of INSERT: ", $dbh->errstr, "\n"; } # # Execute the prepared SQL statement # $sth->execute or die "Execute of INSERT: ", $dbh->errstr, "\n"; # # Now, if we're not using Oracle, we need to find out what the new unqiue # title id the database assigned to us. In MySQL, the DBD places that into # the statement handle object, which we read directly (which is technically # a violation of OO principles). In Access, we haven't found out how to do # this (yet). if ($DB_type =~ /mysql/i) { $tid = $sth->{'insertid'}; } # # We're done with this statement, free up the resources # $sth->finish; # # Disconnect from the database, we're done! # $dbh->disconnect or warn "Disconnect: ", $dbh->errstr, "\n"; # # Print out a nice big header, # print h1("Title Insertion"); # # Inform the user of successful insertion # print "Insertion of \"$name\" at a price of $price was successful.<br>"; print "New Title ID is $tid"; # # Send </BODY></HTML> # print end_html; exit 0; # # This subroutine is used to cleanly exit the program, # sending proper HTML back to the browser # sub later { my( $goof ) = @_; # # Print out a nice big header, # print h1("Insertion Failed"); print $goof; # # Send </BODY></HTML> # print end_html; exit 0; } sub get_next_title_id { my( $dbh ) = @_; my( $sth, $id ); $sth = $dbh->prepare(q{SELECT title_seq.next_val FROM dual}); $sth->execute || die "Select of sequence id: ", $dbh->errstr, "\n"; ($id) = $sth->fetchrow_array; $sth->finish; return $id; } # # This subroutine checks if a title is already in the title table. # It returns 0 if the title is not in the table, and the title id # if it does exist. # sub is_duplicate { my( $dbh, $name, $price, $edition ) = @_; my( $db_tid, $db_name, $db_price, $db_edition ); my( $sth ); # # Prepare the following SQL statement and receive a statement handle # $sth = $dbh->prepare(qq{ SELECT title_id, name, price, edition FROM title WHERE name = '$name' }) or die "Prepare of SELECT: ", $dbh->errstr, "\n"; # # Execute the prepared SQL statement # $sth->execute or die "Execution of SELECT: ", $dbh->errstr, "\n"; while (($db_tid, $db_name, $db_price, $db_edition) = $sth->fetchrow_array) { if ($price == $db_price and $edition eq $db_edition) { # # We're done with this statement, free up the resources # $sth->finish; return $db_tid; # Cannot be 0! } } # # We're done with this statement, free up the resources # $sth->finish; return 0; }