Re: [JSP] 2 nested result set
Mariano wrote:
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>");
}
%>
</xmlRoot>
root cause
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.
--
Lew