Re: Transactions: DBI vs. SQL

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 (\@\@error != 0)
begin
rollback
raiserror 20028 "Couldn't make new Id"
end
else
begin
select
LastUsedId
from
prod..LastId
where
TableName= 'AltFundingHist.KeyId'
if (\@\@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

.

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL SERVER Rollback Problems
    ... It will not be fully committed until you issue a COMMIT TRAN. ... If your sp had an error or was manually stopped the transaction is still ... issue the ROLLBACK ALL operations are rolled back up until that first BEGIN ... > execution. ...
    (microsoft.public.sqlserver.programming)
  • Re: Is statement.executeBacth() a transaction?
    ... point and did not make my question clearer enough. ... rollback() in the catch since the driver already does that, ... The reason I am confused about it is that the "transaction" nature ... A commit is a commit, ...
    (comp.lang.java.programmer)
  • Re: how to roll back
    ... You can only use rollback within a transaction block. ... If you commit a ... COMMIT TRAN, then ROLLBACK TRAN to ...
    (microsoft.public.sqlserver.server)
  • Re: help - transaction control
    ... transaction to successfully commit before any of the inner ones are ... If a Rollback is issued anywhere along the way ALL transactions ... ROLLBACK TRAN ... > My understanding of the way that transactions nest> is that I should be able to have each SP> begin and commit a transaction within itself,> and if the SP happens to be called from some context> that has begun a transaction, then the SP would participate> in that transaction context. ...
    (microsoft.public.sqlserver.programming)