pradyu pradyu - 7 months ago 18
SQL Question

'ResultSet is closed' at resultset function

I'm trying to pass a sql query but i am getting "java.sql.SQLException: Operation not allowed after ResultSet closed" . I have gone through other conversation based on same exception in stackoverflow but still I don't understand the problem.

Here is the code:

Class.forName("com.mysql.jdbc.Driver").newInstance();
String query="SELECT candidate,pan from hcl_candidates where jngstat='Joined'";

statement=conn.createStatement();
resultset=statement.executeQuery(query);

while(resultset.next()){

statement.executeUpdate("insert IGNORE into allinvoice (candidate,pan,invdate,client) select candidate,pan,CURDATE(),'HCL' from hcl_candidates where jngstat='Joined'");
}
}

catch (SQLException ex)
{
ex.printStackTrace();
out.println("Unable to connect to batabase.");

}

finally
{
try
{

if(statement!=null)
{
statement.close();
}
if(conn!=null){
conn.close();
}

if(resultset!=null){
resultset.close();
}
}
catch (SQLException e)
{

e.printStackTrace();
}
}


here is the complete stacktrace:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6320)
at org.apache.jsp.hclimport_jsp._jspService(hclimport_jsp.java:92)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

EJP EJP
Answer

Re-executing the Statement closes the prior result set.

It's hard to understand what you think you're doing here, executing a statement in a loop that doesn't rely on anything that the loop changes.

NB An SQLException can mean a lot of things besides 'unable to connect'.