FW: setUserConnection() does not reset the connection used in SQLMAP

["Wang, Jin (Sensis)" <Ji...@sensis.com.au>]

Hi, Clinton and all the gurus

We try to use the global transaction manager to manage the transaction
for JMS and Oracle database transaction.
But we have problems in managing the Oracle transaction if we use IBATIS
SQLMAP.
BTW. we are not using any heavy application server (such as weblogic,
websphere, ...) and we just use
tyrex as our transaction manager and joram as our JMS server. The
transaction manager and JMS part works fine.

We create the OracleXADataSource as the following :
	oxds = new OracleXADataSource();
	oxds.setURL(dbURL);
	oxds.setUser(user);
	oxds.setPassword(pasword);

	//  Get the XAConnection.
	oxConn = oxds.getXAConnection();
	XAResouce xres = oxConn.getXAResource();

	final Context root = new MemoryContext(null);

	//  Create comp/userTransaction and comp/env/jdbc/jndiName
	Context ctx = root.createSubcontext( "comp" );
	ctx.bind( "userTransaction", userTrans );
	ctx.bind( "transactionManager", transMgr );
	ctx = ctx.createSubcontext( "env" );
	ctx = ctx.createSubcontext( "jdbc" );
	ctx.bind( "SfuTxDataSource", oxds);
	//  Associate the memory context with a new
	//  runtime context and associate the runtime context
	//  with the current thread.
	final RuntimeContext runCtx = RuntimeContext.newRuntimeContext(
root, null );
	RuntimeContext.setRuntimeContext( runCtx );


We did try the following two options :
	1. just use the SQLMAP's configuration sqlmapConfig.xml to tell
SQLMAP to look
	the data source created above via JNDI.
	Here is the configure file :

......
<sqlMapConfig>
  <properties resource="sfuInboundDatabase.properties"/>
  <settings
    cacheModelsEnabled="true"
    enhancementEnabled="true"
    maxSessions="64"
    maxTransactions="8"
    maxRequests="128"/>

  <transactionManager type="EXTERNAL" >
    <property name="DefaultAutoCommit" value="false"/>
    <property name="SetAutoCommitAllowed" value="false"/>
    <property name="commitRequired" value="true"/>
    <dataSource type="JNDI">
    	<property name="DataSource"
value="java:comp/env/jdbc/SfuTxDataSource"/>
    </dataSource>
  </transactionManager>
 
  <sqlMap resource="sfuInBoundReferenceDbSQL.xml"/>
  <sqlMap resource="sfuInBoundDbSQL.xml"/>
</sqlMapConfig>



	In this case, It always commits all the updates done to the
database. When we debug into the
	Sqlmap code, we found out in the init() function (called in the
getConnection() function)in
	class
com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction,
	it calls dataSource.getConnection(). Because this dataSource is
an instance of OracleXADataSource
	and the getConnection() function in OracleXADataSource returns a
default connection. This
	default connection has the autoCommit flag set to "true". which
means all the updates in the SQLMAP
	on this connection would be automatically commited. Ideally, it
should call OracleXADataSource's
	getXAConnection().getConnection() as the connection and allow
the outsider ( the code which use sqlmap)
	to access the XAConnection, then we can use the XAResource to
manage the transaction ourselves.

	2. call the setUserConnection() function to manually set the
connection. In this case, It still
	uses the connection configured from the sqlmapConfig.xml by
calling dataSource.getConnection() in
	the init() in class ExternalTransaction, and did not take the
connection we set via the setUserConnection().
	Here is what in the code (we use the same sqlmapConfig.xml file)
:
		XAConnection cx = oxds.getXAConnection();
		java.sql.Connection dbCon = cx.getConnection();
		sqlMap.setUserConnection(dbCon);
	
	When we trace the setUserConnection() method, we found out that,
at the end of day, it creates a new
	instance of transaction with class like
"UserProvidedTransaction". Somehow, this new transaction is
	not used but it is still uses the original ExternalTransaction
instance.
	If SQLMAP use the connection we set, we can manage the
transaction ourselves because this connection we
	get is from the XAConnection and with the XAConnection we can
get the XAResource, then we can use the
	TransactionManager to manage all the transactions.


We did try different ways, but it still does not work. For example,
remove the <dataSource> element from the sqlmapConfig.xml,
it cause the XML parse error. Use the SIMPLE data source type, it still
did not take the connection we manually
set via call setUserConnection().

We already exhausted, If you can show us some light to solve this
problem It would be very appreciated.



Jin Wang
Architecture
Directory Technology
AMDOCS > INTEGRATED CUSTOMER MANAGEMENT
Phone: +61-3-9835 0016 email: jwang@sensis.com.au

The information contained in this message is proprietary of DIT,
protected from disclosure, and may be privileged. The information is
intended to be conveyed only to the designated recipient/s of the
message. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, use, distribution or
copying of this communication is strictly prohibited and may be
unlawful. If you have received this communication in error, please
notify us immediately by replying to the message and deleting it from
your computer. Thank you.




www.about.sensis.com.au
A leading Australian advertising, information and directories business.
www.sensis.com.au
www.yellowpages.com.au
www.whitepages.com.au
www.citysearch.com.au
www.whereis.com.au
www.telstra.com.au
www.tradingpost.com.au
This email and any attachments are intended only for the use of the recipient and may be confidential and/or legally privileged.
Sensis Pty Ltd disclaims liability for any errors, omissions, viruses, loss and/or damage arising from using, opening or transmitting this email.
If you are not the intended recipient you must not use, interfere with, disclose, copy or retain this email and you should notify the sender immediately by return email or by contacting Sensis Pty Ltd by telephone on [+61 3 8653 5000]