unable to make a db connection using JNDI and Oracle tnsname
Post by:Paul Barter
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):
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.
Post by:Tim Holloway
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.
Post by:Paul Barter
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:
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.