7 Replies Latest reply on Oct 19, 2006 6:32 PM by amitabh prasad

    configuring db2 data source

    amitabh prasad Newbie

      Hi,

      I am trying to configure data source for the application running on JBOSS 4.0 , I followed all the steps as detaild on in this links.
      http://www.onjava.com/pub/a/onjava/2004/02/25/jbossjdbc.html

      I also see the data source in the JNDI tree, but at runtime the DS is tied to default hypersonic data source, as such all my query are failing with following exceptions

      Caused by: java.sql.SQLException: invalid schema name: AS in statement [SELECT discriminator, key, value FROM AS.Control]
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:169).
      This stack trace clearly tells that its using hypersonic data base.

      Not sure if I am missing any steps.

      Thanks in advance

      -Amitabh

        • 1. Re: configuring db2 data source
          amitabh prasad Newbie

          To add some more info at the end I do see some messages like
          2006-10-19 00:43:29,140 DEBUG [org.jboss.resource.adapter.jdbc.remote.WrapperDataSourceService] Started jboss.jca:name=jdbc/abrakadabra,service=DataSourceBinding
          2006-10-19 00:43:29,140 DEBUG [org.jboss.system.ServiceController] Starting dependent components for: jboss.jca:name=jdbc/abrakadabra,service=DataSourceBinding dependent components: []
          2006-10-19 00:43:29,140 DEBUG [org.jboss.system.ServiceController] starting service jboss.jca:name=jdbc/abrakadabra,service=DataSourceBinding
          2006-10-19 00:43:29,140 DEBUG [org.jboss.system.ServiceController] Ignoring start request for service: jboss.jca:name=jdbc/abrakadabra,service=DataSourceBinding
          2006-10-19 00:43:45,531 DEBUG [org.jboss.ejb.StatelessSessionContainer] Binding resource manager: jdbc/abrakadabra to JDNI ENC as: java:/DefaultDS
          2006-10-19 00:43:45,531 DEBUG [org.jboss.ejb.StatelessSessionContainer] Binding resource manager: jdbc/abrakadabra to JDNI ENC as: java:/DefaultDS
          2006-10-19 00:43:45,531 DEBUG [org.jboss.ejb.StatelessSessionContainer] Binding resource manager: jdbc/abrakadabra to JDNI ENC as: java:/DefaultDS

          • 2. Re: configuring db2 data source
            Peter Johnson Master

            Typically the "FROM AS.Control" means that you are querying a table named Control in a database named AS. Only you know the database name and table name that you want. So, fix your select statement. (Hint: you might try using just "FROM Control")

            • 3. Re: configuring db2 data source
              amitabh prasad Newbie

              Thats right, its supposed to read from AS table from DB2 database but for some reason the JNDI is still binding with default hypersonic database

              • 4. Re: configuring db2 data source
                Peter Johnson Master

                You never said you were using DB2. Your post mentioned only Hypersonic so I assumed that is what you were using.

                Do you have a *-ds.xml file for the DB2 database? What
                is the jndi-name for the data source? If you set it to DefaultDS, then you should remove hsqldb-ds.xml. If it is some other name, then your application is using the wrong name. (My preference is to use another name and leave DefaultDS pointing Hypersonic.)

                • 5. Re: configuring db2 data source
                  amitabh prasad Newbie

                  Here is what I have in DB2-ds.xml file located at \jboss4.0*\server\default\deploy

                  <local-tx-datasource>
                  <jndi-name>jdbc/abrakadabra</jndi-name>
                  <connection-url>jdbc:db2://blueh006.rchland.ibm.com:60068/appvvdev</connection-url>
                  <driver-class>COM.ibm.db2.jdbc.app.DB2Driver</driver-class>
                  <user-name>bhcdev</user-name>
                  tmp23tmp
                  <min-pool-size>1</min-pool-size>
                  <!-- sql to call when connection is created -->
                  <new-connection-sql>select count(*) from as.cache</new-connection-sql>
                  <!-- sql to call on an existing pooled connection when it is obtained from pool -->
                  <check-valid-connection-sql>select count(*) from as.cache</check-valid-connection-sql>
                  </local-tx-datasource>


                  Corresponding entry in ejb-jar.xml is

                  <![CDATA[ExpireASPrices Bean]]>
                  <ejb-name>ExpireASPrices</ejb-name>
                  <local-home>products.hardware.configurator.as.emergencyfix.ExpireASPricesLocalHome</local-home>
                  products.hardware.configurator.as.emergencyfix.ExpireASPricesLocal
                  <ejb-class>products.hardware.configurator.as.emergencyfix.ExpireASPricesBean</ejb-class>
                  <session-type>Stateless</session-type>
                  <transaction-type>Bean</transaction-type>
                  <resource-ref>

                  <res-ref-name>jdbc/abrakadabra</res-ref-name>
                  <res-type>javax.sql.DataSource</res-type>
                  <res-auth>Container</res-auth>
                  <res-sharing-scope>Shareable</res-sharing-scope>
                  </resource-ref>

                  in jboss.xml file

                  <ejb-name>ExpireASPrices</ejb-name>
                  <local-jndi-name>ejb/ExpireASPrices</local-jndi-name>
                  <resource-ref>
                  <res-ref-name>jdbc/abrakadabra</res-ref-name>
                  <jndi-name>java:/abrakadabra</jndi-name>
                  </resource-ref>


                  and referred in code as
                  ctx = (ctx == null) ? new javax.naming.InitialContext() : ctx;
                  ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/abrakadabra"); //$NON-NLS-1$

                  Let me know if I am missing any thing. This app works fine in WAS, you must have realized by now I am newbiee to JBOSS and carrying out necessary changes for porting app to run on JBOSS

                  Thanks again

                  -Amitabh

                  • 6. Re: configuring db2 data source
                    amitabh prasad Newbie

                    also I don't understand why does it say

                    Binding resource manager: jdbc/abrakadabra to JDNI ENC as: java:/DefaultDS

                    • 7. Re: configuring db2 data source
                      amitabh prasad Newbie

                      Finally I got it working, got help from one of my colleague and it was configuration problem well got confused with where to use java:/<ds-name> and where not to.

                      Thanks-