Re: Driver AutoCommit issue

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.


"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: 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)
  • 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)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)