Using Transactions with Perl

In Chapter 10, "Advanced SQL and MySQL," I discuss transactions, which you can do in MySQL as long as you are using a transaction-safe storage engine. During that discussion, I demonstrate the transaction process within the mysql client. You can also use transactions from a programming language, though.

For most languages, this is a four-step process:

1.	Turn off MySQL's autocommit mode.
2.	Run your queries.
3.	Commit or roll back the queries.
4.	Turn MySQL's autocommit mode back on.

In PHP, this would look like:

mysqli_autocommit($dbc, FALSE);
$r = mysqli_query($dbc, query);
// Do whatever else.
mysqli_commit($dbc);
// or
mysqli_rollback($dbc);
mysqli_autocommit($dbc, TRUE);

This assumes you've already established a valid connection and assigned it to the $dbc variable. I'm also referencing the MySQL Improved extension functions, which are available as of PHP 5.

With Java, the code would be:

con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate(query);
// Do whatever else.
con.commit();
// or
con.rollback();
con.setAutoCommit(true);

Again, this assumes that a database connection has been made and assigned to con.

Finally, in Perl, the code would look like (where $dbh is the connection):

$dbh-
$dbh->do(query);
$dbh->commit();
// or
$dbh->rollback();
$dbh->{'AutoCommit'} = 0;

I'll go through a demonstration of the Perl technique by creating a script that transfers money from one record in the test .accounts table to another. If you don't have this table in your database, see Chapter 10 for its CREATE command.

To perform transactions using Perl:

Create a new Perl script in your text editor, starting with the standard lines of code (Script 12.4).

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

If you have any questions about any of the Perl, see Chapter 8, "MySQL and Perl."

Identify what variables are needed.

my ($dbh, $sql, $sth, @row,
   $balance, $affected, $from, $to,
   $amount);

Rather than using my for each individual variable, I'm identifying them all here.

Script 12.4. This Perl script uses transactions to guarantee that a common banking sequencethe transfer of funds from one account to anotherworks.

[View full width]
1 #!/usr/bin/perl -w 2 3 # Script 12.4 - transfer_money.pl 4 # This script lets a user transfer money 5 # from one account to another. 6 7 # Use what needs to be used. 8 use strict; 9 use DBI; 10 11 # Needed variables: 12 my ($dbh, $sql, $sth, @row, $balance, $affected, $from, $to, $amount); 13 14 # Print a message. 15 print "Use this program to transfer money.\n\n"; 16 print "Accounts (Account ID):\n"; 17 18 # Connect to the database. 19 $dbh = DBI->connect("DBI:mysql:test: localhost", 'username', 'password', {RaiseError =>; 1}); 20 21 # Show the current accounts with their IDs. 22 $sql = "SELECT CONCAT(last_name, ', ', first_name) AS name, id FROM accounts ORDER BY last_name, first_name ASC"; 23 $sth = $dbh->prepare($sql); 24 $sth->execute(); 25 while (@row = $sth->fetchrow_array()) { 26 print "$row[0] ($row[1])\n"; 27 } 28 $sth->finish(); 29 30 # Get the information from the user. 31 print "\nFROM whose account is the money being transferred? "; 32 $from = <STDIN>; 33 print "\nINTO whose account is the money being transferred? "; 34 $to = <STDIN>; 35 print "\nHOW MUCH money is being transferred? "; 36 $amount = <STDIN>; 37 38 # Validate the input (omitted). 39 40 # Confirm that the FROM account can afford the transfer. 41 $sql = "SELECT balance FROM accounts WHERE id=$from"; 42 $sth = $dbh->prepare($sql); 43 $sth->execute(); 44 $balance = $sth->fetchrow_array(); 45 $sth->finish(); 46 47 # Can they afford it? 48 if ($balance >= $amount) { # Transact! 49 50 # Start the transaction. 51 $dbh->{'AutoCommit'} = 0; 52 53 # Subtract the amount. 54 $affected = $dbh->do("UPDATE accounts SET balance=(balance - $amount) WHERE id=$from"); 55 if ($affected == 1) { # Good! 56 57 # Add the amount. 58 $affected = $dbh->do("UPDATE accounts SET balance=(balance + $amount) WHERE id=$to"); 59 if ($affected == 1) { # Commit. 60 61 $dbh->commit(); 62 print "\nThe transaction is complete! \n"; 63 64 } else { # Rollback. 65 66 $dbh->rollback(); 67 print "\nAn error occurred! The transaction was rolled back. \n"; 68 69 } 70 71 } else { # First UPDATE didn't work. 72 73 # Cancel the transaction. 74 $dbh->rollback(); 75 76 print "\nAn error occurred! The transaction was rolled back. \n"; 77 78 } 79 80 } else { # Can't afford it! 81 print "\nNon-sufficient Funds! \n"; 82 } 83 84 # Disconnect. 85 $dbh->disconnect;

Print an introductory message and connect to the database.

