-----[snip]------------------
$dbh->{AutoCommit} = 0; # enable transactions, if possible
$dbh->{RaiseError} = 1;
eval {
foo(...) # do lots of work here
bar(...) # including inserts
baz(...) # and updates
$dbh->commit; # commit the changes if we get this far
};
if ($@) {
warn "Transaction aborted because $@";
$dbh->rollback; # undo the incomplete changes
# add other application on-error-clean-up code here
}
-----[snip]------------------So after incorporating this into my code I ended up with a function that looks like this:
-----[snip]------------------
sub dbUpdate { my $process = shift;
if ($process eq "Initial" ) {
%Status = @{$dbh->selectcol_arrayref("SELECT ID,STATUS FROM STATUSIDTAB",
{Columns=>[1,2]})};
} else {
$sthStatusExists = $dbh->prepare_cached('SELECT ID FROM
STATUSCURRENTTAB WHERE ID= ?');
$sthStatusCurrentUpdate = $dbh->prepare_cached('UPDATE
STATUSCURRENTTAB SET STATUS = ?, COMMENTS = ?, LABEL = ?, DEFECT = ?, DATESTAMP = ? WHERE
ID = ?');
$sthStatusHistoryInsert = $dbh->prepare_cached('INSERT INTO
STATUSHISTORYTAB (STATUS, COMMENTS, LABEL, DEFECT, DATESTAMP, ID) VALUES (?, ?, ?, ?, ?,
?)'); $sthStatusExists->execute($Instance);
if (($_ID ) = $sthStatusExists->fetchrow_array()) {
$_Status = $q->param("Status");
$_Comments = $q->param("Comments");
$_Comments ? "" : ($_Comments = " ");
$_Label = $q->param("Label");
$_Label ? "" : ($_Label = " ");
$_Defect = $q->param("Defect");
$_Defect ? "" : ($_Defect = " ");
$_Datestamp = time(); eval {
$sthStatusCurrentUpdate->execute($_Status, $_Comments,
$_Label, $_Defect, $_Datestamp, $Instance);
$sthStatusHistoryInsert->execute($_Status, $_Comments,
$_Label, $_Defect, $_Datestamp, $Instance);
$dbh->commit; # commit the changes if we get this far
};
if ($@) {
warn "Transaction aborted because $@";
$dbh->rollback; # undo the incomplete changes
htmlFileError("Transaction aborted because $@");
} else {
htmlFileSuccess();
}
} else { htmlFileError("Sorry, The instance \"$Instance\" does not have valid history data.");
}
$dbh->disconnect;
}
}
-----[snip]------------------Also there is another approach about 80% down the page at http://www.perl.com/pub/a/1999/10/DBI.html, but I didn't try that code.
Dave Anderson
Mike Blezien wrote:
Hello,
Could someone please post a simple Perl code snip example of a "rollback" or "commit" transaction with a INSERT or UPDATE query... I've been testing transaction and seem to be missing something.
Appreciate it,
TIA
