#! /usr/bin/perl -w
# Commit/Rollback 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, {AutoCommit => 0})
or die "Connect failed: ", $DBI::errstr, ".\n";

# a MySQL command as a SQL statement to be issued
my $sql = "insert into student values('20','Lei','1978-04-08')";

# 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 out the student table before commit/rollback
my $sql2 = "select * from student";
my $sth2 = $dbh->prepare( $sql2 );
$sth2->execute;
print "Here is the student table before commit/rollback\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";
}

# ask for confirmation
print "One row is to be inserted into the database, continue?[y/n]\n";
if (<STDIN> =~ /y/) {
	$dbh->commit;
} else {
	$dbh->rollback;
} 

# print out the student table after insertion
$sth2->execute;
print "Here is the student table after commit/rollback\n";
print "================================================\n";
print "ID	Name	DoB\n";
print "--------------------------\n";
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;
$dbh->disconnect;