Re: Driver AutoCommit issue

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.





"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, 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)
  • 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)