#!/usr/bin/perl -w

#######################################################
# lect12_connect1
#
# so, can we connect to Oracle student database from
#    cs-server?
# initial playing with DBI module
#
# by Sharon Tuttle
#
# last modified: 11-08-04
#######################################################

use DBI;

# 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!!!

print "Enter the desired Oracle username: ";
chomp(my $username = <STDIN>);

print "Enter the desired Oracle password: ";
chomp(my $password = <STDIN>);

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

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

print "for what table do you wish to see contents? ";
chomp(my $table = <STDIN>);

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;
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;
}

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

$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();

# end lect12_connect1