#!/usr/local/bin/perl -w

# ^NOTE --- I had to change the perl path on sorrel above
#    so this would work!!!

#######################################################
# lab12_insert_reviewer.pl
#
# quick'n'sleazy table populator for the Week 12 Lab
#    Exercise and HW #10 -
# add a reviewer
#
# by Sharon Tuttle
#
# last modified: 11-11-04
#######################################################

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser carpout);

use DBI;

warningsToBrowser(1);  # activate warnings

# PRINT a form for user to react to

print header, "\n";

print start_html(-title=>'insert reviewer'), "\n";

print h1('insert reviewer'), "\n";

print start_form, "\n",

      "Oracle username: \n", 
      textfield(-name=>'oracle_username',
                -size=>15,
                -maxlength=>15), "\n",
      
      br, br, "\n",

      "Oracle password: \n",
      password_field(-name=>'oracle_password',
                     -size=>10,
                     -maxlength=>10), "\n",

      br, br, "\n",
      hr, "\n",

      "Reviewer id (3 digits): \n",
      textfield(-name=>'reviewer_id',
                -size=>3,
                -maxlength=>3), "\n",

      br, br, "\n",

      "Reviewer Last Name (up to 20 characters): \n",
      textfield(-name=>'reviewer_lname',
                -size=>20,
                -maxlength=>20), "\n",

      br, br, "\n",

      "Reviewer's E-mail address (up to 30 characters): \n",
      textfield(-name=>'reviewer_email',
                -size=>30,
                -maxlength=>30), "\n",

      br, br, "\n",

      submit(-value=>'Insert This Reviewer'), "\n",
      endform, "\n";

print hr, "\n";

# if user has hit submit, try to connect to the database and
#    insert this reviewer

if (param())
{
    # set environment variables needed for Oracle database
    #    software

    $ENV{'ORACLE_HOME'} = '/apps1/oracle/product/9iAS/';
    $ENV{'LD_LIBRARY_PATH'} = '/apps1/oracle/product/9iAS/lib';

    # try to connect to database named student reachable with the
    #    above environment informaton, for the account username and
    #    account password given, for an Oracle database
    # (and remember --- what you connect, REMEMBER TO DISCONNECT!!!

    my $username = param('oracle_username')
        or die "Must enter an Oracle username!\n";

    my $password = param('oracle_password')
        or die "Must enter an Oracle password!\n";        

    my $db_handle = DBI->connect('dbi:Oracle:student',
                    "$username", "$password")
        || die "Database connection not made: $DBI::errstr";

    # try to insert this new reviewer

    my $reviewer_id = param('reviewer_id') 
        or die "Must enter a Reviewer Id!\n";
    my $reviewer_lname = param('reviewer_lname')
        or die "Must enter a Reviewer last name!\n";
    my $reviewer_email = param('reviewer_email')
        or die "Must enter a Reviewer e-mail address!\n";

    # ah, but these need SINGLE QUOTES around 'em for the
    #    SQL insert statement!
    my $qreviewer_id = $db_handle->quote($reviewer_id);
    my $qreviewer_lname = $db_handle->quote($reviewer_lname);
    my $qreviewer_email = $db_handle->quote($reviewer_email);

    $db_handle->do("insert into reviewer
                    values
                    ($qreviewer_id, $qreviewer_lname, $qreviewer_email)")
        or die $db_handle->errstr . "\n";

    # let's show the contents of the reviewer table, now
    my $stmt_handle = $db_handle->prepare( 'select *
                                            from reviewer' )
        or die $db_handle->errstr . "\n";

    $stmt_handle->execute();

    my @next_row;

    print h3("Now the reviewer table contains:"), "\n";

    while (@next_row = $stmt_handle->fetchrow_array())
    {
        print "@next_row", br, "\n";
    }

    $stmt_handle->finish;
    print hr, "\n";

    # DON'T FORGET THIS! Not only is it POOR STYLE to do so, but
    #   could cause REAL Oracle performance problems if you leave
    #   it off!!!!!!!!!!!!!

    $db_handle->disconnect;
}

print end_html, "\n";

# end of lab12_table_setup.pl