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($&gt;))[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.