[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1377
  • Last Modified:

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
0
mightyestme
Asked:
mightyestme
  • 5
  • 4
1 Solution
 
mrcoffee365Commented:
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.
0
 
mightyestmeAuthor Commented:
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?
0
 
GreggCommented:
Could error have to do with the resource reference of type "javax.sql.DataSource" and jsp page is using "java.sql.*"?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mightyestmeAuthor Commented:
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
0
 
mightyestmeAuthor Commented:
@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
0
 
mrcoffee365Commented:
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
0
 
mightyestmeAuthor Commented:
I tried it but get the same error.
0
 
mrcoffee365Commented:
Post your changed code -- all of it, but with usernames and passwords xx'd out as you have been doing.  There are many possibilities, and we can't check all of them because they are on your system.

Things you might do while we look at your code:
Test that you can query your database with the direct sql in a jsp page.  You said it worked with an odbc name -- check that again, and check that it's the same odbc name you are using in the <sql tag .  Check that you can use any of the <c or <SQL tags.  

This is an excellent explanation of versions and tests to make sure that you are declaring and accessing the core taglibs correctly:
http://www.mularien.com/blog/2008/04/24/how-to-reference-and-use-jstl-in-your-web-application/
0
 
mightyestmeAuthor Commented:
Thanks
0
 
mrcoffee365Commented:
Congrats -- what was the solution?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now