Re: DBD::Sybase rollback()

[Terence J. Young, D.C.]

I have seen something similar when connecting to Sybase using another 
product, Studio.  If Sybase has an unfinished select results table on 
the back end,  subsequent commits or rollbacks will fail unless you do 
something to clear this table.  This situation arises if I do a single 
fetch(via a program called Studio); even when I expect the query to only 
return one row.  I  have to do a subsequent fetch to return null and 
clear the results table on the back end.  If I don't do this, then I 
have to clear the statement (using statementobj.$clear() before 
executing another update, insert, commit or rollback.  Studio provides 
for a sessionobj.$clear() that clears all the statements for that session.

So, in sybase, I beleive that you do have to clear any pending results 
tables before trying to execute an other action..like rollback, commit, 
update, insert, or delete.

I believe the equivalent for clearing the statement in DBD:Sybase is 
$sth->finish.  $dbh->finish will probably clear all the statements for 
the session.

Using perldoc DBD::Sybase, read up on syb_flush_finish (bool)...
syb_flush_finish (bool)
          If $dbh->{syb_flush_finish} is set then $dbh->finish will 
drain any
          results remaining for the current command by actually fetching
          them.  The default behaviour is to issue a ct_cancel(CS_CAN-
          CEL_ALL), but this appears to cause connections to hang or to 
fail
          in certain cases (although I've never witnessed this myself.)

Try executing $sth->finish;  before your $dbh->rollback
Try slamming it with $dbh->finish; before your $dbh->rollback (if you 
hang, try turning on syb_flush_finish (bool))

good luck,
terry
Henri Asseily wrote:
Hello,
I have the following code using DBD::Sybase under apache 1.3/mod_perl 
1.xx, where AutoCommit is off:

        $dbh->{RaiseError} = 1;
        eval {
                $sth = $dbh->prepare_cached('exec xx ?');
                $sth->bind_param(1, $a,           { TYPE => 
SQL_INTEGER });
                $sth->execute;
                $sth->finish;

                [ other non-dbi stuff being done here ... ]
                $dbh->commit;
        };
        if ($@) {
                my $prev_error = $@;
                eval { $dbh->rollback };
                $dbh->{RaiseError} = 0;
                return ([ -999, "Got error: $prev_error"]);
        }
        $dbh->{RaiseError} = 0;
I know that the DBI docs say that we technically can simply rollback a 
dbh without worrying about finishing a sth, but is that certainly the 
case for all DBDs? I seem to have a problem where if the statement 
execute fails due to for example a unique index duplicate, the apache 
process hangs with infinite loop AFTER it successfully fully processes 
the request and returns properly. That is very weird to say the least.

Any ideas?
Thanks.