Issues with prepare_cached

[mpeppler]

I am using Sybase Open Client 15.0.5 and ran into some problems.

I use DBIx::Class and posted my problem on the DBIC mailing list: http://lists.scsys.co.uk/pipermail/dbix-class/2015-March/011991.html

Trying to find the cause of the error I tried to reproduce with DBD::Sybase directly I found out that in certain cases statement handles don't seem to be cleaned correctly.

I'm doing a transaction with a rollback like the following. This transaction is rolled back because of a unique key.

eval { $dbh->begin_work; my $sth = $dbh->prepare_cached(<<"EOM"); INSERT INTO test1 ( name) VALUES ( 'test' ) SELECT MAX(id) FROM test1 EOM $sth->execute; }; if ($@) { $dbh->rollback; } else { $dbh->commit; }

I get the expected error message Attempt to insert duplicate key but also I get this error message at the end of the script during destroy: DBD::Sybase::st DESTROY failed: OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (159) Server server, database Message String: ct_cmd_drop(): user api layer: external error: This routine can be called only if the command structure is idle.

[mpeppler]

Sample repro:

!/usr/bin/env perl

use strict; use warnings; use 5.010; BEGIN { $ENV{LANG} = "C"; $ENV{DBI_TRACE} = 4; } use Net::Netrc; use Data::Dumper; use DBI;

my $server = $ENV{HOST}; my $db = $ENV{DB}; my $user = $ENV{USER} || (getpwuid($>))[0]; my $cred = Net::Netrc->lookup($server, $user); unless ($cred) { say "Could not find netrc entry for $user \@ $db"; exit; } my ($login, $pass) = $cred->lpa; my $dsn = "dbi:Sybase:server=$server;database=$db";

my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 1, syb_chained_txn => 0, RaiseError => 1, });

my $i = 101; for (1..2) { eval { $dbh->begin_work; my $sql = <<"EOM"; INSERT INTO test1 ( name) VALUES ( 'test$i' ) SELECT MAX(id) FROM test1 EOM my $sth = $dbh->prepare_cached($sql); $sth->execute; warn "after execute"; $sth->finish; }; my $error = $@; if ($error) { warn "ERROR IN TRANSACTION: $@"; $dbh->rollback; } else { $dbh->commit; } } warn "end";

[mpeppler]

This actually throws up a different kind of issue, specifically with transaction management when using prepare_cached.

As prepare_cached doesn't really bring much benefit in the case of Sybase I've taken the decision to remap prepare_cached as prepare. It's a bit of a cop-out but it won't have any functional effect and will mean all scripts will work as expected.