[jboss-user] [Datasource Configuration] - How to configure connection pool for two databases??

[hiren kesariya]

hiren kesariya [https://community.jboss.org/people/jk.hiren] created the discussion

"How to configure connection pool for two databases??"

To view the discussion, visit: https://community.jboss.org/message/758931#758931

--------------------------------------------------------------
I am trying to make a connection pool for two databases "oracle" and "MS Sql Server 2005".
Can anybody please help with the -ds.xml file for the same.

I tried adding two <xa-datasource> each for one databse in the same file wothout any success.
i also tried creating two differnet -ds,xml files, but found no success.

below is the -ds.xml for oracle : 

*oraclePool-xa-ds.xml*


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


<!-- ===================================================================== -->
<!--                                                                       -->
<!--  JBoss Server Configuration                                           -->
<!--                                                                       -->
<!-- ===================================================================== -->


<!-- $Id: oracle-xa-ds.xml 77479 2008-08-26 10:33:09Z  mailto:alex.loubyansky at jboss.com alex.loubyansky at jboss.com $ -->


<!-- ===================================================================== -->
<!-- ATTENTION:  DO NOT FORGET TO SET Pad=true IN transaction-service.xml  -->
<!-- ===================================================================== -->


<datasources>
  <xa-datasource>
    <jndi-name>test</jndi-name>
    <!-- uncomment to enable interleaving <interleaving/> -->
    <isSameRM-override-value>false</isSameRM-override-value>
    <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
    <xa-datasource-property name="URL">jdbc:oracle:thin:@192.128.2.2:1521:weblogic</xa-datasource-property>
    <xa-datasource-property name="User">help</xa-datasource-property>
    <xa-datasource-property name="Password">help</xa-datasource-property>

 <!--Pooling Paramters -->


                    <min-pool-size>5</min-pool-size> 
  <max-pool-size>20</max-pool-size> 
  <blocking-timeout-millis>5000</blocking-timeout-millis> 
  <idle-timeout-minutes>15</idle-timeout-minutes> 




    <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
    <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
    <!-- Checks the Oracle error codes and messages for fatal errors -->
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
    <!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa -->
    <no-tx-separate-pools/>


      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>Oracle11g</type-mapping>
      </metadata>
  </xa-datasource>


  <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter" 
         name="jboss.jca:service=OracleXAExceptionFormatter">
    <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends>
  </mbean>


</datasources>



and the other one for sql server 2005 :

*sqlPool-xa-ds.xml*



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


<!-- ===================================================================== -->
<!--                                                                       -->
<!--  JBoss Server Configuration                                           -->
<!--                                                                       -->
<!-- ===================================================================== -->


<!-- $Id: mssql-xa-ds.xml 77479 2008-08-26 10:33:09Z  mailto:alex.loubyansky at jboss.com alex.loubyansky at jboss.com $ -->
  <!-- ==================================================================== -->
  <!-- ConnectionManager setup for xa Microsoft SQL Server 2005, using      -->
  <!-- Microsoft's JDBC driver.                                             -->
  <!-- Thanks to Benjamin Geer  < mailto:benjamin.geer at misys.com benjamin.geer at misys.com>                   -->
  <!-- Be sure to set the JndiName property to the name you want to look up -->
  <!-- the datasource under and set the location of your database in        -->
  <!-- the xa-datasource-property section.                                  -->
  <!-- Further information about the Microsoft JDBC Driver version 1.1      -->
  <!-- can be found here:                                                   -->
  <!--  http://msdn2.microsoft.com/en-us/library/aa496082.aspx http://msdn2.microsoft.com/en-us/library/aa496082.aspx               -->
  <!-- ==================================================================== -->




<datasources>
  <xa-datasource>
    <jndi-name>MSSQLDS</jndi-name>
    <connection-url>
     jdbc:sqlserver://hello-welcome\\MSSQL2008R2;DatabaseName=ramos
   </connection-url>
    <driver-class>
     com.microsoft.sqlserver.jdbc.SQLServerDriver
    </driver-class>
    <user-name>help</user-name>
    <password>help</password>
     <!--Pooling Paramters -->


                    <min-pool-size>5</min-pool-size> 
  <max-pool-size>20</max-pool-size> 
  <blocking-timeout-millis>5000</blocking-timeout-millis> 
  <idle-timeout-minutes>15</idle-timeout-minutes> 


        <metadata>
            <type-mapping>MS SQLSERVER2000</type-mapping>
        </metadata>
  </xa-datasource>


</datasources>




*NOTE: i am bale to create the oracle pool usng* 
*oraclePool-xa-ds.xml file alone but as soon as i add the other file* 
*sqlPool-xa-ds.xml, i get "org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: No XADataSourceClass supplied!)"exception.*


*please help..!!!!!!!*
*(please also give some tips to write -ds.xml file for sql server 2005)
*
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/758931#758931]

Start a new discussion in Datasource Configuration at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20120911/f9c72192/attachment-0001.html