Win a copy of Machine Learning Systems: Designs that scale this week in the Scala forum
or Xamarin in Action: Creating native cross-platform mobile apps in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Using SQL statements in JSP  RSS feed

 
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)
 
Author and ninkuma
Marshal
Posts: 66678
167
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.
 
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.
 
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: 66678
167
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.
 
author & internet detective
Marshal
Posts: 38249
623
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
Marshal
Posts: 38249
623
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.
 
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.
 
That's a very big dog. I think I want to go home now and hug this tiny ad:
Rocket Oven Kickstarter - from the trailboss
https://coderanch.com/t/695773/Rocket-Oven-Kickstarter-trailboss
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!