#######################################################
#
# display-notes related to Intro to Perl, Bonus Class
#
#######################################################

# last modified: 5-13-03, in-class [with an additional change 10-21-03!]

# today's topic - a TINY taste of connecting Perl to an Oracle database

#------------------------------------------------------------------
# first: (finally) an intro to using Perl MODULES

*   from Ch. 13, "Learning Perl", "Using Simple Modules", p. 185

*   Perl comes with a module named File::Basename, you see;
    *   when built-in, you can see documentation for a module by typing
    something like:

    perldoc File::Basename

    *   does that work on sorrel? yup! 8-)

*   "you declare it with a use directive", traditionaly placed near the
top so human readers (and maintainers!) can more easily tell which modules
are in use;

    use File::Basename;

    *   "During compilation, Perl sees that line and loads up the module."

    *   "Now it's as if Perl has some new functions that you may use in the
    remainder of your program."

#-------------------------------------------------------------------
# module File::Basename function: basename

*   File::Basename has a function basename, for example:

    *   see class-bonus_01_mod1

use File::Basename;

my $name = "/usr/bin/perl";
my $basename = basename $name;

print "\nThe basename of $name is $basename.\n\n";

chomp(my $curr_absolute_filename = `pwd`);
my $curr_basename = basename $curr_absolute_filename;

print "The basename of current directory $curr_absolute_filename\n";
print "   is $curr_basename\n\n";

*   (File::Basename is "smart" enough to tell what kind of machine you're
running, and to use ITs filename rules...!)

#-----------------------------------------------------------------------
# import list in a use declaration

*   you can specify that only certain function names should be provided
from a particular module ---

use File::Basename qw/ basename /;

*   (you can still use those you haven't imported --- BUT only by giving
their *full* names:
  
    *   my $dirname = File::Basename::dirname $name;

    *   can be useful in cases of name collisions between functions...)

#-----------------------------------------------------------------------

*   so, why talk about modules today? Because there's a Perl module
whose purpose is connected Perl to a database...

#-----------------------------------------------------------------
# Source: Sean Reynolds' writeup for CIS 492,
#         "Using Perl to access Oracle databases on campus"

*   we'll be using some stuff from a Perl module DBI: (database independent)

use DBI;

********************************************************************
* NOTE!!!

* I had to change first line to 

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

* ...for 'use DBI;' to not immediately fail...!
********************************************************************

*   ...and from a Perl module CGI, some part of that within :standard...?

use CGI qw/ :standard /;

*   We know from our "taste of CGI" last week that there is an %ENV
hash with various environment variables; we need to set some, now,
if we want to connect to the Oracle database named student on redwood:

$ENV{'ORACLE_HOME'} = '/home/univ/oracle/software/8.1.6';
 
    *   (I updated the above by checking what:
    redwood> echo $ORACLE_HOME

    ...currently returns on redwood; this is what HSU Oracle database
    administrator Peter Johnson also recommended;

$ENV{'LD_LIBRARY_PATH'} = '/home/univ/oracle/software/8.1.6/lib';

    *   (yup, also updated by checking 'echo $LD_LIBRARY_PATH' on redwood
    and confirmed by Peter Johnson;)

*   then, you set up a database handle (is this like a filehandle...?)
using DBI->connect (which probably means this is object-oriented; but that's
beyond the scope of this course...):

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

**********************************************************************
10-21-03: NOW I'm getting errors with the above! Saying it is
          old-style, and soon to be unsupported!! (try_query1.pl)

THIS seems to make it happier, now (for an Oracle db):

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

Above is used in try_query1_1.pl .
************************************************************************

Or, for us, since I've set up a temporary student Oracle account perlplay
with password perlplay:

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

*   BUT, currently this doesn't work. (see file dbi1).
    *   if/when I determine the problem, I'll e-mail to all in the class
    what does work...

*   BUT: when it does... ALSO remember to DISCONNECT the database handle
when you are done!

    $db_handle->disconnect;

#-----------------------------------------------------------------------
# USING a database handle: statement handles

*   (...once you GET that handle...! 8-) )

*   let's create an SQL statement that we want executed under the
database software we've gotten a handle to:

my $sql_cmd = 'select empl_last_name, job_title from empl';

    *   (I've created example tables empl, dept, and customer
    in Oracle student account perlplay...)
 
*   you feed the SQL statement to the database handle with a
statement handle:
                                
    $stmt_handle = $db_handle->prepare( $sql_cmd );

    *   this sets up the statement handle $stmt_handle?

*   you then execute the SQL command by using:
                            
    $stmt_handle->execute();

*   and how do you get the info? You can fetch it:
                                                           
    ($emp_name, $emp_title) = $stmt_handle->fetchrow_array();

    *   ...reads the empl_last_name and job_title from the next row
    into variables $emp_name and $emp_title, respectively

    @emp_info = $stmt_handle->fetchrow_array(); 

    *   ...reads the empl_last_name and job_title into the array 
    @emp_info

        *   (fetchrow_array() must return a Perl list, then!)

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

*   IF you are not reading all the rows of the executed statement, it
is good to call finish for your statement handle before disconnecting
the database handle.
 
$stmt_handle->finish;

#---------------------------------------------------------------------
# some more DBI info gleaned from perldoc DBI

*   note: you get a LOT of info from:

perldoc DBI

*   many database systems have a special value that means lack of
a value (a column with NO value for a particular row) --- this value
is null, in SQL
    *   perldoc DBI notes that "NULL values are represented by undefined 
    values in Perl" --- so, any undef's you encounter can be treated
    as null database values 

*   do you want any change to the database committed as you perform
it, or do you want commits delayed until you explicitly send a commit 
command? Set which you want using AutoCommit value in a hash of 
attribute values that can be sent when you set up your database handle;


#---------------------------------------------------------------------
# 

# end of 180bonux-class_notes.txt