Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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?

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]
[choroba]: 20 months? :-o
[Discipulus]: up to 20 months.. but you'll be sure to remain unemployed

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2018-05-23 21:03 GMT
Find Nodes?
    Voting Booth?