4 Replies Latest reply on Jan 18, 2012 12:57 PM by Ryan Slominski

    AS7 Oracle datasource configuration

    John Sulik Newbie

      I'm trying to perform a simple query and not getting very far.  Not sure if my datasource configuration is wrong or what.

       

      Here's my code:

       





      ctx = new InitialContext();




      ds = (DataSource) ctx.lookup("java:jboss/datasources/OracleDS");




      conn = ds.getConnection();




      pst = conn.prepareStatement("select * from bv_user_profile where lan_id = 'sulikj'");




      rset = pst.executeQuery();

       

      The lookup results in the error:  javax.naming.NameNotFoundException: Name 'datasources' not found in context ''

       

      Here's the datasource setup from standalone.xml (I excluded the pool and security elements)

      <subsystem xmlns="urn:jboss:domain:datasources:1.0">

          <datasources>

              <datasource jndi-name="java:jboss/datasources/OracleDS" pool-name="OracleDS"

                                    enabled="true" jta="true" use-java-context="true" use-ccm="true">

                  <connection-url>

                      jdbc:oracle:thin:@bsm_devl.mtolive.us.basf.com

                  </connection-url>

                  <driver>ojdbc14.jar</driver>

              </datasource>

          </datasources>

      </subsystem>

       

      Any push in the right direction is appreciated.

        • 1. Re: AS7 Oracle datasource configuration
          jaikiran pai Master

          Which version of JBoss AS7 is this? And from where are you doing the lookup? Also post the console logs when the server starts up.

          • 2. Re: AS7 Oracle datasource configuration
            Stefano Maestri Expert

            Have you already had a look to this blog post guiding you through driver deployment and datasources creation?

             

            http://www.javalinux.it/wordpress/2011/07/14/how-to-create-an-manage-datasources-in-as7/

             

            Example are made using MySql, but it should apply as is to Oracle too. I've given some answer in this forum on Oracle specific stuffs in last weeks too.

             

            And of course feel free to ask if you still have issue.

             

            bye

            S.

            • 3. Re: AS7 Oracle datasource configuration
              John Sulik Newbie

              I finally got back to this and it's working now. 

               

              My first problem is that I wasn't even connecting to the database.  I was getting the following error in the startup logs:

              12:52:37,879 INFO  [org.jboss.as.controller] (Controller Boot Thread) Service status report
                 New missing/unsatisfied dependencies:
                    service jboss.jdbc-driver.oracle (missing)

               

              I was trying to use ojdbc14.jar which isn't jdbc4 compliant.  I switched to ojdbc6.jar which simplified the configuration process.

               

              Here's my final configuration from standalone.xml

              <datasources>
              <datasource jndi-name="java:/poolDS" pool-name="OracleDS" enabled="true" use-java-context="true" jta="true">
                  <connection-url>jdbc:oracle:thin:@nti271.andipc.basf-corp.com:1521:bsmd</connection-url>
                <driver>oracle</driver>
                      <pool>
                          <min-pool-size>1</min-pool-size>
                          <max-pool-size>2</max-pool-size>
                          <prefill>true</prefill>
                          <use-strict-min>false</use-strict-min>
                          <flush-strategy>FailingConnectionOnly</flush-strategy>
                      </pool>
                      <security>
                          <user-name>orauser</user-name>
                          <password>orapassword</password>
                      </security>
                      <statement>
                          <prepared-statement-cache-size>32</prepared-statement-cache-size>
                      </statement>
              </datasource>
              <driver name="oracle" module="com.oracle.ojdbc6">
                   <xa-datasource-class>oracle.jdbc.OracleDriver</xa-datasource-class>
              </driver>
              </datasources>

               

              I created the directory modules/com/oracle/ojdbc6/main and placed the ojdbc6.jar and module.xml there.  The module.xml is:

              <module xmlns="urn:jboss:module:1.0" name="com.oracle.ojdbc6">
                <resources>
                  <resource-root path="ojdbc6.jar"/>
                </resources>
                 <dependencies>
                   <module name="javax.api"/>
                   <!--<module name="javax.transaction.api"/>-->
                 </dependencies>
              </module>

              The code I used to grab a connection and run a query is:

              ctx = new InitialContext();
              ds = (DataSource) ctx.lookup("java:/poolDS");
              conn = ds.getConnection();
              pst = conn.prepareStatement("select * from bv_user_profile where lan_id = 'sulikj'");
              rset = pst.executeQuery();
              writer.println("excuted query...</br>");
              int recordCount = 0;
              while (rset.next()){
                recordCount++;
                writer.println("Record count is " + recordCount + "</br>");
                writer.println("Record " + recordCount + " " + rset.getString(1) + " : " + rset.getString(2) + "<br/>");
              }

              I'm using jboss-as-7.0.0.Final.

              • 4. Re: AS7 Oracle datasource configuration
                Ryan Slominski Newbie

                What about configuring hot deployment?  The example above is for module configuration.  When I configure for hot deployment I just drop ojdbc6.jar into the deployments directory and add <driver>ojdbc6.jar</driver> under the <datasource> element.  Should I create an entry under <drivers> or is that only for the module approach?  I think it is working as is, but on startup JBoss first reports service xxxx.ojdbc6_jar (missing) and then later on in the startup reports service xxxx.ojdbc6_jar (now available).  Why is JBoss not finding it initially and then finding it later on?