Perl of Wisdom - MySQL

You should now know a little bit about how transactions work at the SQL command prompt. This is great for learning purposes; however, in the real world, it's unlikely that your application will be interacting with MySQL through a command prompt. With that in mind, let's look at a simple application (written in Perl) that implements a transaction using Perl DBI methods.

#!/usr/bin/perl
# load module
use DBI();
# variables for the transaction
# assume these have come from standard input
$name = "ian";
$pass = "storm";
@groups = ("hr", "admin");
$mhost = "my.pop.server";
$muser = "my.name";
$mpass = "my.pass";
# connect
my $dbh = DBI->connect
("DBI:mysql:database=master;host=192.168.0.241",
"root", "secret", {RaiseError => 1, AutoCommit => 0});
# place the transaction in an eval{} block
# so that errors can be trapped for rollback
eval
{
# insert user record
$dbh->do("INSERT INTO users (name, pass) 
VALUES ('$name', '$pass')");
# get ID
$id = $dbh->{'mysql_insertid'};
# insert group memberships
foreach $g (@groups)
{
$dbh->do("INSERT INTO groups 
(uid, grp) VALUES ('$id', '$g')");
}
# insert mailbox data
$dbh->do("INSERT INTO mailboxes (uid, host, 
mboxname, mboxpass) VALUES ('$id', 
'$mhost', '$muser', '$mpass')");
# got this far means no errors
# commit
$dbh->commit();
};
# if any errors
# rollback
if ($@)
{
print "Transaction aborted: $@";
$dbh->rollback();
}
# clean up
$dbh->disconnect();

The transaction in question here is the same as before - adding a user to the system - only the method differs. The first step is to connect to the database using the connect() method. Note the addition of the AutoCommit parameter to connect() - as explained in the previous segment of this tutorial, this tells MySQL to turn off automatic commits, and turns every query into a transaction that must be explicitly committed in order for it to be saved.

Once a connection has been opened, standard INSERT statements are used to insert the new user's data into the system. These statements are enclosed in an eval{} exception handling block, so that errors, if any, are trapped and escalated upwards to the main program. An error in the eval{} block will be stored in the special $@ variable, and caught by the following "if" block, which rolls the entire transaction back with rollback(). If there are no errors, the transaction will be committed with commit().

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus