#! /usr/bin/perl -w
# show_tables - list the tables within the univ database.
# Uses "DBI::dump_results" to display results.
use strict; 

# include a collection of DBI database utility routines
use DBI qw( :utils );

# define data source, user name and password as constants
use constant DATABASE => "DBI:mysql:univ";
use constant DB_USER => "lei";
use constant DB_PASS => "lei";

# make a database connection (a database handle is returned)
my $dbh = DBI->connect( DATABASE, DB_USER, DB_PASS )
or die "Connect failed: ", $DBI::errstr, ".\n";

# a MySQL command as a SQL statement to be issued
my $sql = "show tables";

# prepare the SQL statement (a statement handle is returned)
my $sth = $dbh->prepare( $sql );

# execute the prepared SQL statement and show the result
$sth->execute;
print "All talbes in the database:", "\n";
print "===============================", "\n";
print dump_results( $sth ), "\n\n\n";

# prepare and execute another SQL statement
my $sql2 = "select * from student";
my $sth2 = $dbh->prepare( $sql2 );
$sth2->execute;

# retrieve the result
# fetchrow_array retrieve one row from the result table at a time
# and returns an array of elements corresponding the fields in the row
print "Data in the student talbe:", "\n";
print "===============================", "\n";
print "ID	Name	DoB\n";
print "--------------------------\n";
my @student = ();
while (@student = $sth2->fetchrow_array()) {
            print $student[0], "	", $student[1], "	", $student[2],"\n";
}

# remember to release the statement handler and close the database connection
$sth->finish;
$sth2->finish;

# remember to release the statement handler and close the database connection
$sth->finish;
$dbh->disconnect;