Home.... | log in.... | Pub my question! | Sign-up....
Java-index >> Enterprise & Remote Computing >> Web Tier APIs

Not able to connect to database(MS SQL Server 2000) through JSTL tag

Hi,

I just want to retrieve some data from the database through a JSP page.I am using JSTL tags the code is as shown below. Whenever i execute this code i get an error message like this

My Code:

<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>

<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql_rt" %>

<sql:setDataSource var="datasource"

driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"

url="jdbc:mssqlserver:sqlserver://SYS57:1433;DatabaseName= sree"

user=" "

password=" "/>

<sql:query var="res" dataSource="${datasource}">

SELECT * FROM books

</sql:query>

<html>

<head>

<title>A First JSP Database</title>

</head>

<body>

<table border="1">

<tr>

<td>id</td><td>title</td><td>price</td>

</tr>

<c:forEach items="${res.rows}" var="row">

<tr>

<td><c:out value="${row.id}" /></td>

<td><c:out value="${row.name}" /></td>

<td><c:out value="${row.author}" /></td>

</tr>

</c:forEach>

</table>

</body>

</html>

error is this:

javax.servlet.ServletException: Unable to get connection, DataSource invalid:"No suitable driver"

org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:848)

org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)

org.apache.jsp.firstdb_jsp._jspService(org.apache.jsp.firstdb_jsp:93)

org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)

javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)

javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid:"No suitable driver"

org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(QueryTagSupport.java:308)

org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(QueryTagSupport.java:192)

org.apache.jsp.firstdb_jsp._jspx_meth_sql_query_0(org.apache.jsp.firstdb_jsp:132)

org.apache.jsp.firstdb_jsp._jspService(org.apache.jsp.firstdb_jsp:68)

org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)

javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)

javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

I am not clear with url attribute of setDatasource tag... I feel the error is because of that line only..... Kindly tell me how to specify the jdbc URL for MS SQL Server 2000 while using JSTL tags for connection.

Thanks,

Akshatha

[3576 byte] By [doubts4akkua] at [2007-10-3 0:28:17]
«« Selecting rows from a JTable using keys
»» Maintaining audit trail(who modified and when modified)
# 1
I dont use jstl or mssql so cant answer your question, but ... have you downloaded and installed the driver correctly on your web-server (Tomcat or similar)?
angrycata at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 2

(assuming you are using Tomcat, judging by the stacktrace)

When you declare a datasource you need to put the database driver in tomcat/common/lib, NOT in the WEB-INF/lib directory of your webapp.

The reason is because tomcat is making the actual connection, so Tomcat needs the driver class. If you put the driver in WEB-INF/lib then tomcat itself has no access to those classes, only your webapp.

gimbal2a at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 3

As the other have said, the driver needs to be in the "classpath"

The classpath for a web app is basically the WEB-INF/classes directory, any jars in the WEB-INF/lib directory.

Also servers normally have a /common/lib directory where classes "shared" between the server and the web apps go.

Often you will find database drivers here, so that the server can handle the connection pooling part.

There is an article here you should read:

http://support.microsoft.com/kb/313100/

Basically you need Msbase.jar, Msutil.jar and Mssqlserver.jar in your classpath (ie in the WEB-INF/lib, or /common/lib directories)

--

Other advice (just to get on my soapbox here)

It is generally recommended NOT to have sql code in a JSP page.

It makes the pages harder to maintain. Consider writing the database code in a bean somewhere.

If you MUST use the JSTL sql tags, at least set up a datasource in Tomcat, rather than specifying the URL parameters with the datasource string every page. That way at least you will have connection pooling.

http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html

Hope this helps,

evnafets

evnafetsa at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 4

"no suitable driver" means that the driver class was loaded, but the URL you gave has incorrect syntax.

the advice to set up the connection pool is spot on, but the real problem isn't CLASSPATH.

Your code says:

<sql:setDataSource var="datasource"

driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"

url="jdbc:mssqlserver:sqlserver://SYS57:1433;DatabaseName= sree"

user=" "

password=" "/>

The SQL Server driver docs say it should be:

jdbc:sqlserver://serverName\instance:port;property=value[;property=value]

I'm looking at this URL:

http://msdn2.microsoft.com/en-us/library/ms378428.aspx

The recommendation to move the connection info to a pool handled by Tomcat is absolutely correct. Look at the Tomcat docs for JNDI data source:

http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

%

duffymoa at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 5
HiI am unable to complie that JSTL program.Again it is giving same error.If possible any one please help me.Akshatha
doubts4akkua at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 6
Same error? Same problem. You didn't fix it. Look at it again.Who can help you without more information? Post the new URL, or just look at the docs a bit more.%
duffymoa at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 7
try replacing:url="jdbc:microsoft:sqlserver://SYS57:1433;DatabaseName=sree"
andresurbinaa at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
# 8

unable to rectify the error........ tried lot but all in vain...still trying........

my current code is:

<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>

<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql_rt" %>

<sql:setDataSource var="datasource" driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"

url="jdbc:microsoft:sqlserver://SYS57:1433;databaseName=sree" user="sa"

password="dfgdfg"/>

<sql:query var="res" dataSource="${datasource}">

SELECT * FROM books

</sql:query>

<html>

<head>

<title>A First JSP Database</title>

</head>

<body>

<table border="1">

<tr>

<td>id</td><td>title</td><td>price</td>

</tr>

<c:forEach items="${res.rows}" var="row">

<tr>

<td><c:out value="${row.id}" /></td>

<td><c:out value="${row.name}" /></td>

<td><c:out value="${row.author}" /></td>

</tr>

</c:forEach>

</table>

</body>

</html>

Thanks,

Akshatha

doubts4akkua at 2007-7-14 17:21:04 > top of Java-index,Enterprise & Remote Computing,Web Tier APIs...
Enterprise & Remote Computing
Web Tier APIs

Enterprise & Remote Computing New Topic

Enterprise & Remote Computing Hot Topic

Enterprise & Remote Computing

All Classified