### Perl module for demo
### Kemal Sonmez
### Wed May 15 09:21:59 PDT 2002
use  DBI;

$argCount = @ARGV;
if( $argCount != 2 ) {
	die "usage: perl $0 userid password\n"
}
my $userid = shift( @ARGV );
my $password = shift( @ARGV );

# Connect to the database.
$dbh = DBI->connect(
		    qq{dbi:Oracle:$ORACLE_SID},
		    $userid,
		    $password,
		    { PrintError => 1, RaiseError => 1, AutoCommit => 0 } )
    or die $DBI::errstr;

# Set DatasetWID variables
$sqlquery = "select WID from Dataset where Name='BioCyc' or Name='BsubCyc'";
my $sth = $dbh->prepare($sqlquery) 
    or die "Can't prepare SQL statement\n";
$sth->execute 
    or die "Can't execute SQL statement\n";
while(@row = $sth->fetchrow_array() ) {
    $bsubcyc = @row[0];
}

#
# Introduction of the query
#
print"\n
In this query, we count how many reactions there are in BsubCyc 
B. subtilis pathway database.\n";
print"\n
In the BioSPICE Warehouse schema, this corresponds to counting BsubCyc reaction entries.\n";
#
# The SQL query
#
$sqlquery = "select count(distinct ecnumber) from reaction where reaction.datasetwid = $bsubcyc";
print"\nSQL query:\n$sqlquery\n";

my $sth = $dbh->prepare($sqlquery) 
  or die "Can't prepare SQL statement\n";
#
# Execute the query
#
$sth->execute or die "Can't execute SQL statement\n";
#
# Print ORACLE output
#
print"\nBioSPICE Warehouse output:\n";
while(@row = $sth->fetchrow_array() ) {
    $line = join('::',@row);
    print "$line\n";
}
warn "Data fetching terminated early by error: $DBI::errstr\n" if $DBI::err;
$dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";
exit;
