BEGIN, ROLLBACK,COMMIT

Yuri,

Here's one way. Turn off autocommit, do all database inserts, updates, or
deletes within an eval block in order to trap exceptions. Then check $@. If it
is defined, an exception happened, so you might rollback. Otherwise, commit.

The code snippet below illustrates.

Paul Laub

Hello,

When i starts transaction with BEGIN from my Perl scripts using
DBD::Pg module i want to get in script status after COMMIT
executed.

I mean, if ROLLBACK used i want to inform user to try again
later or something else

How can i do it

------------
With respect,
Yuri A. Kabaenkov
hellman@artofit.com

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, $password,
{
AutoCommit => 0, # Turn off autocommit to allow rollback.
PrintError => 0, # I create my own error messages using $handle->errstr
RaiseError => 0 # I use die within eval to raise exceptions.
}
) or die "Cannot connect!\n$dbh->errstr";

my $sqlinsert = "insert into ...";
my $sqlinsert_h = $dbh->prepare($sqlinsert);

eval {

foreach $record (@array) {
...
$sqlinsert_h->execute($arg1, $arg2)
or die "\nERROR: SQL insert statement failed for "
. "arg1 $arg1, arg2 $arg2\n$sqlinsert_h->errstr";
...
}
};

if ($@) {
print "Rolling back on error.\n$@\n";
$dbh->rollback();
} else {
print "Transactions successful. Committing them.\n\n";
$dbh->commit();
}

$dbh->disconnect;