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

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

#######################################################
# lect12_connect2
#
# a CGI version of lect12_connect1, still connecting
#    to the Oracle student database on redwood;
# playing with DBI module, continued
#
# by Sharon Tuttle
#
# last modified: 11-09-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=>'lect12_connect2 page'), "\n";

print h1('lect12_connect2 page'), "\n";

print start_form, "\n",
      br, br, "\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",

      "Which table's contents should be displayed: \n",
      textfield(-name=>'table_name',
                -size=>30,
                -maxlength=>30), "\n",

      br, br, "\n",

      submit(-value=>'Submit'), "\n",
      endform, "\n";

print hr, "\n";

# if user has hit submit, try to connect to the database and
#    show the specified table's contents

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")
        or die "Database connection not made: $DBI::errstr";

    # let's set up the SQL command...

    my $table = param('table_name')
        or die("Must enter a table name.\n");

    my $sql_cmd = "select * from $table";

    # feed the SQL statement to the database handle
    #    with a statement handle; first, need to create
    #    this statement handle object:

    my $stmt_handle = $db_handle->prepare( $sql_cmd )
        or die $db_handle->errstr . "\n";

    # now, execute the SQL command using the $stmt_handle object's
    #    execute() method

    $stmt_handle->execute();

    # how can I get the results? Well, one option is to call the
    #    fetchrow_array() method of the $stmt_handle object...

    my @row_values = $stmt_handle->fetchrow_array();

    my $row_length = @row_values;

    # quick'n'sleazy table output --- yes, an HTML table would
    #    be slicker...
    print hr, "\n",
          "<PRE>\n",
          br, "\n";

    while ($row_length > 0)
    {
        foreach (@row_values)
        {
            # because a NULL database cell value is seen as
            #    as undefined in this array;
            if (defined($_))
            {
                printf "%10s", $_;
            }
            else
            {
                print "      NULL";
            }
        }
        print "\n";
        @row_values = $stmt_handle->fetchrow_array();
        $row_length = @row_values;
    }
    print "</PRE>\n";

    # IF you are not reading all of the rows of the executed statement,
    #    I am told that is is good to call finish for your statement
    #    handle BEFORE disconnecting the database handle.
    # SO, consider this a course style standard, too.
    # 
    # (I'm just playing it safe here...)

    $stmt_handle->finish;

    # 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 lect12_connect2.pl