• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Connect - Table/View 'CATEGORY' does not exist

 
Rich Li
Greenhorn
Posts: 7
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



Hi,
I was trying to work through the AffableBean project in NetBean tutorial.
then I encountered a problem which made me painful.

javax.servlet.ServletException:
SELECT * FROM category, product WHERE category.id = product.category_id
: Table/View 'CATEGORY' does not exist.


I tried to figure it out, but failed many times.

I am pretty sure a few things
1. already set up the table category and product in db,
2. used the root user to connect affablebean db,
3. web.xml
<resource-ref>
<description>Connects to database for AffableBean application</description>
<res-ref-name>jdbc/affablebean</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

confirm jdbc/affablebean in GlassFish Server->Resources->JDBC->JDBC Resources
affablebeanPool in GlassFish Server->Resources->JDBC->Connection Pools
and be able to ping affablebeanPool in GlassFish Admin Console

4. my testDataSource.jsp

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<%--
Document : testDataSource
Created on : 9-Jul-2013, 11:54:38 AM
Author : LiuR
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>Hello World!</h1>
<sql:query var="result" dataSource="jdbc/affablebean">
SELECT * FROM category, product WHERE category.id = product.category_id
</sql:query>

<table border="1">
<!-- column headers -->
<tr>
<c:forEach var="columnName" items="${result.columnNames}">
<th><cut value="${columnName}"/></th>
</c:forEach>
</tr>
<!-- column data -->
<c:forEach var="row" items="${result.rowsByIndex}">
<tr>
<c:forEach var="column" items="${row}">
<td><cut value="${column}"/></td>
</c:forEach>
</tr>
</c:forEach>
</table>
</body>
</html>

5. Env: NetBeans 7.3, GlassFish 4

Any clue or ideas are much welcomed.

Thanks in advance!!!

 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What DB are you using? Try running the sql in the DB itself.

And welcome to the Ranch
 
Rich Li
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your answer, Tsang. I am using MySql 5.6, that sql statement run well in DB. I am stilling thinking some connection issue. but don't know what it is and how to fix.
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I noticed you are using JSTL sql tag. After checking, you are missing the <sql:setDataSource>



 
Rich Li
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I added <sql:setDataSource dataSource="jdbc/affablebean"/> , but the same, still got that error
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmm interesting. You should also try using the jdbc version of setDatasource

<sql:setDataSource var="some_var" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/<db_name>"
user="user_id" password="mypassword"/>

<sql:query var="result" dataSource="${some_var}">

Also make sure your project classpath has the jdbc driver (don't rely on the app server)
 
Rich Li
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Tsang. this way you suggest works well.
<sql:setDataSource var="some_var" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/<db_name>"
user="user_id" password="mypassword"/>



But I am still confused
why <sql:query var="result" dataSource="jdbc/affablebean">
SELECT * FROM category, product WHERE category.id = product.category_id
</sql:query>
not work in this case? So that means this is not good practise to use or Just some issues happened in my local?
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe the way you reference the datasource in the web.xml file is not what jstl sql tags wanted. Check out JSTL SQL with Tomcat, it use Tomcat instead of Glassfish.

Also I googled that Glassfish 4 issues with jdbc connection pool. Have you tried it on Glassfish 3? Because using resource-ref should work on GF3.
 
Thitipong Suparurkrat
Greenhorn
Posts: 6
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
at web.xml
try to change
<res-type>javax.sql.DataSource</res-type>
to
<res-type>javax.sql.ConnectionPoolDataSource</res-type>
 
Rich Li
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks lot! Tsang, I removed Glassfish 4 and reinstall Glassfish 3, it works great without changing any code. Even I still don't know what caused that issue.
Sorry for delaying respond, I was busy with some projects these days.
And Thanks again.
 
Rich Li
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


last year I encountered the above issue, this year I tried to redo this tutorial, I got the following error:

javax.servlet.ServletException: javax.servlet.jsp.JspException:
Unable to get connection, DataSource invalid: "java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused."



After trying for a few days, I found one solution without removing glassfish4 and reinstalling glassfish3.

1.go to WEB-INF/glassfish-web.xml
2. add resource-ref
<resource-ref>
<res-ref-name>jdbc/affablebean</res-ref-name>
<jndi-name>jdbc/affablebean</jndi-name>
<default-resource-principal>
<name>root</name>
<password>xxx</password>
</default-resource-principal>
</resource-ref>


this solution can work in case directly using JDBC connecting mySql, if you use EJB and JPA, you don't need to use this.
 
Marco Asteriti
Greenhorn
Posts: 1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I came across this issue today and found the fix. I'm following the same NetBeans E-Commerce tutorial, but using the NetBeansIDE v.8 instead of 7 and GlassFish 4 instead of 3 (I know, I'm asking for it). The problem arises in step 3 of the section "Creating a Resource Reference to the Data Source". When creating the reference, the tutorial correctly states in step 3 that the Resource Type should be 'javax.sql.ConnectionPoolDataSource', whereas the pic shows it set to 'javax.sql.DataSource'. If open up the Glassfish Admin Console, drill down to the node 'Resources/JDBC/JDBC Connection Pools/AffableBeanPool', you'll notice the correct resource type you need to add in the web.xml file...(i.e. javax.sql.ConnectionPoolDataSource).
 
Julius Iqbal
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
here is an easy solution to that prob "table doesn't exist"

I'm using win 8.1 enterprise , netbeans 8.0, jdk 7u67

just copy the database folder from C:\Users\"username"\.netbeans-derby\

to your project folder in netbeans project folder directory

same can be done for eclipse.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic