#!/usr/local/bin/perl

# LHW: Author: Francis Ng Hoong Kee
# LHW:
# LHW:   This is a sample program written for the UIT2201 Project.
# LHW:   The purpose of this program is to illustrate how to
# LHW:   use a perl program to access an oracle database.
# LHW:  
# LHW:   This program takes input a name
# LHW:      as the first parameter (which is @ARGV[0] below)
# LHW:   output: 1 if name is found, 2 if name is not found.
# LHW:
# LHW:
# LHW: Question (LHW): 
# LHW:   Is there a way to isolate the part the deals with the
# LHW:   part on establishing the connection so that NOT EVERYONE
#  LHW:  has to deal with this?    

use DBI;

## LHW: Comment: Could this be unsafe?
## LHW:

# initialize database connection
$ENV{'ORACLE_HOME'} = '/opt/oracle';
$drh = DBI->install_driver('Oracle');
$dbh = $drh->connect('sid3.comp.nus.edu.sg', 'idea', 'uit-2201');

# 1=found, 2=not found      (by default set to 2)
$found=2;


## LHW: First write out the SQL statement;
## LHW:   then execute it;
## LHW:   then "catch" the output

# search the database
$sh = $dbh->prepare("select * from names");
$sh->execute;
while (($id,$name)=$sh->fetchrow()) {
  $name =~ s/\s+$//;  # trims all trailing spaces
  $found=1 if ($name eq @ARGV[0]);
  last if ($found==1);
};

# close database connection
$sh->finish;
$dbh->disconnect;

# return the output
print "$found";