Re: Driver AutoCommit issue



Arun wrote:

Joe
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.e. Since I will be accessing a production system I will not be able to install additional DLLs. I tried without installing the patch got some missing stored proc error when the connection pool was getting created. I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. But this time I got a different exception. This time the exception is because I tried to use two connection pools in my application one to connect to MS Sql server and another to connect to Oracle. If I don't use a XA driver I get "Connection has already been created in this tx context for pool" exception and if I use the XA driver for SQL Server without installing the xa_install.sql I get missing stored proc error. Is there a way to use the non XA SQL server ConnectionPool along with the Oracle Non XA Connection pool.

If you want all JDBC to more than one DBMS to be included in a single tx, at most
one JDBC connection can be non-XA. I suggest doing the oracle pool as XA because
it works without changing the DBMS in any way, and the MS one as non-XA, but it
has to be transactional.
Joe






"Joe Weinstein" wrote:



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: 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: 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)
  • Re: Driver AutoCommit issue
    ... I am not using the driver from a stand alone application, ... JDBC in any WebLogic/EJB transaction. ... When in the code do you do a commit? ... Is the connection you're using shared, ...
    (microsoft.public.sqlserver.jdbcdriver)