2 Replies Latest reply on Aug 21, 2009 9:18 PM by Greg Kelly

    Accessing Multiple Datasources

    Greg Kelly Newbie

      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!

        • 1. Re: Accessing Multiple Datasources
          Arbi Sookazian Master
          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.
          • 2. Re: Accessing Multiple Datasources
            Greg Kelly Newbie

            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!