Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Using SQL statements in JSP  RSS feed

 
c york
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am a beginner and learning JSP. I am using a simple SQL statement in the program. I don't understand how the root cause is the '=' in the statement.

need help.....

org.apache.jasper.JasperException: Exception in JSP: /MyJsp.jsp:37

34: };
35:
36: Statement stmt = conn.createStatement();
37: ResultSet rset = stmt.executeQuery("select Person_nbr from Application" +
38: "where person_nbr = 1000");
39: while (rset.next()){ %>
40: <%=rset.getString(3)%>


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:467)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause

javax.servlet.ServletException: Line 1: Incorrect syntax near '='.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
org.apache.jsp.MyJsp_jsp._jspService(MyJsp_jsp.java:101)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause

java.sql.SQLException: Line 1: Incorrect syntax near '='.
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258)
org.apache.jsp.MyJsp_jsp._jspService(MyJsp_jsp.java:78)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65830
134
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a beginner, I must advise you against learning bad habits such as putting SQL statements in a JSP.

Ideally, your application will follow best-practice MVC patterns, but at minimum, you should remove the Java code to perform database access from your JSP and factor it into Java classses, be they servlet controllers or task beans.

This not only creates a better application structure, it makes problems such as the one you are experiencing easier to diagnose.
 
Herman Schelti
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi C,

I think you will need a space between 'Application' and 'where'
Is it Person_nb or person_nb?

Herman
I totally agree with Bear's response.
 
Dilshan Edirisuriya
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with you Bear. But sometimes i face situations where i couldnt do so. Like in resulset navigations it will be time consuming when you using another bean. So how should i cope up with this situation?
[ May 12, 2007: Message edited by: Dilshan Edirisuriya ]
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65830
134
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe it is a fallacy that it takes longer to write code in a bean than it does in a JSP.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36417
454
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Dilshan Edirisuriya:
But sometimes i face situations where i couldnt do so. Like in resulset navigations it will be time consuming when you using another bean. So how should i cope up with this situation?

You can still do the query in Java. You get the records that will be displayed in the JSP and store them in a data structure like an ArrayList. Then the JSP, just has to loop through the data structure and present the data.

I notice a few things about the JDBC code:
1) I agree with Herman that you need a space before the "where."
2) You are only returning one column in your query. "rset.getString(3)" should be "rset.getString(1)"
3) You are only returning one column for which you already know the value. Person number is 1000 for any rows matching your query which you know before you do the query.

A general troubleshooting tip: The easiest way to debug your SQL statement is to copy it to your database command line and run it there.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36417
454
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For future reference, note that we have a separate forum for JDBC questions. (6 down from the JSP forum.) All SQL and JDBC questions should go there - even if the code happens to reside in a JSP.
 
Deepak Bala
Bartender
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Make the controller call code that will fetch the results for you. Now populate these results into a custom object and put these objects into a collection if necessary. Attach the collection to the appropriate scope and make the JSP fetch the data from that scope using standard/custom actions. If you put scriplets/SQLs into JSP it will become hard to maintain.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!