# DatabaseTest.pl
# Example program to connect to a database and retrieve information,
# using the Perl DBI database connection package 
# Richard J. White,
# 14 October 2004  first version
#  7 October 2008  improved error reporting 
# 10 October 2008  temporarily changed driver name to mysqlPP for ActivePerl 5.10
#  9 October 2009  similar to DatabaseDemo.pl but with more error testing
#  5 October 2010  include the functionality of listDrivers.pl
# 16 October 2012  default database is now LegumeWeb_10_01

# Import the Perl driver package 
use DBI;  

# Show which database drivers are available 
# (the list should include "mysql")
print "List of available DBI drivers (which should include 'mysql'):\n";
my @available_drivers = DBI->available_drivers('quiet');
my $driver;
foreach $driver (@available_drivers)
{ print "  $driver";
} 
print "\n\n";

# Install the correct MySQL driver
$drh=DBI->install_driver('mysql');  # $drh=DBI->install_driver('mysqlPP');

# Prompt the user for database connection information
print "Please enter database connection details\n";

$server =   ask_user ("  server", "biodiversity.cs.cf.ac.uk");
$database = ask_user ("database", "LegumeWeb_10_01");
$user =     ask_user ("    user", "bioinfo");
$password = ask_user ("password", "bioinfo");

# Set the special variable $| to "flush" output (make it appear immediately)
$|= 1;
# Then print part of an output line ... 
print "Connecting to the database ... ";  
        
# Establish a connection to the database

#$dbh= $drh->connect("DBI:mysql:host=$server;port=3306;database=$database", 
#                    $user, $password, {RaiseError=>0,RaiseError=>0}); 

$dbh= $drh->connect("DBI:mysql:host=$server;port=3306;database=$database", 
                    $user, $password, {});

if ($dbh)
{ # Report success ... and finish the output line
  print "succeeded\n";     
}
else
{ # Report failure ... and finish the output line
  print "failed\n" . DBI->errstr;   
  die "\nCannot continue without a database connection!\n";
}

print "Preparing SQL statement ... ";  
        
#if ($stmt = $dbh->prepare("sho w tables;", {RaiseError=>0,RaiseError=>0}))

if ($stmt = $dbh->prepare("show tables;"))
{ # Report success
  # Note that at this point the SQL statement is not known to be correct
  print "succeeded\n";     
}
else
{ # Report failure
  print "failed\n" . DBI->errstr;   
  die "\nCannot continue without an SQL statement!\n";
}

print "Executing SQL statement ... ";  
        
# Execute the statement
if ($stmt->execute)
{ # Report success
  print "succeeded\n";     
}
else
{ # Report failure
  print "failed\n" . DBI->errstr;   
  die "\nCannot continue: SQL statement failed!\n";
}

print "--------------------------\n";
   
# Loop to print every record returned
while (@result = $stmt->fetchrow_array)
{ # Print the fields of one record
  $i = 0;
  while ($field = $result[$i++])
  { print "| $field \t";
  }
  print "|\n";
}
            
print "--------------------------\n";
        
# Close the statement and connection
$stmt->finish;
$dbh->disconnect;

# Subroutine to prompt user for variable value, with default
sub ask_user
{ print "$_[0] [$_[1]]: ";
  my $rc = <>;
  chomp $rc;
  if($rc eq "") { $rc = $_[1]; }
  return $rc;
}

# End of DatabaseTest.pl