Using SQL statements in JSP

[c york]

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]

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]

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]

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]

I believe it is a fallacy that it takes longer to write code in a bean than it does in a JSP.

[Jeanne Boyarsky]

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]

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]

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.