asked on

Populate html table using jstl sql server websphere application server

I am trying to populate a simple table in my webpage using content from a database table. Google search introduced me to the option of using JSTL. I tried but I get this error on my browser when I try to access to JSP

Error 500: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"

Here is my simple jsp code:

<%@ page import="java.io.*,java.util.*,java.net.MalformedURLException,java.text.*,java.sql.*"
	session="true"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>Customer Service Office Support System</title>
<link rel="stylesheet" href="css/style.css">
<script src="js/libs/modernizr-2.5.3.min.js"></script>
</head>


<sql:setDataSource
dataSource="jdbc/XXX"
var="conn"
/>

<sql:query dataSource="${conn}" var="alertRecs">
SELECT comments COMMENTS,dt1, dt from incidents;
</sql:query>

<div class="tbl">
	<div class="thead">
		<div class="th c1">Comments</div>
		<div class="th c2">dt1</div>
		<div class="th c3">dt</div>
	</div>
	<div class="clear"></div>
	<div class="tbody">
 		<c:forEach var="row" items=${alertRecs.rows}>
		<div class="tr">
			<div class="td c1"><c:out value="${row.COMMENTS}"/></div>
			<div class="td c2"><c:out value="${row.dt1}"/></div>
			<div class="td c3"><c:out value="${row.dt}"/></div>
		</div>
		</c:forEach>
	</div>
</div>

Open in new window

Here is my web.xml under the webinf directory of my webapp (I am adding the jsp to an existing webapp)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app id="WebApp_1336715422616">


  <context-param>
    <param-name>XXServlet</param-name>
    <param-value>http://DDDD:xxxxx/xxxxxx/servlet/xxxxxxxxxxxxx</param-value>
  </context-param>

    <context-param>
    <param-name>demoSiteUrl</param-name>
    <param-value>/NCC-SITE</param-value>
  </context-param>
  
<!--
  <context-param>
    <param-name>weblogic.jsp.pageCheckSeconds</param-name>
    <param-value>1</param-value>
  </context-param>
  -->
  
  

<resource-ref id="ResourceRef_1168431556765">
<description></description>
<res-ref-name>jdbc/XXX</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>



</web-app>

Open in new window

What else do I need to do to ensure this connects to the database correctly? I am building a prototype - if there is an easier solution to reading records from a database table and populating a table then I am happy to use that as well. The query to the database has to be parameterized - parameter coming from the URL

mrcoffee365

You don't appear to have your driver configured. You also might not have the driver in your classpath.

Check any tutorial on using jstl sql setDataSource, for example, this one:
http://www.tutorialspoint.com/jsp/jstl_sql_setdatasource_tag.htm

So something like this is required:
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/TEST"
user="user_id" password="mypassword"/>

If you don't know how to identify the driver, then tell us which dbms you are using. Also, probably which OS.

mightyestme

ASKER

Hi mrcoffee:

I tried this as well but with the same results:

<%@ page
	import="java.io.*,java.util.*,java.net.MalformedURLException,java.text.*,java.sql.*"
	session="true"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>CCCCC</title>
<link rel="stylesheet" href="css/style.css">
<script src="js/libs/modernizr-2.5.3.min.js"></script>
</head>

<sql:setDataSource var="conn" driver="sun.jdbc.odbc.JdbcOdbcDriver"
     url="jdbc:sqlserver://jdbc:odbc:XXX"
     user="sa"  password="mypassword"/>
<sql:query dataSource="${conn}" var="alertRecs">
   SELECT table_name from information_schema.tables;
</sql:query>

Open in new window

I am able to connect to the database if I use just a simple jsp connection similar to the following:

drv = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();	
		CCconn = DriverManager.getConnection("jdbc:odbc:XXX", "XX", "XXXXXX");	
  		CCstmt = CCconn.createStatement();
  	
  	  	query = "select comments COMMENTS, dt1, dt from incidents;
  	  	DCCrs = DCCstmt.executeQuery(query);

Open in new window

Is there something I need to add to the web.xml? Do i have to add this new jsp to the webapp or something?

Gregg

Could error have to do with the resource reference of type "javax.sql.DataSource" and jsp page is using "java.sql.*"?

mightyestme

ASKER

I am on Windows 2008 with SQL Server as the RDBMS.
I have a webapp deployed on Websphere 7.x - I am just trying to add this jsp to the existing webapp

mightyestme

ASKER

@gregg_s - Honestly, I have no idea. I am copying/pasting code snippets from all over to create this prototype.

If there is a quick, easy and dirty solution to help me select these three columns from a database table in jsp and populate a table in my HTML with it then I'll use that

mrcoffee365

Okay -- you're part way there. You can use the JDBC-ODBC bridge in the jstl. In that case, you do not identify your db as sql server in the url, you give it the jdbc-odbc url.

So, something like:

<sql:setDataSource var="conn" driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:whateverodbcnameyougaveyoursqlserverdb"
user="sa" password="mypassword"/>

This sample program uses jdbcodbc and the jstl tags:
http://www.java2s.com/Code/Java/JSTL/SQLTagOutExamples.htm

mightyestme

ASKER

I tried it but get the same error.

ASKER CERTIFIED SOLUTION

mrcoffee365

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mightyestme

ASKER

Thanks

mrcoffee365

Congrats -- what was the solution?