[JSP] 2 nested result set

[Mariano]

I have a JSP file that make an XML Document:

<%@ page contentType="text/xml" pageEncoding="windows-1252"%>
<%@ page language="java" import="java.sql.*"%>
<%
Connection dbconn = null;
Class.forName("com.mysql.jdbc.Driver");
dbconn = DriverManager.getConnection("", "" "");
Statement stmt = dbconn.createStatement();
String query_1;
String query_2;

query_1 = "SELECT * .....";

query_2 = "SELECT * .......";

ResultSet rs1 = stmt.executeQuery(query_1);
ResultSet rs2 = stmt.executeQuery(query_2);

%>

<xmlRoot>

<%
while(rs1.next()) {
out.println("<son>");
while (rs2.next()) {
out.println("<grandchild ...>");
out.println("</grandchild>");
}
out.println("</son>");
}
%>

</xmlRoot>

but I get this error page when I try to run jsp page.

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented
it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing
JSP page /xml/xml.jsp at line 30

line 30 is the row of the first while

Stacktrace:

org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:
524)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:
417)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:
390)

root cause

javax.servlet.ServletException: java.sql.SQLException: Operation not
allowed after ResultSet closed

org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:
850)

org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:
779)

org.apache.jsp.xml.quest_005fcomp_jsp._jspService(quest_005fcomp_jsp.java:
105)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:
393)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:
390)

root cause

java.sql.SQLException: Operation not allowed after ResultSet closed
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)

org.apache.jsp.xml.quest_005fcomp_jsp._jspService(quest_005fcomp_jsp.java:
84)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:
393)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:
390)

note The full stack trace of the root cause is available in the Apache
Tomcat/6.0.14 logs.
Apache Tomcat/6.0.14

Where is the error? As you can see i never close ResultSet, so I don't
understand: Operation not allowed after ResultSet closed

 

[Lew]

I have a JSP file that make an XML Document:

Sidebar: don't put scriptlet in JSPs. See below.

<%@ page contentType="text/xml" pageEncoding="windows-1252"%>
<%@ page language="java" import="java.sql.*"%>
<%
Connection dbconn = null;
Class.forName("com.mysql.jdbc.Driver");
dbconn = DriverManager.getConnection("", "" "");
Statement stmt = dbconn.createStatement();
String query_1;
String query_2;

query_1 = "SELECT * .....";

query_2 = "SELECT * .......";

ResultSet rs1 = stmt.executeQuery(query_1);
ResultSet rs2 = stmt.executeQuery(query_2);

Check out the Javadocs. The answers are so often in the Javadocs, and one
expects that would be the first place one would look.

See below.

%>

<xmlRoot>

<%
while(rs1.next()) {
out.println("<son>");

You could emit this tag directly as JSP rather than using scriptlet.

while (rs2.next()) {
out.println("<grandchild ...>");
out.println("</grandchild>");
}
out.println("</son>");
}
%>



javax.servlet.ServletException: java.sql.SQLException: Operation not
allowed after ResultSet closed ...
java.sql.SQLException: Operation not allowed after ResultSet closed
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
...
Where is the error? As you can see i never close ResultSet, so I don't
understand: Operation not allowed after ResultSet closed

But you do close the first ResultSet:

A ResultSet object is automatically closed when the Statement object that generated it
is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

This from the Javadocs for ResultSet, the first place you should look for this
problem:
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html>

Sidebar: You can avoid mixing scriptlet in your JSP by using the JSP Standard
Tag Library (JSTL) and the Expression Language.

The JSTL has a rich set of SQL tags that would save you using a DAO layer,
which seems a suitable shortcut for your application.

 

[Mariano]

I've resolve usig two different statement.
Thank you so much (and to the java doc too )