### 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 min(WID) from Dataset where Name='KEGG Database'";
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() ) {
    $kegg = @row[0];
}

#
# Introduction of the query
#
print"\n
In this query, we count how many reactions there are in the reference 
KEGG pathways which are involved in B. subtilis.\n";
print"\n
In the BioSPICE Warehouse schema, this corresponds to joining 
KEGG(unique warehouse dataset ID $kegg) reference pathways and 
reaction tables for B. subtilis to specify those reference pathways that include 
B.subtilis reactions.\n";
#
# The SQL query
#
$sqlquery = "select count(distinct reaction.ecnumber) 
from reaction, pathway, pathwayreaction
where reaction.wid = pathwayreaction.reactionwid
and pathway.wid = pathwayreaction.pathwaywid
and pathway.type = 'R' and pathway.datasetwid = $kegg
and pathway.name in
(select pathway.name from pathway
where pathway.datasetwid = $kegg 
and pathway.organismwid = (select wid from organism where datasetwid=$kegg and name='B.subtilis'))";
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;
