Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

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
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: (6)
As of 2015-07-05 13:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (67 votes), past polls