aspose file tools*
The moose likes Tomcat and the fly likes unable to make a db connection using JNDI and Oracle tnsname Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Tomcat
Bookmark "unable to make a db connection using JNDI and Oracle tnsname" Watch "unable to make a db connection using JNDI and Oracle tnsname" New topic
Author

unable to make a db connection using JNDI and Oracle tnsname

Paul Barter
Greenhorn

Joined: May 02, 2013
Posts: 2
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
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16145
    
  21

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.


Customer surveys are for companies who didn't pay proper attention to begin with.
Paul Barter
Greenhorn

Joined: May 02, 2013
Posts: 2
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="jdbcracle: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

Joined: Jun 25, 2001
Posts: 16145
    
  21

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: unable to make a db connection using JNDI and Oracle tnsname