### Example perl DBI program to connect to BioSPICE Warehouse
### Kemal Sonmez
### Sun April 14 20:19:50 PDT 2002

# use DBI module
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;
	
# Output description of query
print "Query the entire warehouse for:\n name, warehouse ID, database ID, and database of proteins that have\n 'Isocitrate dehydrogenase' as part of either their name or a synonym.\nThen show the size of this query.\n\n";
    
### Subquery 1
$sqlquery = "select distinct Protein.Name, Protein.WID, XID, Dataset.Name
from Protein, DataSet, DBID, SynonymTable
where Protein.WID=SynonymTable.OtherWID
and (syn like '%Isocitrate dehydrogenase%'
or Protein.Name like '%Isocitrate dehydrogenase%')
and Protein.DatasetWID = Dataset.WID and DBID.OtherWID = Protein.WID" ;
print"SQL query:\n$sqlquery\n";

# prepare a SQL query
my $sth = $dbh->prepare($sqlquery) 
    or die "Can't prepare SQL statement\n";

# send to ORACLE
$sth->execute 
    or die "Can't execute SQL statement\n";

# get and print the output
print"\nBioSPICE Warehouse output:\n";
while(@row = $sth->fetchrow_array() ) {
    $line = join('::',@row);
    print "$line\n";
}

### Subquery 2 - just the count of the above
$sqlquery = "select count(*) from
(select distinct Protein.Name, Protein.WID, XID, Dataset.Name 
from Protein, DataSet, DBID, SynonymTable 
where Protein.WID=SynonymTable.OtherWID 
and (syn like '%Isocitrate dehydrogenase%'
or Protein.Name like '%Isocitrate dehydrogenase%')
and Protein.DatasetWID = Dataset.WID and DBID.OtherWID = Protein.WID)" ;
print"\nSQL query:\n$sqlquery\n";

# prepare a SQL query
my $sth = $dbh->prepare($sqlquery) 
    or die "Can't prepare SQL statement\n";

# send to ORACLE
$sth->execute 
    or die "Can't execute SQL statement\n";

# get and print the output
print"\nBioSPICE Warehouse output:\n";
while(@row = $sth->fetchrow_array() ) {
    $line = join('::',@row);
    print "$line\n";
}

# error check
warn "Data fetching terminated early by error: $DBI::errstr\n" if $DBI::err;



# Disconnect and exit
$dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";
exit;
