1 2 Previous Next 17 Replies Latest reply on Feb 18, 2004 1:16 PM by nasser

    Help needed with Configuring for Multiple Datasources

    ericchang591 Newbie

      I posted this on the Datasources forum, but perhaps some of you here can also help me out as no one has replied to my original post.
      Thanks!
      ----------------------------------

      Hi, I'm attempting to configure JBoss 3.2.3 to recognize 3 databases...the packaged Hypersonic one and 2 Ingres databases. I currently have the default hsdqldb-ds.xml, as well as separate -ds.xml files for each Ingres database in the server/default/deploy directory.
      I have managed to start JBoss with no errors, however, it seems as if the application can only access the DefaultDS, which is the Hypersonic database. For example, one of the Ingres databases is used to hold User information, and if I change the JNDI name for it to DefaultDS, I can access that database, but none of the other 3. And the Hypersonic one handles session information, so if that is DefaultDS, I can generate a sessionId with no problem, but then I can't login! I have looked around, and have seen mention of adding things to the standardjbosscmp-jdbc.xml file as well as adding things to the login-config.xml file.
      If someone could please point me in the right direction in what I need to do so that the application will be able to "see" all three databases. Note that this is something being ported from JBoss 2.4.3 so pretty much everything is already in place and working, just the configuration of JBoss 3.2.3 needs to be done.
      Here is an example of what I have in the Ingres -ds.xml files (skipped the hsqldb-ds.xml one since I have no changed it in any way from the default configuration it came with the distribution...both Ingres files are very similar except for the jndi-name):

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


      <local-tx-datasource>
      <jndi-name>UserDB</jndi-name>
      <connection-url>jdbc:edbc://DATABASEINFO</connection-url>
      <user-name>USER</user-name>
      PASSWORD
      <driver-class>ca.edbc.jdbc.EdbcDriver</driver-class>
      <min-pool-size>0</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>1200000</idle-timeout-minutes>
      </local-tx-datasource>


      Thanks! Any help would be appreciated.
      Eric


      Joined: Wed Jan 28, 2004 10:07 AM
      Posts: ericchang591
      Location:
      Posted: Wed Jan 28, 2004 13:09 PM Post subject: Re: Multiple Datasources Configuration Help Needed

      --------------------------------------------------------------------------------

      "ericchang591" wrote:
      Oh, and I am NOT using any CMP, so I guess modifying the jbosscmp-jdbc.xml files would not be necessary?


        • 1. Re: Help needed with Configuring for Multiple Datasources
          Juha Lindfors Master

          How are you looking up your databases? What is the error you're getting?

          • 2. Re: Help needed with Configuring for Multiple Datasources
            ericchang591 Newbie

            Well, I'll attempt to explain it as clearly as I can, but I do not fully understand everything about the application server.
            Essentially, we have 3 databases, 2 of which are Ingres databases (1 for Adminstration data, and another for all the rest of the data), and the HyperSonic database which we use for generating and storing things like sessionIDs. Originally, with the older version of JBoss 2.x, we had the two Ingres databases bound to the JNDI names of UserDB and xxx, and the HyperSonic one stayed as DefaultDS.
            While porting this over to JBoss 3.2.x, I've made the various -ds.xml files, I had to update the jboss.xml file stored in the jar file packed in an ear file (it did not follow the dtd), and I created a jboss-web.xml in my war file (also packed in the ear file). Currently, the jboss-web.xml file only contains one entry:
            <?xml version="1.0" encoding="UTF-8"?>
            <jboss-web>
            <resource-ref>
            <res-ref-name>jdbc/UserDB</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <jndi-name>java:/Admin</jndi-name>
            </resource-ref>
            </jboss-web>

            to use the alias UserDB in place of the JNDI name Admin (so the server code can use UserDB regardless of what the name of the datasource is).

            Now, after making all these changes, I start the JBoss server and get no errors of any kind.
            When starting the client-side application, the very first thing that happens before one even logs on is a sessionId is generated and stored in the HyperSonic db (DefaultDS). This works fine, but then when hitting the login screen, the Admin db (UserDB) is queried to fill in a drop-down with potential users/roles, etc. This causes an error (which I will try to get...I have to change my environment to start the madness).
            Now, if I tinker around and make the Admin database bound to the name DefaultDS, I can get the login information filled in, but a sessionId is not generated. This leads me to believe that I'm missing something somewhere to allow JBoss to "see" multiple databases, as right now, it seems as if I'm only able to access the single DefaultDS.
            Also, the beans which access the HyperSonic DB use CMP, and the other beans which access the other DBs use BMP.
            Whew, that was a mouthful. If you need any other information, I can try to provide it. And again, thanks for the help!
            Eric

            • 3. Re: Help needed with Configuring for Multiple Datasources
              ericchang591 Newbie

              Essentially the error that I get is a Java SQLException thrown by our server code stating that the connection to the database cannot be made.

              • 4. Re: Help needed with Configuring for Multiple Datasources
                ericchang591 Newbie

                Here's the error on the server console:

                [DatabaseUtilities] SELECT DISTINCT ADM_UserInfo.username,ADM_UserInfo.primary_key,ADM_Role.name,ADM_Role.primary_key,ADM_Site.name,ADM_Site.primary_key FROM ADM_Site,ADM_Role,ADM_UserInfo,ADM_UserRoleSite,ADM_UserRoleSite_UserInfo,ADM_UserRoleSite_Role,ADM_UserRoleSite_Site WHERE ADM_UserRoleSite_UserInfo.ADM_UserInfo_fk = ADM_UserInfo.primary_key AND ADM_UserRoleSite_UserInfo.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key AND ADM_UserRoleSite_Role.ADM_Role_fk = ADM_Role.primary_key AND ADM_UserRoleSite_Role.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key AND ADM_UserRoleSite_Site.ADM_Site_fk = ADM_Site.primary_key AND ADM_UserRoleSite_Site.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key
                17:58:35,706 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
                java.lang.Exception: STACK TRACE
                at org.jboss.resource.adapter.jdbc.WrappedConnection.registerStatement(WrappedConnection.java:805)
                at org.jboss.resource.adapter.jdbc.WrappedStatement.(WrappedStatement.java:43)
                at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:142)
                at com.raytheon.adams.ejb.dataaccess.DatabaseUtilities.executeQuery(DatabaseUtilities.java:81)
                at com.raytheon.adams.ejb.dataaccess.multiuser.SessionManagerBean.getRoleSiteInfo(SessionManagerBean.java:155)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                at java.lang.reflect.Method.invoke(Method.java:324)
                at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683)
                at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
                at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
                at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
                at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
                at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
                at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
                at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
                at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
                at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
                at org.jboss.ejb.Container.invoke(Container.java:700)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                at java.lang.reflect.Method.invoke(Method.java:324)
                at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
                at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
                at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:90)
                at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
                at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
                at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:100)
                at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
                at $Proxy62.getRoleSiteInfo(Unknown Source)
                at org.apache.jsp.login_jsp._jspService(login_jsp.java:222)
                at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
                at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
                at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
                at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
                at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
                at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
                at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
                at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
                at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
                at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
                at java.lang.Thread.run(Thread.java:536)
                17:58:35,727 INFO [STDOUT] [ERROR IN LOGIN.JSP] Unhandled ADAMS error. 1014 Root Cause: java.sql.SQLException: Table not found: ADM_SITE in statement [SELECT DISTINCT ADM_UserInfo.username,ADM_UserInfo.primary_key,ADM_Role.name,ADM_Role.primary_key,ADM_Site.name,ADM_Site.primary_key FROM ADM_Site,ADM_Role,ADM_UserInfo,ADM_UserRoleSite,ADM_UserRoleSite_UserInfo,ADM_UserRoleSite_Role,ADM_UserRoleSite_Site WHERE ADM_UserRoleSite_UserInfo.ADM_UserInfo_fk = ADM_UserInfo.primary_key AND ADM_UserRoleSite_UserInfo.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key AND ADM_UserRoleSite_Role.ADM_Role_fk = ADM_Role.primary_key AND ADM_UserRoleSite_Role.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key AND ADM_UserRoleSite_Site.ADM_Site_fk = ADM_Site.primary_key AND ADM_UserRoleSite_Site.ADM_UserRoleSite_fk = ADM_UserRoleSite.primary_key]



                • 5. Re: Help needed with Configuring for Multiple Datasources
                  Juha Lindfors Master

                  Hmm so it looks like it is trying to connect to the Ingres database, as you are getting an SQL Exception from the ADM database... or is this only after you've bound Ingres to DefaultDS?

                  If yes, then I suspect there's an issue with your deployment descriptors, the datasources are not referenced correctly so JBoss falls back trying to connect to DefaultDS.

                  • 6. Re: Help needed with Configuring for Multiple Datasources
                    ericchang591 Newbie

                    The error that I posted above is when I have the HyperSonic db bound to DefaultDS, and then it attempts to connect to the Admin database (ADM_ tables), which causes that error.
                    And if I end up binding the Admin database to DefaultDS, I don't get that error, but I get another one with trying to connect to the HyperSonic db to generate a sessionId.
                    So all-in-all, I'm not positive how I should proceed. I am probably going to tinker around with the login xml file on Monday, but if there is any other files I should look at (standardjaws.xml?), please let me know.
                    Thanks,
                    Eric

                    • 7. More information to those that can help...
                      ericchang591 Newbie

                      Since I'm trying to port from JBoss 2.4.3/Tomcat 3.2.3 to JBoss 3.2.3, I figured what might help you guys figure out what I'm doing wrong is show you what I've got from the old install of JBoss.
                      In my jboss.jcml file, I had for each datasource, something like this:


                      Admin
                      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
                      jdbc:edbc://v3102:II7/Admin
                      XXX
                      XXX

                      1200000
                      10
                      false
                      false
                      false
                      true
                      120000
                      1800000
                      false
                      false
                      1.0
                      0


                      Now, the thing is that the

                      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

                      part, I'm not sure where that goes in the new JBoss 3.2.3.

                      Any ideas?

                      • 8. Doh...let me try that again...
                        ericchang591 Newbie


                        Admin
                        org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
                        jdbc:edbc://v3102:II7/Admin
                        dms
                        Groundho

                        1200000
                        10
                        false
                        false
                        false
                        true
                        120000
                        1800000
                        false
                        false
                        1.0
                        0

                        • 9. Re: Help needed with Configuring for Multiple Datasources
                          ericchang591 Newbie

                          Ah, I now see the use of the Preview button:

                          (mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=Admin")
                          (attribute name="PoolName")Admin(/attribute)
                          (attribute name="DataSourceClass")org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl(/attribute)
                          (attribute name="URL")jdbc:edbc://v3102:II7/Admin(/attribute)
                          (attribute name="JDBCUser")dms(/attribute)
                          (attribute name="Password")Groundho(/attribute)
                          (attribute name="Properties")(/attribute)
                          (attribute name="GCMinIdleTime")1200000(/attribute)
                          (attribute name="MaxSize")10(/attribute)
                          (attribute name="GCEnabled")false(/attribute)
                          (attribute name="InvalidateOnError")false(/attribute)
                          (attribute name="TimestampUsed")false(/attribute)
                          (attribute name="Blocking")true(/attribute)
                          (attribute name="GCInterval")120000(/attribute)
                          (attribute name="IdleTimeout")1800000(/attribute)
                          (attribute name="IdleTimeoutEnabled")false(/attribute)
                          (attribute name="LoggingEnabled")false(/attribute)
                          (attribute name="MaxIdleTimeoutPercent")1.0(/attribute)
                          (attribute name="MinSize")0(/attribute)
                          (/mbean)

                          • 10. Re: Help needed with Configuring for Multiple Datasources
                            Juha Lindfors Master

                            These should be new -ds.xml files in 3.2.3, have you deployed all three datasources via the -ds.xml files?

                            • 11. Re: Help needed with Configuring for Multiple Datasources
                              ericchang591 Newbie

                              Yes, I've got the original hsql-ds.xml with slight modifications, and also a -ds.xml file for each Ingres database.
                              Each one looks like this:

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


                              <local-tx-datasource>
                              <jndi-name>Admin</jndi-name>
                              <connection-url>jdbc:edbc://v3102:II7/Admin</connection-url>

                              <!-- user information -->
                              <user-name>User</user-name>
                              Password

                              <!-- The driver class -->
                              <driver-class>ca.edbc.jdbc.EdbcDriver</driver-class>

                              <min-pool-size>0</min-pool-size>
                              <max-pool-size>20</max-pool-size>
                              <idle-timeout-minutes>1200000</idle-timeout-minutes>
                              <security-domain>AdminRealm</security-domain>
                              </local-tx-datasource>



                              but with obvious differences in names, etc. And for each Ingres database, I now have an entry in the login-config.xml file similar to this:

                              <application-policy name = "AdminRealm">

                              <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
                              flag = "required">
                              <module-option name = "principal">Name</module-option>
                              <module-option name = "userName">Name</module-option>
                              <module-option name = "password">Password</module-option>
                              <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=Admin</module-option>
                              </login-module>

                              </application-policy>

                              ...still no luck though in getting it to work.

                              • 12. Re: Help needed with Configuring for Multiple Datasources
                                Juha Lindfors Master

                                Go to your JNDI-view MBean, what do you see your java:comp/env/jdbc/UserDB being linked to?

                                • 13. Re: Help needed with Configuring for Multiple Datasources
                                  ericchang591 Newbie

                                  Exactly what do you mean by looking an the JNDI-View MBean? I assumed it was in the jmx-console, but I see nothing that tells me what JNDI names are bound to.

                                  • 14. Re: Help needed with Configuring for Multiple Datasources
                                    Juha Lindfors Master

                                    Search for the JNDIView MBean and click on its list() operation, this will give you the content of the name service, including what bindings have been made for your EJBs. One of them should be a link from the java:comp/env/jdbc/somename to a datasource in java:/DataSourceName. Check that the bindings look correctly (and that for instance not all the bean private names get configured to point to java:/DefaultDS)

                                    1 2 Previous Next