aspose file tools*
The moose likes Tomcat and the fly likes Database connectivity issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Tomcat
Bookmark "Database connectivity issue" Watch "Database connectivity issue" New topic
Author

Database connectivity issue

Sen George
Ranch Hand

Joined: Sep 21, 2005
Posts: 76
I am running Tomcat 5.5 and Java JDK 1.6. I am facing a jdbc connectivity issue while trying to connect to a local Oracle database (version 11.1.0) installed on my laptop. The required changes-I trust-have been made in the files context.xml and web.xml. The relevant listings from both files are as follows:

context.xml


<!-- The contents of this file will be loaded for each web application -->
<Context>

<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>

<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->

<!-- Oracle DB Connection Pool -->
<Resource name="jdbc/myOracle" auth="Container"
type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:abelandr"
username="hr" password="hr" maxActive="20" maxIdle="10"
maxWait="-1"/>

</Context>


web.xml


<web-app
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/myOracle</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

</web-app>


I have placed the relevant jar file ojdbc6.jar in the directories CATALINA_HOME/common/lib as well as [web app name]/WEB-INF/lib. In spite of all this, the error message received is as follows (partial listing):


org.apache.jasper.JasperException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/myOracle"
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:460)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

root cause

javax.servlet.ServletException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/myOracle"
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.db_005ftest_jsp._jspService(db_005ftest_jsp.java:83)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)


Please advise what is being missed out here.

Thanks.
Misha Ver
Ranch Hand

Joined: Mar 03, 2008
Posts: 470
Sen George wrote:the error message received is as follows


When do you get the error message and how do you access datasource in your application?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

Remove the Oracle jar from your WAR. There's rarely a good reason to put a database driver in the app instead of the server, and when you have them in both places, you risk having classpath problems.


Verify that the driver classname is, in fact represented in your JDBC JAR (use "jar -tvf ojdbc6.jar") and that you've spelled it right.


Customer surveys are for companies who didn't pay proper attention to begin with.
Sen George
Ranch Hand

Joined: Sep 21, 2005
Posts: 76
I have removed the ojdbc6.jar file from the WEB-INF/lib directory and still the same results. I have verified that the jar file has the file OracleDriver.class. Here is the code I am using to test the database connectivity issue.


Please advise.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

I don't see anything wrong with your spec files, but I'm not sure about putting the jar in TOMCAT_HOME/common/lib. I think there's a server/lib directory in Tomcat 5.5. They simplified it in Tomcat6 so I don't remember. But regardless of its exact name, the server-only lib directory is where you want the jar, since the server owns the connection pool, not the app.
Sen George
Ranch Hand

Joined: Sep 21, 2005
Posts: 76
Placed the jar file under server/lib instead of common/lib and also added the entry of the jar file in PATH variable-still no luck. The database indeed works with tools like Oracle SQL*Plus and JDeveloper.

Also, I am wondering about the significance of the string value in the entry driverClassName="oracle.jdbc.OracleDriver" in the file context.xml. Shouldn't the driverClassName should be just 'OracleDriver'? Does the path to the class file be identical to the one in the definition above? Please advise.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18909
    
    8

No, you do need to specify the fully-qualified class name in your configuration. And what you specify does have to be the fully-qualified name of a class which is actually in that jar file which -- hopefully -- is now in the right place. And did you restart the server?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

PATH has no meaning for Java, only CLASSPATH. However, Tomcat maintains its classpaths by internal configuration, not by by the external environment, so even modifying CLASSPATH would not help.

Your driverClassName does have to be the fully-qualified classname, so "OracleDriver" wouldn't work. But the message you're getting is consistent with the driver not being found in the classpath, which is why I asked you to scan the ojdbc6.jar file to make sure it was in there.

I'm not working with Oracle just now, so I don't have any reference projects I can check, but I did use Oracle for years and what you're doing is close enough that my memory (what's left of it) doesn't detect anything obviously wrong. Most likely it's something like a mis-capitalized bit of text or a ":" out of place. See if you can't find some example Oracle JDBC connection URLs for the version of Oracle you're running and verify you have it letter-for-letter perfect. It's not going to offer any hints, alas.

Misha Ver
Ranch Hand

Joined: Mar 03, 2008
Posts: 470
Look at http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html and note this


Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
Sen George
Ranch Hand

Joined: Sep 21, 2005
Posts: 76
I got it to work finally. As per the Tomcat documentation, the jar file-ojdbc6.jar-needs to be present only in $CATALINA_HOME/common/lib directory. I had the jar file in this directory earlier also, but don't know why it threw up errors. The only concrete thing which comes to my mind is that while trying different options, I had changed the value for the type attribute from "javax.sql.DataSource" to ""javax.jsp.jstl.sql.DataSource" in the context.xml file. Think I didn't change it back after copying the jar file. Anyways, it works now.

I was almost giving up. Thank you all for the tips and help.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

Actually, it's possible that you had junk classes in your Tomcat work directory, which is something I forgot about. Two things that are always good to do when redeploying are to delete the exploded WAR (if there is one) from your webapps directory (otherwise, Tomcat will use IT instead of the newer .war file!) and to delete the workfiles from TOMCAT_HOME/work/Catalina/localhost.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database connectivity issue