[Dbix-class] Problem with Sybase, Rollbacks and prepare_cached

[Tina Müller]

Hi,

I am working with Sybase Open Client libs and the latest
DBIx::Class.
I stumbled over this weird thing:
Doing two transactions with rollbacks (because of a duplicate)
results in:

Setting of CS_OPT_CHAINXACTS failed.
...
Unexpected transaction depth of 1 on freshly connected handle

Here are my classes, the create table and the output:
http://paste.scsys.co.uk/469383

I have been debugging a lot.
The CS_OPT_CHAINXACTS error message comes from DBD::Sybase dbdimp.c.

Turning DBI_TRACE on additionally gives me:
Message String: ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending.

It turns out that turning off prepare_cached helps. Then I get
the expected "Attempt to insert duplicate key" in both transactions,
and transaction_depth is set to 0.
So it seems that the $sth is not really "finished", when rollback is
called after the first transaction.
This is probably because of the INSERT command. To retrieve the
insert id the command is actually an INSERT plus SELECT MAX(id)...
(The error does not happen with an UPDATE, for example)

I've been debugging in DBIC::Storage::DBI::_dbh_execute mainly.

If I do two transactions/rollbacks with plain DBI and prepare_cached,
I get this error at the end of the script:
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.

This also confirms that there might be a statement handle which
is not finished.

Although this points to DBD::Sybase I thought I ask here, since
it looks like there is a lot of magic in Storage::DBI::* and the
author(s) maybe know about the problem.

I'm glad for any hints. The current workarounds could be to
turn of prepare_cached, or to disconnect dbh after a rollback
happened.

Thanks,
tina