error on statement

[Tom Joiner]

This code generates are error, having something to do with using the "Statement" twice in a row. If I utilized "Statement2" it has no error and works correctly. Do I need to close out the recordset or the statement to utilize the statement again in another query?

The error is posted at the bottom.

   connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "SMART", "password");
    statement = connection.createStatement();
    statement2 = connection.createStatement();
    
    roleSet = statement.executeQuery("select * from role");
 
     select_from_account = "SELECT * FROM account WHERE username = '" + userName + "'";  
        rs = statement.executeQuery(select_from_account);

  
 
type Exception report
 
message
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception
 
javax.servlet.ServletException: Operation not allowed after ResultSet closed
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
org.apache.jsp.Account.Create_jsp._jspService(Create_jsp.java:279)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
 
root cause
 

 
java.sql.SQLException: Operation not allowed after ResultSet closed
com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:639)
com.mysql.jdbc.ResultSet.next(ResultSet.java:5869)
org.apache.jsp.Account.Create_jsp._jspService(Create_jsp.java:241)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
 
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.7 logs.
Apache Tomcat/5.5.7  

[Shailesh Chandra]

Reusing/Closing a Statement will cause any resultset associated with to be released, so you can not use ResultSet once you close the statement or you execute any other query on same statement object.

you are getting the error because you are using resultset (roleSet), after you have executed another query on statement.

Shailesh

[Nidhi Singhal]

The error is coming because of using executeQuery() on the same statement object two times.

try if this works..

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "SMART", "password");

String str1 = "SELECT * FROM account WHERE username = '" + userName + "'";

String str2 = "select * from role";

Statement statement1 = connection.createStatement();
ResultSet roleSet = statement1.executeQuery(str2);

Statement statement2 = connection.createStatement();
ResultSet rs = statement2.executeQuery(str1);

[Tom Joiner]

So you are supposed to create a new statement for every SQL execution? That seems a bit out of control.. is there a way to release it and use it again?

[Jeanne Boyarsky]

Yes. You can reuse a prepared statement. However the SQL needs to be the same, so it wouldn't help you here.

String sql = "SELECT * FROM account WHERE username = ?";
PreparedStatement statement1 = connection.prepareStatement(sql);
statement1.setString(1, userName)l
ResultSet roleSet = statement1.executeQuery();