DB connectivity issue through Tomcat post Windows7 migration

[Madhura Apte]

Hi Java Gurus,

Hi All,

I have recently migrated from Windows XP to Windows 7. Since then I have been struggling to get my tomcat up and running.
I am unable to connect to any DB through Tomcat. Although I am able to connect to all the DBs through sqlplus (command prompt) and TOAD.

I have the following version installed on my machine :

Tomcat : Version -: 5.0
My Eclipse : Version -: 5.1.1 GA
Eclipse : version -:3.0.1
Oracle : Version -: 10.2.0.1

I have googled this issue and tried a lot of solutions suggested online, but none of them worked for me. Some of them are :

1. Add a TNS_ADMIN environment variable.
2. Different configurations in sqlnet.ora.
3. Turning off firewall security ( just to check if the DB connection is being denied due to this)
4. Granting full permissions to my ‘C:\ORACLE\product\10.2.0.1\NETWORK’ directory.
5. Using Resource Type "javax.sql.DataSource” instead of ‘oracle.jdbc.pool.OracleConnectionCacheImpl’ in the localhost resources.xml.

I do not see any issues while starting tomcat . I have even verified through Tomcat administrator that all the datasources mentioned in my resources.xml are properly getting loaded in tomcat.

The error that I get while connecting to DB is :

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory, cause:
java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

Please help me out with a solution. I have been struggling with this error since quite some time now :(

[Tim Holloway]

Welcome to the JavaRanch, Madhura!

I thought TNS was supposed to be on the way out 5+ years ago. Regardless, what it looks like is that the identifier you're using to get to the database instance isn't defined in TNSNAMES. For help on that, you'd need to ask in our Oracle forum, since that's highly specialized knowledge - no other DBMS uses a TNSNAMES-like configuration and that includes PostgreSQL, which is not the DBMS most similar to Oracle, but actually is reported to have a common code ancestor with Oracle.

FIrst: you ARE using Tomcat's connection pooling, aren't you? That's what we're going to be assuming since it's standard practice for industrial-grade J2EE. If you are attempting to make the webapp connect directly to the database without obtaining connections from the pool, our answers won't make sense.

Secondly: Oracle provides a JDBC Type 4 driver and JAR that contains it as well as its supporting classes. JDBC Type 4 means that communications between the client and server are strictly via TCP/IP using portable Java code - no local binary DLLs (or Linux .so) and no need to install additional Oracle client software. This jar (ojdbc4.jar last time I looked) must be installed in the TOMCAT_HOME/lib directory and must not be present in the application WAR.

Finally, the java.sql.DataSource class is an object supplied by the Tomcat Database Connection Pooler that can be used as a factory for JDBC Connection objects. These objects are not the actual Connections that are created from the oracle JDBC Driver class (oracle.jdbc.driver.OracleDriver or one of its relatives), but are instead wrappers for those Connections that support the pooling mechanism. You normally obtain the DataSource by using a JNDI lookup.

For further details and examples, a good place to look is the JDBC Connection Pool documentation page in the Tomcat documents at http://tomcat.apache.org