Beefy Boxes and Bandwidth Generously Provided by pair Networks Bob
Think about Loose Coupling
 
PerlMonks  

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

by vitoco (Pilgrim)
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
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:

    $sth->rollback;
    $sth->finish;
    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 http://jenda.krynicky.cz/perl/DBD-Sybase.ppd

      Jenda
      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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2014-04-17 01:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (437 votes), past polls