I am able to enact transaction processing in sybase with the
DBI/DBD::Sybase combination with autocommit set to on. My code looks
like this..
#step three, set LastId.LastUsedId for AltFundingHist.KeyId entry..allow
a 20 KeyId padding
my $start_value = $rv;
$sql_script = qq( begin transaction
update prod..LastId
set LastUsedId = LastUsedId + $start_value
+ 20
where
TableName = 'AltFundingHist.KeyId'
if ([EMAIL PROTECTED]@error
!= 0)
begin
rollback
raiserror 20028 "Couldn't make new Id"
end
else
begin
select
LastUsedId
from
prod..LastId
where
TableName= 'AltFundingHist.KeyId'
if ([EMAIL PROTECTED]@error != 0)
begin
rollback
raiserror 20028 "Couldn't make
new Id"
end
else
begin
commit
end
end
);
eval {
print "...updating LastId.LastUsedId for AltFundingHist.KeyId
entry....\n";
print "......please be patient...\n";
$cRows_aref = $dbh->selectall_arrayref( $sql_script);
};
if ($@) {
my $error_desc = "$@";
$error_desc =~ s/\s+$//;
print "...Failed to update LastId.LastUsedId for
AltFundingHist.KeyId entry\n";
print "...Error: $error_desc\n...Script will now quit.\n";
print {$error_log} "...Failed to update LastId.LastUsedId for
AltFundingHist.KeyId entry\n";
print {$error_log} "...Error: $error_desc\n...Script will now
quit.\n";
&graceful_exit();
}
else {
print "......LastId.LastUsedId (where TableName =
AltFundingHist.KeyId) set to $cRows_aref->[0][0] \n";
}
I tested this by forcing errors (thru malformed sql) at the second part
of the transaction, the rollback went thru.
terry
Martin J. Evans wrote:
Tom Mornini wrote:
You need to turn AutoCommit off with:
my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',
{ PrintError => 1, RaiseError => 0,
AutoCommit => 0 });
You should not have to turn autocommit off with begin_work:
From the docs:
"Enable transactions (by turning AutoCommit off) until the next call
to commit or rollback. After the next commit or rollback, AutoCommit
will automatically be turned on again."
I certainly do not turn autocommit off with DBD::ODBC and dbd::mysql
and it works as I'd expect.
Martin
Martin J. Evans wrote:
Tom Mornini wrote:
You need to turn AutoCommit off with:
my $dbh = DBI->connect('dbi:Pg:dbname=test', 'postgres', '...',
{ PrintError => 1, RaiseError => 0,
AutoCommit => 0 });
You should not have to turn autocommit off with begin_work:
From the docs:
"Enable transactions (by turning AutoCommit off) until the next call
to commit or rollback. After the next commit or rollback, AutoCommit
will automatically be turned on again."
I certainly do not turn autocommit off with DBD::ODBC and dbd::mysql
and it works as I'd expect.
Martin
