9 Replies Latest reply on Sep 11, 2012 11:49 AM by jk.hiren

    How to configure connection pool for two databases??

    jk.hiren

      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 alex.loubyansky@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 alex.loubyansky@jboss.com $ -->

        <!-- ==================================================================== -->

        <!-- ConnectionManager setup for xa Microsoft SQL Server 2005, using      -->

        <!-- Microsoft's JDBC driver.                                             -->

        <!-- Thanks to Benjamin Geer  <benjamin.geer@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               -->

        <!-- ==================================================================== -->

       

       

       

       

      <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)

        • 1. Re: How to configure connection pool for two databases??
          wdfink

          Looks like that you use a wrong example. It seems a non xa datasource with changed element name.

           

          See the docs/examples/jca/mssql-xa-ds.xml in your JBOSS directory.

          • 2. Re: How to configure connection pool for two databases??
            jk.hiren

            I have seen the example mentioned by you, but have not been able to implement the same.

            The same exception "org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: No XADataSourceClass supplied!)"exception. is thrown.


            Can you please provide some sample code.

             

            Also, which is the right approach?
            1)creating two different -ds.xml files(one for oracle and one for sql server 2005 each)
            2)creating one file and and having two <xa-datasource> tags in it.

             

            Thanx for your time.

            • 3. Re: How to configure connection pool for two databases??
              wdfink

              I prefer a single file for each DS, but the other approach is also possible.

              If you need a XA datasource you have to use a DS file similar to the example.

               

              It looks to me that you did not define the <xa-datasource-class> element or the give class is not in the lib folder of your installation.

              • 4. Re: How to configure connection pool for two databases??
                jk.hiren

                I have added ojdbc14.jar and sqljdbc.jar in the lib folder at the location jBoss/server/default/lib.

                 

                Do i need anything else??

                which example to use from the examples given in Jboss docs.

                mssql-xa-ds.xml or msql-ds.xml

                 

                 

                Thanx.

                • 5. Re: How to configure connection pool for two databases??
                  wdfink

                  If you need XA you should use the mssql-xa-ds.xml.

                  In my example the class is <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>. Is this class packed in the mssql jar?

                  BTW what JBoss version do you use?

                  • 6. Re: How to configure connection pool for two databases??
                    jk.hiren

                    Yes, the class com.microsoft.sqlserver.jdbc.SQLServerXADataSource is packed in the jar file.

                    I am using "jboss-5.1.0.GA"

                     

                    I went as per your instructions and added

                    <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>.  in the -xa-ds.xml file for sql server 2005,
                    but now I am getting

                     

                    "org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.util.regex.PatternSyntaxException: Illegal/unsupported escape sequence near index 1\MSSQL2008R2 ^)"

                    as my server name in url is     <xa-datasource-property name="ServerName">hello-welcome\\MSSQL2008R2</xa-datasource-property>

                     

                     

                    please help with this.

                    Thanx.

                    • 7. Re: How to configure connection pool for two databases??
                      wdfink

                      My example looks like this:

                       

                      <datasources>

                        <xa-datasource>

                          <jndi-name>MSSQLXADS</jndi-name>

                          <!-- uncomment to enable interleaving <interleaving/> -->

                          <isSameRM-override-value>false</isSameRM-override-value>

                          <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                          <xa-datasource-property name="ServerName">myserver</xa-datasource-property>

                          <xa-datasource-property name="DatabaseName">mydatabase</xa-datasource-property>

                          <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>

                          <xa-datasource-property name="User">myuser</xa-datasource-property>

                          <xa-datasource-property name="Password">mypassword</xa-datasource-property>

                       

                            <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

                            <metadata>

                               <type-mapping>MS SQLSERVER2000</type-mapping>

                            </metadata>

                        </xa-datasource>

                      </datasources>

                       

                      Maybe you have some hidden sequences?

                      Or the server name could not contain \\, try / instead.

                      I don't know the exact syntax here

                       

                      Maybe this wiki helps

                      • 8. Re: How to configure connection pool for two databases??
                        jk.hiren

                        I tried changing "\\" to "/" and "//",

                        but still no luck.

                         

                        I am getting     " [JBossManagedConnectionPool] Throwable while attempting to get a new connection: null

                        org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host  has failed. java.net.UnknownHostException: )" exception.

                         

                        Is this by any chance because of not passing server port.???

                        • 9. Re: How to configure connection pool for two databases??
                          jk.hiren

                          The Issue is fixed.
                          I passed the server name as

                          <connection-url>

                              jdbc:sqlserver://hello-welcome\MSSQL2008R2;DatabaseName=ramos

                             </connection-url>

                           

                           

                          Thank you so much for helping me out.