Re: Driver AutoCommit issue



Arun wrote:

I use MS SQL Server latest JDBC driver and trying to connect to MS SQL Server 2000 SP3 database using the datasource. I set the auto commit to false on connection as soon as I get the connection from the datasoruce. For some reasons what ever DML statements I execute through the connection it gets committed to the database. below is the snippet of the code that I am trying to execute.

connection = mydataSource.getConnection();
connection.setAutoCommit(false);
logger.debug("Connection Auto Commit Status "+ connection.getAutoCommit()); CallableStatement callableStatement = connection.prepareCall( {call dbo.updateData}");
callableStatement.execute();


The logger printed : Connection Auto Commit Status false.
dbo.updateData is a very simple procedure that updates a table. There not begin transaction or commit transaction in stored procedure.

How are you proving the data is committed? I just ran this code and
got what you'd expect:

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement s = c.createStatement();
try{s.execute("drop table foo");} catch (Exception ignore){}
try{s.execute("drop procedure bar");} catch (Exception ignore){}
s.execute("create table foo(qwe int)");
s.execute("create procedure bar as insert foo values(1)");

PreparedStatement p = c.prepareStatement("{ call bar()}");

c.setAutoCommit(false);
p.executeUpdate();
c.rollback();

ResultSet r = s.executeQuery("select count(*) from foo");
r.next();
System.out.println("We inserted " + r.getInt(1) );

I got:
C:\new_ms_driver>java foo
Driver version is 1.0.809.102
We inserted 0

.

Relevant Pages

  • Q: setAutoCommit after connection.createStatement()
    ... Presumption is that the connection from pool could have ... auto commit set to false or true since last invocation ... created statment above if the call to setAutoCommit ...
    (comp.lang.java.databases)
  • Re: Q: setAutoCommit after connection.createStatement()
    ... > auto commit set to false or true since last invocation ... The connection pool is keeping the ... autocommit state of the connection, ...
    (comp.lang.java.databases)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)