[JSP] 2 nested result set

Discussion in 'Java' started by Mariano, Jan 19, 2008.

  1. Mariano

    Mariano Guest

    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
     
    Mariano, Jan 19, 2008
    #1
    1. Advertising

  2. Mariano

    Lew Guest

    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
     
    Lew, Jan 19, 2008
    #2
    1. Advertising

  3. Mariano

    Mariano Guest

    Re: 2 nested result set

    On 19 Gen, 03:41, Lew <> wrote:
    > > 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.


    I've resolve usig two different statement.
    Thank you so much (and to the java doc too :) )
     
    Mariano, Jan 19, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page