Re: Driver AutoCommit issue


Arun wrote:

Joe,
Thank you for you quick reply

Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed.

I am not using the driver from a stand alone application, I am using the driver from within weblogic.
I have configured the datasource in weblogic config.xml using the following configuration

<JDBCConnectionPool
DriverName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
Name="myPool"
PasswordEncrypted="{3DES}ifMhpj12kfwKJ+1BteiLZg=="
Properties="instanceName=SQL Server 2000;user=nemoadap;url=jdbc:sqlserver://my_db_host_name:1433;userName=user;databaseName=mydb;serverName=mydb
Targets="admin" TestTableName="SQL SELECT 1" URL="jdbc:sqlserver://my_db_host_name:1433"/>

<JDBCDataSource JNDIName="myDatasource"
Name="myDatasource" PoolName="myPool" Targets="admin"/>

Ok, Using it in weblogic shouldn't matter. You're not using a
tx-aware WebLogic DataSource so we aren't including your
JDBC in any WebLogic/EJB transaction. If you want us to control
it you need a TxDataSource. When in the code do you do a commit?
Is the connection you're using shared, so maybe some other
thread is committing it before you're done here?
Joe Weinstein at BEA Systems




This the output i am getting from the database metadata.
2006-05-04 12:53:25,089 DEBUG dao.Dao: =========>>>>>>>>>> Driver version is 3.40.19 (012727.007216.008716)
2006-05-04 12:53:25,089 DEBUG dao.Dao: =========>>>>>>>>>> Database major version is 8
2006-05-04 12:53:25,089 DEBUG dao.Dao: =========>>>>>>>>>> Database minor is 0
2006-05-04 12:53:25,089 DEBUG dao.Dao: =========>>>>>>>>>> Database ProductVersion is Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


"Joe Weinstein" wrote:



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

  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Driver AutoCommit issue
    ... Why I am saying the DML always gets committed is when I step thought the ... This the output i am getting from the database metadata. ... I set the auto commit to false ... on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: [PHP] A no brainer...
    ... that once you commit, the data is there no matter what. ... Unless session storage is used to save time in retrieving data, ... I think it would be kind of fun to run some actual tests. ... it makes no sense for a database to be faster than ...
    (php.general)
  • Re: commit_write= BATCH, NOWAIT; ... how long do I wait?
    ... we've started configuring the database servers with: ... minutes or so worth of transactions, so we're not deeply concerned by ... the durability loss associated with running in asynchronous commit ... Does anybody know how long Oracle will buffer redo in memory before it ...
    (comp.databases.oracle.server)
  • Re: using sqlite3 - execute vs. executemany; committing ...
    ... and also the proper commiting the transactions and closing the connection. ... If the database supports transactions then cursors automatically use ... I suggest that you use the standard cursor methods instead, ... always use .commit() and .close. ...
    (comp.lang.python)
Loading