print "Use this program to transfer money.\n\n";
print "Accounts (Account ID):\n";
$dbh = DBI->connect("DBI:mysql: test:localhost", 'username', 'password', {RaiseError => 1});

Show the current accounts with their IDs.

[View full width]$sql = "SELECT CONCAT(last_name, ', ', first_name) AS name, id FROM accounts ORDER BY
 last_name, first_name ASC";
$sth = $dbh->prepare($sql);
$sth->execute();
while (@row = $sth->fetchrow_array()) {
    print "$row[0] ($row[1])\n";
}
$sth->finish();

The script starts by listing every account by name, showing the account ID in parentheses (Figure 12.22). The program user will reference the account ID in Step 5.

Figure 12.22. Every existing account is listed so that the user can pick the two accounts involved in the transaction.

Get all of the necessary information from the user.

print "\nFROM whose account is the money being transferred? ";
$from = <STDIN>;
print "\nINTO whose account is the money being transferred? ";
$to = <STDIN>;
print "\nHOW MUCH money is being transferred? ";
$amount = <STDIN>;

The user will be prompted for three pieces of input (Figure 12.23). The first is the ID number of the account from which the money is coming. The second is the ID number of the account into which the money is going. The third is the amount of money.

Figure 12.23. The script's user enters the ID numbers of the involved accounts and then the amount of money being transferred.

You'll likely want to perform some validation on these values, although I omitted it from the script. See Chapter 8 for suggestions.

Confirm that the "from" account can afford the transfer.

$sql = "SELECT balance FROM accounts WHERE id=$from";
$sth = $dbh->prepare($sql);
$sth->execute();
$balance = $sth->fetchrow_array();
$sth->finish();
if ($balance >= $amount) {

Before trying to transfer some money, you ought to check that the user has that much money available. To do so, the current user balance is retrieved from the table.

Begin the transaction.

$dbh->{'AutoCommit'} = 0;

From this point forward, MySQL is in transaction mode.

Subtract the dollar amount.

$affected = $dbh->do("UPDATE
accounts   SET balance=(balance -
$amount)   WHERE id=$from");
if ($affected == 1) {

A simple UPDATE query is run. The results are then checked to make sure that exactly one row was affected.

Add the dollar amount to the other account.

$affected = $dbh->do("UPDATE
accounts   SET balance=(balance +
$amount)   WHERE id=$to");
if ($affected == 1) {

This is the inverse of the query in Step 8. Again the results are confirmed to ensure the transaction worked.

10.

Commit or roll back the transaction.

    $dbh->commit();
    print "\nThe transaction is complete! \n";
} else {
    $dbh->rollback();
    print "\nAn error occurred! The transaction was rolled back. \n";
}

If both UPDATE queries affected one row, the transaction can be committed (the first line here). If the second UPDATE query failed, the transaction should be nullified by rolling it back (this takes place within the else clause). Messages indicate success (Figure 12.24) or failure (Figure 12.25).

Figure 12.24. The transaction was a success!

Figure 12.25. A problem occurred, so neither `UPDATE` was committed.

11.

Complete the two conditionals.

    } else {
       $dbh->rollback();
       print "\nAn error occurred! The transaction was rolled back. \n";
    }
} else {
    print "\nNon-sufficient Funds! \n";
}

The first else clause is the companion to the one in Step 10. It applies if the first UPDATE query fails. The second else clause applies if the "from" account does not have enough money (Figure 12.26).

Figure 12.26. The script will not allow a negative balance in any account.

12.

Disconnect from the database.

$dbh->disconnect;

13.

Save the file as TRansfer_money.pl, change the permissions (if necessary), and run the script.

Tips

In Perl you can also turn off the autocommit mode when you connect:

[View full width]$dbh = DBI->connect("DBI:mysql: test:localhost", 'username', 'password', {RaiseError => 1,
 AutoCommit => 0});

You may want to also add some error reporting to this script. Further, you should check that a valid "from" account was entered, or you may see an error message as $balance ends up with no value.

Calling Stored Procedures

In Chapter 11, "MySQL 5 Features," I cover stored procedures. These are memorized bits of code that are stored in the database. The topic, unfortunately, is not as easily demonstrated using programming languages, as each supports the concept to different degrees. Instead of a full-on example, I'll mention the basic idea for each language.

With PHP, you can call a stored procedure as you would execute any SQL command using mysqli_query():

$r = mysqli_query("CALL procedure_name ($var1, $var2));

If the procedure has several SELECT statements that return several result sets, use mysqi_multi_query() instead.

With Java, you need to create a

[View full width]
CallableStatement object: CallableStatement stmt = con.prepareCall("{CALL procedure_name(?, ?)}");

Then you bind the values to the statement and execute it. The MySQL manual has a couple of examples of this.

As for Perl, sadly the DBD::mysql driver doesn't support stored procedures at the time of this writing. There is a patch that can kind of get this to work, but not reliably.

Stored routines are normally used exactly like the existing MySQL functions. Therefore, you can use stored routines in a query as you would a MySQL function.