#!/usr/bin/perl
use DBI;
use DBD::mysql; # for MySQL direct access. Note that installation of DBD::mysql under Ubuntu requires "sudo apt-get install mysql-client" first.
use DBD::ODBC; # for ODBC

# References include
# DBI usage - http://www.foo.be/docs/tpj/issues/vol4_2/tpj0402-0023.html
# DBD::mysql - http://search.cpan.org/~capttofu/DBD-mysql-4.018/lib/DBD/mysql.pm
# More on DBI/DBD::ODBC usage - 1 - http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
# More on DBI/DBD::ODBC usage - 2 - http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

# Example of mysql remote access... exactly as above, but run this first:
# ssh servername -L 3306:localhost:3306

sub open_mysql_db
{
	($database, $host, $port, $user, $password) = @_;
	# note that "localhost" doesn't work as a hostname; use "127.0.0.1" instead
	my $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
	my $dbh = DBI->connect($dsn, $user, $password) or die "Couldn't connect to MySQL database. DSN: $dsn\n";
	print "Connected to MySQL database. DSN: $dsn\n";
	return $dbh;
}

sub open_odbc_db
{
	($dsn, $user, $password) = @_;
	my $dsn = "DBI:ODBC:$dsn";
	my $dbh = DBI->connect($dsn, $user, $password) or die "Couldn't connect to ODBC database. DSN: $dsn\n";
	print "Connected to ODBC database. DSN: $dsn\n";
	return $dbh;
}

sub print_query_output
{
	($dbh, $sql) = @_;
	my $query_handle = $dbh->prepare($sql) or die "Couldn't prepare query. SQL: $sql\n";
	$query_handle->execute() or die "Couldn't query database: " . $query->errstr;
	my @row;
	while (@row = $query_handle->fetchrow_array) {  # retrieve one row
	    print join(", ", @row), "\n";
	}
}

# Let's do it.

# my $dbh = open_mysql_db("xmas", "127.0.0.1", "3306", "root", "MYPASSWORD");
# my $table = "xmas_addresses"; # Table we'd like to query

# my $dbh = open_odbc_db("xmas", "root", "MYPASSWORD");
# my $table = "xmas_addresses"; # Table we'd like to query

my $dbh = open_odbc_db("tim1", "", "");
my $table = "Rats"; # Table we'd like to query

print "Querying tables in database...\n\n";
$sql1 = qq{
	SHOW TABLES
};
print_query_output($dbh, $sql1);
# This won't work via the MDB driver, which is somewhat deficient (see "man mdb-sql"). To get a table listing, use the command line ("mdbtables <filename.mdb>")
# or MDB in interactive mode ("mdb-sql <filename.mdb>") followed by "list tables" or "describe table XXX", with each command followed by "go".

print "\nQuerying one particular table...\n\n";
$sql2 = qq{
	SELECT * FROM $table
};
print_query_output($dbh, $sql2);

