• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

unable to make a db connection using JNDI and Oracle tnsname

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all.

I am having trouble making a JNDI db connection to an Oracle 11G database under Tomcat 7 using a tnsname. I need to use a tnsname and not the hostname / port / SID for technical reasons. Here are my resource properties (trimmed down):

<Resource
auth="Container"
name="jdbc/immv2db"
type="oracle.jdbc.xa.OracleXADataSource"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
driverType="oci"
nativeXA="true"
tnsEntry="<tnsname>"
driverClassName="oracle.jdbc.OracleDriver"
user="..."
password="..."
/>

I am using the ojdbc14.jar file which comes with the Oracle client install for the Oracle OCI driver. I just get a null pointer exception (with no stack trace) when executing the following code:

InitialContext ctxss = new InitialContext();
String dbcpURL = "java:comp/env/jdbc/immv2db";
Connection c = ((DataSource) ctxss.lookup(dbcpURL)).getConnection();

ps I know that the setup is right because if I use other properties with hostname etc then I do get a connection.

Appreciate any help here.

thanks
Paul
 
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Interesting. Not the least since TNSNAMES were supposed to be going away circa 2005. I'd guess that the reason you want to do this is to use the OCI interface, although most of the benefits of OCI look to be stuff that can probably be done more intelligently with a good ORM+cache manager.

Nevertheless, I think that the most likely reason for your problem is that the "tnsName" attribute on your definition should be something more like "tNSEntryName".

Note that the TNSEntryName property violates one of the fundamental rules of JavaBeans, which is that property names must begin with a lower-case letter, so I'm not sure exactly how you're supposed to capitalize it in the XML.
 
Paul Barter
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Tim

The reason we are using tnsnames is to be compliant with Oracle RAC. Our other product changed the way they made connections to use tnsnames in order for the listener to be able to connect the app to the currently up RAC instance. Is there a better way to do this?

By the way, I realised that I just needed to add a System property to be able to use the "thin" Oracle db driver, and then it all worked! So my resource file now looks like this:

<Resource
auth="Container"
name="jdbc/immv2db"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="jdbc:oracle:thin:@<tnsname>"
validationQuery="select * from cat"
maxIdle="5"
maxActive="10"
driverClassName="oracle.jdbc.OracleDriver"
maxWait="12500"
removeAbandoned="true"
username="..."
password="..."
logAbandoned="true"
removeAbandonedTimeout="120"/>

with the extra java code:

System.setProperty("oracle.net.tns_admin", System.getenv("ORACLE_HOME") + "\\NETWORK\\ADMIN");

cheers,
Paul
 
Tim Holloway
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't deal as much with Oracle these days as I used to, but one of the fundamental premises of clustering is that you shouldn't have to stop clients and re-target them if a node goes down. In other words, a single access point should suffice for the cluster and it's the cluster's responsibility to ensure that that access point responds even if the primary listener goes down. This is a fairly simple thing to do even on non-Oracle HA systems. So a thin (type 4) jdbc driver should be sufficient from that point of view as long as you have been given the cluster's URL and not the URL of a specific cluster node.

The one thing I do wonder about is your validationQuery, since typically queries like that require a fair bit of labor on the database server. A "SELECT 1 FROM dual" should be all that's required to poll the connections, I think.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic