#! /usr/bin/perl -w
# Cached Query Example
use strict; 
use DBI;

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

# get next student ID from keyboard
print "Please enter student ID (q to quit): ";

my $sth;
while(<>) {
	# exit the loop if the user enters "quit"
	chomp();
	if ($_ eq 'q') {
		print "Bye!\n";
		last;
	}

	# a SQL statement to be issued
	my $sql = "select sName from student where sID = $_";

	# prepare the SQL statement
	$sth = $dbh->prepare_cached( $sql );

	# execute the prepared SQL statement and show the result
	$sth->execute;
	
	# retrieve the result
	my @student = $sth->fetchrow_array();

	print "The name of the student is: ";
	
	if (defined $student[0]) {
		print $student[0], "\n";
	} else {
		print "[NO SUCH STUDENT]", "\n";
	}

	# get next student ID from keyboard
	print "\nPlease enter student ID (q to quit): ";

}

$sth->finish;
$dbh->disconnect;