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

# NOTE --- NEED /usr/local/bin/perl, NOT /usr/bin/perl, on sorrel 
# for this to work!!!!! 

# so, we may be using these modules:
use DBI;
use CGI qw/ :standard /;

# set necessary environment variables
# ... this is sorrel's: 
$ENV{'ORACLE_HOME'} = '/apps1/oracle/product/9iAS/';

# ...and so is this:
$ENV{'LD_LIBRARY_PATH'} = '/apps1/oracle/product/9iAS/lib';

my $db_handle = DBI->connect('Student', 'perlplay', 'perlplay', 'Oracle')
    || die "Database connection not made: $DBI::errstr";

# this is simply the SQL command that I wish to run
my $sql_cmd = 'select empl_last_name, job_title from empl'; 

# this sets up a statement handle for that SQL command
$stmt_handle = $db_handle->prepare( $sql_cmd );

# this executes the SQL statement
$stmt_handle->execute(); 

# ...and now you can use the statement handle to fetch the resulting
#    rows.

# Here, I'm going to loop through handling all rows...

while (@emp_info = $stmt_handle->fetchrow_array()) 
{ 
    print "Employee: $emp_info[0] "; 
    print "Job_title: $emp_info[1]\n"; 
} 

# since I did read all of the rows, I received no warning for
# NOT finishing the statement handle...
#$stmt_handle->finish;

# let's try another query...hey, finishing it first WASN'T required!
#$stmt_handle->finish;

# let's ask the user if they want to see the dept_name or job_title

print "Do you want to see dept_name or job_title? ";
chomp(my $choice = <STDIN>);

# WARNING --- I HAVEN'T CKD USER's RESPONSE!!!

$sql_cmd = "select empl_last_name, $choice" .
           " from empl, dept" .
           " where empl.dept_num=dept.dept_num";

$stmt_handle = $db_handle->prepare( $sql_cmd );

$stmt_handle->execute();

($looky, $desired) = $stmt_handle->fetchrow_array();

print "\$looky: $looky, \$desired: $desired\n";

$stmt_handle->finish;

$db_handle->disconnect;

# end of dbi3