# DatabaseDemo.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 (later moved to DatabaseTest.pl)
#  9 October 2009  similar to DatabaseTest.pl but with minimal error testing
#  5 October 2010  minor edits
# 16 October 2012  default database is now LegumeWeb_10_01

# Import the Perl driver package 
use DBI;  
# Install the correct MySQL driver
$drh=DBI->install_driver('mysql');  

# 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");

# Establish a connection to the database

$dbh= $drh->connect("DBI:mysql:host=$server;port=3306;database=$database", 
                    $user, $password, {}) 
  or die "Couldn't connect to database: " . DBI->errstr;

# Create an SQL statement
$stmt = $dbh->prepare("show tables;");

# or any other SQL statement, such as
#$stmt = $dbh->prepare("select * from Version;");

# Execute the statement
$stmt->execute
  or die "Invalid SQL statement: " . DBI->errstr; 
   
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 DatabaseDemo.pl