AS7 Oracle datasource configuration

[jsulik]

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.

[jaikiran]

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.

[maeste]

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.

[jsulik]

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.

[slominskir]

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?