Re: Driver AutoCommit issue

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"/>



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 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 have configured the datasource in weblogic config.xml using the following configuration ... 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