Multiple database JNDI calls in single EJB

[anacarda]

Hi,

I have some code in an EJB that I want to open up two different database connections (using JNDI) to two different datasources... so...

Context loContext = (Context) new InitialContext().lookup("java:");
DataSource loDataSource1 = (DataSource)loContext.lookup("Prophet_Data");
Connection loConnection1 = loDataSource1.getConnection();
try {
 DataSource loDataSource2 = (DataSource)loContext.lookup("Prophet_Cube");
 // Issue when this getConnection() is called
 Connection loConnection2 = loDataSource2.getConnection();
 try {
 //
 } finally {
 if (!(loConnection2 == null)) {
 loConnection2.close();
 }
 }
} finally {
 if (!(loConnection1 == null)) {
 loConnection1.close();
 }
}

When I call loDataSource2.getConnection(), the following exception is raised:

11:21:35,475 WARN [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.lastResource.disallow] [com.arjuna.ats.internal.jta.transaction.arjunacore.lastResource.disallow] Adding multiple last resources is disallowed. Current resource is org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@1b6ac25
11:21:35,476 ERROR [STDERR] org.jboss.util.NestedSQLException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: a090101:c463:47658705:2467 status: ActionStatus.ABORT_ONLY >); - nested throwable: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: a090101:c463:47658705:2467 status: ActionStatus.ABORT_ONLY >))
11:21:35,477 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:94)
11:21:35,478 ERROR [STDERR] at nz.co.mcpond.test.ejb.helloejb.HelloEJB.getText(HelloEJB.java:23)
11:21:35,478 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
11:21:35,478 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

If I set com.arjuna.ats.jta.allowMultipleLastResources to true, within jbossjta-properties.xml, then I can use the second connection, however, I have read that this is not really safe to do so.

So, I am just wondering, what is the best way to use two seperate database connections within the same EJB (using JNDI)?

I am using two seperate database connections, because I want to read data from both databases (and update both databases) depending on values from each table within each database...

Antonio Broughton

[anacarda]

Just wondering, is it better to split my EJB into 3 seperate EJBs?

ProphetDataEJB - Uses Prophet_Data database, returns information required / allows updates
ProphetCubeEJB - Uses Prophet_Cube database, returns information required / allows updates
ProphetEJB - uses the returned information from the above EJBs, and calls update methods within each of the above EJBs

Is it better to do it that way?

[jaikiran]

Have you configured your datasources (in the -ds.xml file) to be XA?

[anacarda]

I changed the datasources to be no-tx-datasource, they were local-tx-datasource.

Seems to work fine now...

[jaikiran]

You know the implication of changing it to no-tx-datasource, right?

[anacarda]

Yep, it doesn't allow JTA transactions.

This is fine I think.

The datasources are defined within Pentaho, I am just reading about if they use JTA transactions or not.