• Post Reply Bookmark Topic Watch Topic
  • New Topic

2 databases access with EJB ?

 
Franck Tranchant
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all
I'm working with Weblogic 7 and i would need to connect to 2 Databases (Oracle) from within the same transaction.
I have created 2 differents Tx DataSources based on 2 differents Connection Pools (one for each DataBase).
When I try to use the second DataSource in my container managed transaction, it goes like this :

Any suggestion about how to successfully achieve this kind of operation ?
Thanx in advance
[ October 17, 2003: Message edited by: Franck Tranchant ]
 
Pradeep bhatt
Ranch Hand
Posts: 8933
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How have you started the tx?
 
Franck Tranchant
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hum, what do you mean by started ?
Here is the how I get the connections :

First lookup for the first TxDataSource = TxDataSource1
Second lookup = TxDataSource2
Right after the second ds.getConnection() the exception occurs :
"Connection has already been created in this tx context..."
 
walter wu
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you proberbly have to do work around this, create 2 tx object for 2 datasource, and then get connection from each tx object, then commit or roll back both transaction according to the exception you get.
 
Franck Tranchant
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No so easy Walter..
The exception is raised right after the getConnection() of the second txDataSource.
So I cannot make any access to the second DataBase.
 
walter wu
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why do you want to continuing acces 2nd db if you caught exception in the first db?
if you have to to that, what you can do is just catch the exception when
you access the firt db, process it and continue to access 2nd db with new
tx and connection. Isn't that working for u?
 
Pradeep bhatt
Ranch Hand
Posts: 8933
Firefox Browser Java Spring
 
walter wu
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can find this info in FAQ from WebLogic home:
http://edocs.bea.com/wls/docs61/faq/JTA.html
Can I use more than one non-XA connection pool in distributed transactions?
No. Even if you set EnableTwoPhaseCommit=true for both TxDataSources of the connection pools, attempting to use two non-XA connection pools in the same distributed transaction will result in:
"java.sql.SQLException: Connection has already been created in this tx context for pool named <first pool's name>. Illegal attempt to create connection from another pool: <second pool's name>"
when you attempt to get the connection from the second non-XA connection pool.
 
walter wu
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you didn't quite get my response, you need two different Context object and 2 datasource object: Some thing like this.

Context ctx1 = null;
Context ctx2 = null;
Hashtable ht = new Hashtable();
Connection conn1 = null;
Connection conn2 = null;
ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL, "t3://127.0.0.1:7001");
try{
ctx1 = new InitialContext(ht);
ctx2 = new InitialContext(ht);
javax.sql.DataSource ds1 =
(javax.sql.DataSource)ctx1.lookup("TxDataSource1");
javax.sql.DataSource ds2 =
(javax.sql.DataSource)ctx2.lookup("TxDataSource2");
try {
conn1 = ds1.getConnection();
conn2 = ds2.getConnection();
}
catch(SQLException e)
{
<..>
}
}
catch (NamingException e){ <..>}
finally{
try {
ctx1.close();
ctx2.close();
}
catch (Exception e)
{ <..> }<..>

You'd have to be very careful though in case one transaction failed while the other commited. you have to commit or rollback the both transactions.
 
Franck Tranchant
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Walter,
Well, that's exactly what I'm doing since I'm calling my sample of code twice with the txDataSource JNDI name as parameter !
I guess I'm forced to use Distributed Transactions with javax.transaction.UserTransaction etc..
Too bad... Container handled transactions were nice..
 
Lu Battist
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can I use more than one non-XA connection pool in distributed transactions?

No you can't. Only one of the connections can be non-XA complaint for a distributed transaction.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!