byterock

Ouch I didn't Mean to do That

In part 8 we used fetchall_arrayref to get just the fields we want and not the entire row now we will looks at when we make mistakes.

DBI Transactions

In a perfect world, we would never need our old friends commit and rollback. But hard disks die, the power goes out, and fingers click the wrong buttons; so DBI supports the age-old ACID Module for transactions. As long as the underlying DBD driver and RDBMS and Driver support it as well.

To get transactions to work on DBI, you first have to set the AutoCommit attribute of our database handle to off. One normally does this when creating the handle like this:

$dbh = DBI->connect('dbi:Oracle:',
'hr@localhost/XE',
'hr', {AutoCommit => 0});

We can now use the DB handle's rollback and commit methods much as you would in any PSQL program.

UPDATE employees SET salary = ? WHERE last_name = ?

To use transactions with the foregoing SQL, you would create the handle like this:

$dbh = DBI->connect('dbi:Oracle:' , 'hr@localhost/XE', 'hr', {AutoCommit => 0});

You could then write Perl like the following.

$dbh = DBI->connect('dbi:Oracle:'
, 'hr@localhost/XE',
'hr', {AutoCommit => 0});

$last_name = "Cambrault";
$salary = 200000;
$sql = "UPDATE employees SET salary = :p_salary
WHERE last_name = :p_last_name";
$sth = $dbh->prepare($sql);
$sth->bind_param(':p_salary', $salary);
$sth->bind_param(':p_last_name', $last_name);
$rows_affected = $sth->execute();

if ($rows_affected > 1){

$dbh->rollback();

print "There are $rows_affected employees with ";

print "the last name $last_name. Transaction canceled!\n";

}
else{

$dbh->commit();
print "$last_name's salary is now $salary\n";
}

The Better Way

The above example will work fine but the much better way to do it is catch the error coming from the DBD Driver by using the 'RaiseError' attribute which will raise exceptions rather than simply return error codes. As well you could be fairly sure it will you code will work across more DBD.

So we add in the 'RaiseError' to our connect call;

$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr', {AutoCommit => 0, RaiseError => 1});

eval {

$rows_affected = $sth->execute();

};

and catch the error

if ($@){ $dbh->rollback(); print "There are $rows_affected employees with "; print "the last name $last_name. Transaction canceled with this error: $@!\n";

}
else{

$dbh->commit();
print "$last_name's salary is now $salary\n";

}

Hey What About Me??

This all goody goody for DBD::Oracle as it fully supports transaction what about me and my CVS??

Well all DBD fall into one of the three categories

1. DB Dose not support Transactions so setting AutoCommit to '0' should throw an error.
2. DB Dose Support Transactions so the coded examples above should works.
3. DB Must explicitly start its transactions. For these the DBD driver should kludge things a little so it works like 2.

Disconnect
A bit a hole here in the Spec as things are largely left up to the underlying DBD. Good practice is to either explicitly call 'commit' or 'rollback' before you call 'disconnect'. How about our old friend 'finish'. Well what about it! Calling it does nothing in terms of transactions so go ahead if you want. Well then what about 'DESTROY'? Well in the case you want to call it the the DBD driver should 'rollback' and thus wipe out any uncommitted transactions on you.