Re: Proposed change to disconnect in DBD::ODBC

On 16/03/11 16:07, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> but in the second case DBD::ODBC will rollback the transaction before
>> disconnecting and there is no error or warning.
>> I don't really like this behaviour and would like to change it so in the second
>> case above, DBD::ODBC issues a warning before rolling the txn back so you'd get
>> something like:
>>
>> DBD::ODBC::db disconnect warning: Disconnect with transaction in progress - rolling back
>
> I don't know - it seems that someone issuing an explicit $dbh->disconnect() is far
> different from a script just randomly exiting for one reason or another.

oh, yes, I'd agree with that.

> I'd
> also like to keep DBI consistent as much as possible, and I don't know of any
> other DBDs that issue a warning on disconnect. So a -1 on this proposal, or at
> least maybe a very weak +1 to putting such a thing inside DBI rather than
> DBD::ODBC if others agree on this new behavior.
>

Thanks for the response Greg.

This area is already inconsistent. From DBI under disconnect:

"The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will rollback any outstanding changes."

I tried it with DBD::Oracle using the same code as my DBD::ODBC example and in case 1 the transaction is not committed and in case 2 it is. That differs from the way DBD::ODBC has always been which is to rollback on disconnect no matter what. Note, I'm not suggesting a change to DBD::ODBC rolling back but I could just as easily make it commit like DBD::Oracle.

I'm not sure how we'd get DBI to control this I don't think it knows - all it does on DESTROY right now is to call rollback if disconnect has not been called. However, DBD::ODBC knows when disconnect is called and it calls SQLDisconnect which errors with state 25000 that a txn was in progress.

The reason I was putting this forward is that I got caught out by this and I thought I'd rather see at least a warning.

DBI also says
"Generally, if you want your changes to be committed or rolled back when you disconnect, then you should explicitly call "commit" or "rollback" before disconnecting."

I'm still of the feeling that calling disconnect without committing or rolling back is probably a mistake (and almost definitely a mistake if your code could use multiple DBDs).

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com