does disconnect() actually commits transaction even if the connect is made using autocommit=0
Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie
RE: does disconnect() actually commits transaction even if the connect is made using autocommit=0
On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
man DBI: ---8--- ... 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. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. ... ---8--- Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie signature.asc Description: OpenPGP digital signature
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
Well I was already familiar with this behaviour because it's the same with JDBC-Drivers. Tom Martin J. Evans wrote: On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com signature.asc Description: OpenPGP digital signature
Re: Message from Maintainer to DBD::mysql users, developers
On 2006-03-29 09:58:16 -0800, Mark Hedges wrote: On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: [...] But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. That has been my experience as well. DBD::mysql will just pass everything through unaltered. So if you use UTF-8 as connection charset, you have to encode('utf-8', ...) all queries and parameters, unless you are sure that they are either plain ASCII or already have the UTF-8 bit set. And you will get raw UTF-8 strings back, which you have to decode() explicitely. However, I notice that on Debian Sarge (on which I did my testing), libdbd-mysql-perl depends on libmysqlclient12. So there may be a problem with mixing releases (The server is 4.1, but libmysqlclient12 belongs to 4.0, which doesn't know about UTF-8). hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp7K0E14XpOu.pgp Description: PGP signature
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
Tom and Martin, Thank you both! It is a bit counter intuitive. Jie Tom Schindl wrote: Well I was already familiar with this behaviour because it's the same with JDBC-Drivers. Tom Martin J. Evans wrote: On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
Jie Zhang wrote: Tom and Martin, Thank you both! It is a bit counter intuitive. If by that you mean that you expected disconnect with a commit to rollback the changes then I agree. We had an Oracle ODBC driver which used oci which did this and ended up changing it to NOT commit on disconnect because we (and more importantly our customers) believed it was just too dangerous. Off the top of my head I can't remember how this was achieved in OCI but it was. Martin Tom Schindl wrote: Well I was already familiar with this behaviour because it's the same with JDBC-Drivers. Tom Martin J. Evans wrote: On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
Yeah, I mean the disconnect should not commit if autocommit is set to 0. In most of the case, this is not a problem because people probably do rollback and commit. Could you change oci code? Is it owned by Oracle? thanks, Jie Martin J. Evans wrote: Jie Zhang wrote: Tom and Martin, Thank you both! It is a bit counter intuitive. If by that you mean that you expected disconnect with a commit to rollback the changes then I agree. We had an Oracle ODBC driver which used oci which did this and ended up changing it to NOT commit on disconnect because we (and more importantly our customers) believed it was just too dangerous. Off the top of my head I can't remember how this was achieved in OCI but it was. Martin Tom Schindl wrote: Well I was already familiar with this behaviour because it's the same with JDBC-Drivers. Tom Martin J. Evans wrote: On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: does disconnect() actually commits transaction even if the connect is made using autocommit=0
Jie Zhang wrote: Yeah, I mean the disconnect should not commit if autocommit is set to 0. In most of the case, this is not a problem because people probably do rollback and commit. Could you change oci code? Is it owned by Oracle? From what I remember we did not make any change to OCI, we changed how we used OCI. I'll have to look this up after the weekend. This would suggest it is how DBD:oracle uses OCI. If you don't hear anything early next week mail me personally to remind me to look it up. Martin thanks, Jie Martin J. Evans wrote: Jie Zhang wrote: Tom and Martin, Thank you both! It is a bit counter intuitive. If by that you mean that you expected disconnect with a commit to rollback the changes then I agree. We had an Oracle ODBC driver which used oci which did this and ended up changing it to NOT commit on disconnect because we (and more importantly our customers) believed it was just too dangerous. Off the top of my head I can't remember how this was achieved in OCI but it was. Martin Tom Schindl wrote: Well I was already familiar with this behaviour because it's the same with JDBC-Drivers. Tom Martin J. Evans wrote: On 07-Apr-2006 Jie Zhang wrote: Hi, If I initiate a connection using autocommit=0 in DBI and I don't do a explicit connection-commit(), should transactions automatically commit after I do an explicit connection-disconnect()? I was expecting an automatica rollback. But the testing result is just the opposite. For example: my $databaseHandle = DBI-connect( , [EMAIL PROTECTED], $pass, {AutoCommit = 0}, Oracle ) deleteRecord; $databaseHandle-disconnect(); Should the delete query be commited? thanks, Jie According to the DBI docs: The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any out- standing changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect. I was a bit surprised to see the comment on Oracle automatically committing. I believe there is a way to stop this happening at the oci level. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: Help required from dbd::mysql maintainers/authors re MYSQL_VERSION_ID and SERVER_PREPARE_VERSION
Martin, Thanks for the info. Do you have a script that does this? mysql_st_execute41 should be called if mysql supports prepared statements, that is, = SERVER_PREPARE_VERSION. I'm a bit suprised it works, but I think I have code that catches it even if it doesn't support prepared statements, so mysql_st_execute41 might have better handling, even for versions 4.1. Please send whatever script you have to reproduce this. I'd like to see why it works where it shouldn't and where it doesn't where it should. Kind regards, Patrick Martin J. Evans wrote: Hi, I could really do with a little assistance form the dbd::mysql maintainers or authors please. I am investigating the issue I reported here with execute_array or all executes failing after one execute fails. i.e. create table test (a int primary key) prepare(insert into test values(?) execute(1) - OK execute(2) - OK execute(1) - fails but expected execute(3) - fails but row inserted and not expected to fail It appears this has something to do with whether MYSQL_VERSION_ID is = SERVER_PREPARE_VERSION. My myql is 50015 and my client lib is also 50015 - as client and server are on the same machine. SERVER_PREPARE_VERSION is 40103 (in dbimp.h) When I run with dbd::mysql (from 3.0002_4) it fails. When I turn all comparisons of MYSQL_VERSION_ID = SERVER_PREPARE_VERSION around so it is it works. Can someone tell my if dbd_st_execute is supposed to call mysql_st_internal_execute41 for versions of MySQL 40103 and lower or the opposite way around. I suspect it should be: if MYSQL_VERSION_ID SERVER_PREPARE_VERSION use mysql_st_internal_execute41 because other places in the code do things like: #if MYSQL_VERSION_ID SERVER_PREPARE_VERSION if (mysql_real_query(imp_dbh-mysql, COMMIT, 6)) #else if (mysql_commit(imp_dbh-mysql)) #endif because functions like mysql_commit were introduced in 4.1 I'd greatly appreciate any help here as I'm desparately trying to make this work and trying very hard to feedback problems and fixes to DBD::mysql. Martin