vcoderv has asked for the wisdom of the Perl Monks concerning the following question:

Hi there --- first of all I am new to Perl - so pls be patient :) Having trouble with DBD Sybase trying to start a transaction in the code, getting this error DBD::Sybase::db begin_work failed: Already in a transaction..

Syb OpenClient Ver: Sybase Client-Library/12.5.1/P-EBF16000 ESD #24/DRV.12.5.1.10/Linux x86_64/Linux 2.4.21-47.0.1.ELsmp x86_64/BUILD1251-055/OPT/Thu Jul 3 19:37:33 2008

Perl version: This is perl, v5.10.0 built for x86_64-linux-thread-multi

Syb ASE Ver: Adaptive Server Enterprise/12.5.4/EBF 14919 ESD#7/P/Sun_svr4/OS 5.8/ase1254/2093/64-bit/FBO/Thu Oct 11 09:25:36 2007

...i am trying to execute a sp, lets say 100 times that inserts some records to the database, and , only if all 100 times are successfull --- then commit, else rollback ...

Here is the code... Any help is appreciated !! THANK YOU!! 
#!/efs/dist/perl5/core/5.10/exec/bin/perl

use EFSPerl::Version 'DBI' => '1.607', 'DBD-Sybase' => '1.09';
use 5.010;
use DBI qw(:sql_types);

my %attr        = (     'RaiseError' => 1,
                        'PrintError' => 0,
                        'ChopBlanks' => 1,
                        'AutoCommit' => 0,
                  );
print "establishingh connection\n";
my $dbh = DBI->connect("dbi:Sybase:server=ETMA_NYDEV_DS;database=some_
+db","some_uid","some_pwd", \%attr);

print "preparing call to PerlTest sp\n";
my $sth = $dbh->prepare("exec PerlTest2 \@RetVal = ? OUTPUT, \@SomePar
+am = ? ");

print "starting transaction\n";
$dbh -> begin_work;   # <<<<< error here ....  DBD::Sybase::db begin_w
+ork failed: Already in a transaction

$sth->bind_param(1, undef, SQL_INTEGER);
$sth->bind_param(2, "val1", SQL_VARCHAR);

print "executing...\n";
$sth->execute;

print "rolling back...\n";
# lets rollback
$dbh->rollback;

print "finishing...\n";
$sth -> finish;
$dbh->disconnect;

close dbh;
print "----disconnected------\n";
exit 0;

[download]

Replies are listed 'Best First'.
Re: DBD Sybase Transaction begin_work fails
by kennethk (Abbot) on Sep 15, 2009 at 16:02 UTC
    If you read the DBI documentation for begin_work, it says:

    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.

    So why are you calling it? When your create your connection, you've already disabled autocommit with

     'AutoCommit' => 0,

    I suspect the failure is happening because you are calling begin_work after you have created a statement handle with $dbh->prepare.

[reply]
[d/l]

      Another way of putting it is that $dbh->{AutoCommit} = 0; and $dbh->begin_work; are two ways of starting a transaction.

      I suspect the failure is happening because you are calling begin_work after you have created a statement handle with $dbh->prepare.

      No, the error occurs because transactions aren't nestable, at least not in DBI.

[reply]
[d/l]
[select]
      Hi , with AutoCommit -> 0, I removed begin_work but the rollback, at the end did not actually rollback anything, stored proc created records... but the records were only supposed to be kept if i committed.. but i rolled back..
[reply]
Re: DBD Sybase Transaction begin_work fails
by mje (Curate) on Sep 15, 2009 at 16:24 UTC

    I think you need to decide if you want your transactions automatically committed or whether you are going to do it. i.e., leave AutoCommit as it is by default and start your transactions specifically with begin_work or disable AutoCommit and commit all transactions. You probably want the former as a) it allows you to control better where you specifically want transactions and b) some databases need commits in places you might be surprised - e.g., after a select.

[reply]
      I left AutoCommit -> 1, began transaction with $dbh -> begin_work right after connecting, prepared statement, and executed it: got this DBD::Sybase::st execute failed: Server message number=7713 severity=16 state=2 line=1 server=ETMA_NYDEV_DS text=Stored procedure 'PerlTest2' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode. Issuing rollback() for database handle being DESTROY'd without explicit disconnect() at ./syb_test.pl line 28.
[reply]
Re: DBD Sybase Transaction begin_work fails
by vcoderv (Initiate) on Sep 15, 2009 at 17:48 UTC
    Hi , with AutoCommit -> 0, I removed begin_work but the rollback at the end did not actually rollback anything, stored proc created records... but the records were only supposed to be kept if i committed.. but i rolled back, so i expect nothing to be committed to the disk...
    i am essentially trying to execute a sp, lets say 100 times that writes some records to the database, and , only if all 100 times are successfull --- then commit, else rollback ...
[reply]

Back to Seekers of Perl Wisdom