Re: Driver AutoCommit issue



Arun wrote:

Joe
I am not calling the connection.commit(). 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.

Well, I would suggest using the com.microsoft.sqlserver.jdbc.SQLServerXADataSource,
but the main issue is that you should never call setAutoCommit() or any other
transacitonal stuff if you want the container to be doing that stuff, and
crucially, your DataSource for accessing the pool has to be a TxDataSource
so we know to include your JDBC in the tx if any.
However, even so, you should ater your code like mine, to try doing
a rollback immediately after calling the procedure, and then do a query
to see if the change happened or not. And I do suspect that your DAO code
may not be thread-safe for use in WebLogic.
Joe



"Joe Weinstein" wrote:


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: SQLCE 3.0 losing data after transaction
    ... You can also control the flush scheduling time using Connection String ... only SqlCeResultSet to get data from the database. ... CommandType.Text) As SqlCeCommand ... If Transaction IsNot Nothing Then ...
    (microsoft.public.sqlserver.ce)
  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: psycopg, transactions and multiple cursors
    ... > first time a change is made to the database. ... My intention is actually to keep them in the same transaction. ... >> cursor fetches one record from the database at a time). ... > generated from the same connection, ...
    (comp.lang.python)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, ... the connection pool was getting created. ... When in the code do you do a commit? ...
    (microsoft.public.sqlserver.jdbcdriver)