Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re^3: DBD Sybase Transaction begin_work fails , post #2

by vitoco (Friar)
on Sep 16, 2009 at 18:26 UTC ( #795690=note: print w/ replies, xml ) Need Help??

in reply to Re^2: DBD Sybase Transaction begin_work fails , post #2
in thread DBD Sybase Transaction begin_work fails , post #2

Shouldn't that be in this order:

$dbh->rollback; # <---- still records get committed into the dB !!! $sth->finish;

Also, DBD::Sybase says something about $h->{AutoCommit}... Did you set that to off somewhere?

Comment on Re^3: DBD Sybase Transaction begin_work fails , post #2
Select or Download Code
Replies are listed 'Best First'.
Re^4: DBD Sybase Transaction begin_work fails , post #2
by vcoderv (Initiate) on Sep 16, 2009 at 18:51 UTC
    Yes, AutoCommit => 0
    when I switch the order:

    I get this error @ ->rollback :
    DBD::Sybase::db rollback failed: OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (49) Server ETMA_NYDEV_DS, database Message String: ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending.

    Now the code looks like this:
    my %attr = ( 'RaiseError' => 1, 'PrintError' => 1, 'ChopBlanks' => 1, 'AutoCommit' => 0, ); print "establishingh connection\n"; my $dbh = DBI->connect("dbi:Sybase:server=ETMA_NYDEV_DS;database=tss_d +s_dev2","uid","******", \%attr); print "preparing call to PerlTest sp\n"; my $sth = $dbh->prepare("exec PerlTest2 \@RetVal = ? OUTPUT, \@SomePar +am = ? "); $sth->bind_param(1, undef, SQL_INTEGER); $sth->bind_param(2, "val1", SQL_VARCHAR); print "executing...\n"; $sth->execute; $sth->bind_param(1, undef); $sth->bind_param(2, "val22"); $sth->execute; # exec again... print "rolling back...\n"; # lets rollback $dbh->rollback; $sth->finish; print "finishing...\n"; $dbh->disconnect; close dbh; print "----disconnected------\n"; exit 0;

      Works for me with DBD::Sybase 1.07_01, DBI 1.607 and Sybase ASE 15.0.3 Dev Edition, with the original order of finish() and rollback(), except that I had to add syb_chained_txn  => 0, into the %attr because otherwise I got "Stored procedure PerlTest2 may be run only in unchained transaction mode."

      Maybe there's something spicy going on with your mix of CHAINED/UNCHAINED mode, AutoCommit status and whatever's inside the stored procedure. I used just

      create procedure dbo.PerlTest2( @id integer = NULL OUTPUT, @SomeParam varchar(100) ) as begin INSERT INTO test (stuff) VALUES (@SomeParam) SELECT @id = @@IDENTITY end go Grant Execute on dbo.PerlTest2 to test go sp_procxmode 'PerlTest2', unchained go

      Update 2009-09-17: tested with newly built DBD::Sybase 1.09. Works correctly as well.

      Update 2009-09-17 number 2: you can get the 1.09 for ActivePerl 5.8 by ppm install

      Enoch was right!
      Enjoy the last years of Rome.

        THANKS --- Now I see what I was doing wrong --- i was setting the syb_chained_txn => 0 on a sth instead of dbh .. Thanks again!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://795690]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2015-12-01 03:49 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (792 votes), past polls