Can't seem to resolve the following error:
javax.servlet.ServletException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"
I'm using Tomcat 5.0.28, j2sdk1.4.2_08 and MYSQL 5.0.
I have the MYSQL driver mysql-connector-java-3.1.12-bin.jar in the classpath and in the WEB-INF/lib directory, neither one fixes the problem.
Any suggestions?
The URL url="jdbc:mysql://localhost:3306/wroxcatalog" looks right to me...wroxcatalog is the MySQL database.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ include file="include.jsp"%>
<sql:setDataSource
var="datasource"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/wroxcatalog"
user="wroxuser"
password="wrox" />
<sql:query var="books" dataSource="${datasource}">
select id, name, description, price from products order by id
</sql:query>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>TryDB</title>
</head>
<body>
<table border="1">
<tr>
<td>ID</td><td>Name</td><td>Description</td><td>Price</td>
</tr>
<c:forEach items="${books.rows}" var="row">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.name}"/></td>
<td><c:out value="${row.description}"/></td>
<td><c:out value="${row.price}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
In include.jsp:
<%@ taglib prefix="c"uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="x"uri="http://java.sun.com/jsp/jstl/xml" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
I have tried putting the MySQL JAR (mysql-connector-java-3.1.12-bin.jar) in WEB-INF/lib, common/lib and shared/lib.
I have tried putting the ZIP file mysql-connector-java-3.1.12.zip in WEB-INF/lib, common/lib and shared/lib
I have placed "<% Class.forName("com.mysql.jdbc.Driver"); %>
" in the JSP page.
I changed localhost in the URL connection string to 127.0.0.1.
I added the MySQL server port 3306 to the URL connection string.
I have tried using another MySQL Jar mysql-connector-java-5.0.0-beta-bin.jar.
I have completely uninstalled Tomcat 5.0.28 and reinstalled it.
I have completely uninstalled MySQL and reinstalled it, resetting up the database.
When you say I already tried entering <% Class.forName("com.mysql.jdbc.Driver"); %> with no effect. can you confirm that you checked that you were getting exactly the same exception, rather than a different one?
Can you try using a scriptlet to get the connection directly instead of via a datasource or through the JSTL tags.
You mean something like this?
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page import="java.lang.*, java.util.*, java.io.*, java.sql.*" %>
<%!
String db_user= "wroxuser";
String db_password = "wrox";
String db_host= "localhost:3306";
String db_database = "wroxcatalog";
%>
<%
try{
/*
* 1 - in WEB-INF/lib put mysql-connector-java-3.1.2-bin.jar
* 2 - Load driver
* 3 - Connection
* 4 - Success hopefully
*/
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException k) {
out.println(" Error Class : " + k);
}
try{
Connection conn = DriverManager.getConnection(
"jdbc:mysql://"+db_host+"/"+db_database, db_user, db_password);
response.getWriter().println("<font color=green><b>");
response.getWriter().println("Connection success!");
response.getWriter().println("</b></font>");
} catch (SQLException sqle) {
out.println(" SQL ERROR: " + sqle);
}
%>
I've never used the sql jstl tags - but I've got a nasty suspicion that it's trying to write into the JNDI context, which isn't actually possible on Tomcat. That may be total rubbish, but skimming the documentation it seems plausible.
(edit) By "it" I mean the setDatasource tag.
Is there any particular reason why you can't set up a JNDI resource for the datasource and use that from within your JSP? If I'm right, that would work just fine.
I'm trying to do an example out of the book "Beginning JSP 2.0", below is the code for the example:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ include file="include.jsp"%>
<% Class.forName("com.mysql.jdbc.Driver"); %>
<sql:setDataSource
var="datasource"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/wroxcatalog"
user="wroxuser"
password="wrox"
/>
<sql:query var="books" dataSource="${datasource}">
select id, name, description, price from products order by id
</sql:query>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>TryDB</title>
</head>
<body>
<table border="1">
<tr>
<td>ID</td><td>Name</td><td>Description</td><td>Price</td>
</tr>
<c:forEach items="${books.rows}" var="row">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.name}"/></td>
<td><c:out value="${row.description}"/></td>
<td><c:out value="${row.price}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
I don't know what you mean by JNDI resource or how to code one. I'm almost to the point of giving up and chucking the book and buying another one.
<professor-farnsworth>
Good news, everybody!
</professor-farnsworth>
I'm wrong. The bad news is that I can't figure out what you're doing wrong. So another question - have you stopped and re-started Tomcat?
(edit)
This is actually very odd. If you have a missing JDBC driver, the wrong username, the wrong password, or the wrong variable ("datasource") name, then you get completely different error messages.
As Duffy indicates in his first reply, this error seems to be claiming that the JDBC url is wrong - and yet your scriptlet suggests that it's *not* wrong.
However, so far I've only tested this against a PostgreSQL database (all that I've got on my system at the moment). I'll install MySQL and try further - but that URL does look right...
(edit)
Other potentially pertinent differences: I'm currently using Tomcat 5.5.9 (just installing 5.0.28 now though). I'm also using Jakarta JSTL 1.1 - not sure which version you're on.
(edit)
Still works on Tomcat 5.0.28...
...with MySQL 5.0 and Connector/J 3.1.12...
...with your main JSP file and the following include.jsp:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
And the same usernames, passwords, database name, and a compatible catalog.
What's in your web.xml file?
Do you have anything else in the WEB-INF directory?
Where are your TLD files?
Where did you get your JSTL library from, what version is it, and where did you copy it?
On my test the jstl.jar, standard.jar, and mysql driver are all placed in the common/lib directory. I have a WEB-INF/tld directory containing all the TLD files from JSTL 1.1 in my project directory. Aside from that the project directory only contains the two JSP files and the following web.xml in the WEB-INF directory:
<?xml version="1.0" encoding="ISO-8859-1"?>
<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 http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<display-name>Sample</display-name>
<description>Sample application</description>
<taglib>
<taglib-uri>http://java.sun.com/jsp/jstl/sql</taglib-uri>
<taglib-location>/WEB-INF/tld/sql.tld</taglib-location>
</taglib>
</web-app>
Based on the OP's parallel post on JavaRanch, he has in desperation put the MySQL driver jar file in pretty much every directory that could possibly be right. But given the ease of falling into classloader hell in Tomcat and its relatives, it's possible that there are different classloaders trying to load the JDBC driver and that's confusing the issue. So I would recommend cleaning them all out and just putting the jar file in one place. And restarting Tomcat.
Already tried that....I still get the "No Suitable Driver" message.
I want to thank all of you that helped...I really appreciate the effort. You have no idea how much you appreciate the help other people give you until you are completely clueless about how to resolve a problem and you wait with excited atticipation on possible solutions that others offer up.
I'm giving up on this problem....chucking the book and starting over with another book.
> I'm giving up on this problem....chucking the book
> and starting over with another book.
I really don't think that's the answer to your problems - I was able to get the same code that you posted working. It's something specific to your configuration that's causing the problem. So there's probably nothing wrong with the book.
If you like I can archive the entire working Tomcat install and make that available to you by FTP. Drop me an email (dave@paperstack.com) if it would be helpful. You'll still need a seperate MySQL install, but I think that's unlikely to be the cause of the problem (I'd expect different error messages).
D.
