Accessing Multiple Datasources

[gkelly]

So my problem occurs when trying to access multiple datasources in the same action class. I get the following error at the execution of the query for the second datasource.

WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: null
ERROR [JDBCExceptionReporter] Could not enlist in transaction on entering meta-aware object

Here is some sample code:

@In (create = true)
private EntityManager source1EntityManager;
     
@In (create = true)
private EntityManager source2EntityManager;

private void init() {
   String item = "test";

   List<MyBean1> myBean1List = source1EntityManager.createQuery(
      "from MyBean1")
      .getResultList();

   List<MyBean2> myBean2List = source2EntityManager.createQuery(
      "from MyBean2 where item = :item")
      .setParameter("item", item)
      .getResultList();

   //Collect info for processing
}

Here is my project-dev-ds.xml:

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
   <local-tx-datasource>
      <jndi-name>source1</jndi-name>    
      <connection-url>jdbc:oracle:thin:@server1.company.com:1521:scdev</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>username</user-name>
      <password>password</password>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <blocking-timeout-millis>120000</blocking-timeout-millis>
      <eager-release>false</eager-release>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <metadata>
         <type-mapping>Oracle9i</type-mapping>
      </metadata>
   </local-tx-datasource>

   <local-tx-datasource>
      <jndi-name>source2</jndi-name>    
      <connection-url>jdbc:oracle:thin:@server2.company.com:1521:gldev</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>username1</user-name>
      <password>password1</password>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <blocking-timeout-millis>120000</blocking-timeout-millis>
      <eager-release>false</eager-release>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <metadata>
         <type-mapping>Oracle9i</type-mapping>
      </metadata>
   </local-tx-datasource>
</datasources>

Here is my components.xml:

...
<persistence:managed-persistence-context name="source1EntityManager" auto-create="true" persistence-unit-jndi-name="java:/source1EntityManagerFactory"/>                          
   
<persistence:managed-persistence-context name="source2EntityManager" auto-create="true"
persistence-unit-jndi-name="java:/source2EntityManagerFactory"/>
...

Anything else needed to help, I'll be more than happy to supply.

Thanks in advance!

[asookazian]

you need to use <xa-datasource>, you are engaging in a 2PC/XA distributed tx when there is more than one resource mgr (in this case more than one db) managed by the tx mgr.

Read my recent posts about XA/2PC in this forum for more details.

[gkelly]

Thank you so much for your help! Your solution worked perfectly.

Here is what I ended up with:

<xa-datasource>
   <jndi-name>source2</jndi-name>
   <track-connection-by-tx>true</track-connection-by-tx>
   <isSameRM-override-value>false</isSameRM-override-value>
   <managedconnectionfactory-class>
      org.jboss.resource.adapter.jdbc.xa.oracle.XAOracleManagedConnectionFactory
   </managedconnectionfactory-class>
   <transaction-isolation></transaction-isolation>
   <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
   <xa-datasource-property name="URL">jdbc:oracle:thin:@server2.company.com:1521:gldev</xa-datasource-property>
   <xa-datasource-property name="User">username1</xa-datasource-property>
   <xa-datasource-property name="Password">password1</xa-datasource-property>
   <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</xa-datasource>

Again, thank you very much!