I have this method in my application to get child organizations of a particular organization. It calls a stored procedure(which creates a temporary table).
StoredProcedureDAO.java
private Connection dbConnection = null;
private CallableStatement cstmt = null;
private ResultSet results = null;
inside getChildrens(int siteId) method
public ArrayList<String> getChildrens(int siteId)
{
ArrayList<String> childrenSites = new ArrayList<String>();
Connection pgConnection = getFreshDBConnection();
if (pgConnection == null) {
cat.error("getChildrens got a null connection");
return null;
}
try{
pgConnection.setAutoCommit(false);
cstmt = pgConnection.prepareCall("{ ? = call \"spGetChildSitesInfo\" ( ? ) }");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.setInt(2, siteId);
cstmt.execute();
results = (ResultSet) cstmt.getObject(1);
if(results != null){
while(results.next()){
childrenSites.add(results.getString(1));
}
}
cstmt.close();
pgConnection.commit();
pgConnection.close();
} catch (SQLException e){
try {
pgConnection.rollback();
cat.error("SQL Exception has occured while retriving the children", e);
} catch (Exception ex) {
cat.error("Error while connection rollback: ", e);
}
} finally {
try {
if(!pgConnection.isClosed()){
pgConnection.setAutoCommit(true);
pgConnection.close();
}
pgConnection = null;
} catch (Exception e) {
cat.error("Error while closing the connection: "+e);
}
}
return childrenSites;
}
I am calling this function on clicking a link in my jsp and everything works perfectly. The problem occurs when i click the link more than once in between the action. In that case i am getting the following exception
ERROR [StoredProcedureDAO] SQL Exception has occured while retriving the children
org.postgresql.util.PSQLException: Results cannot be retrieved from a CallableStatement before it is executed.
at org.postgresql.jdbc2.AbstractJdbc2Statement.checkIndex(AbstractJdbc2Statement.java:2417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.checkIndex(AbstractJdbc2Statement.java:2400)
at org.postgresql.jdbc2.AbstractJdbc2Statement.getObject(AbstractJdbc2Statement.java:2084)
at com.mycompany.ejb.session.StoredProcedureDAO.getChildrens(StoredProcedureDAO.java:120)
at com.mycompany.struts.action.SessionFacadeManager.getSelectedSite(SessionFacadeManager.java:495)
at org.apache.jsp.Clinical.BrowseCases_jsp._jspService(BrowseCases_jsp.java:383)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:373)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:336)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:654)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:445)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:379)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:292)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
at java.lang.Thread.run(Thread.java:619)
Adding some more info after experiments
This is my constructor(private)
private StoredProcedureDAO() {
DBManager dbManager = DBManager.getInstance();
dbConnection = dbManager.getConnection();
}
And here is the getInstance()
public static StoredProcedureDAO getInstance()
{
if (myself == null)
myself = new StoredProcedureDAO();
return myself;
}
I am calling the getChildrens() using the following
StoredProcedureDAO dao = StoredProcedureDAO.getInstance();
children = dao.getChildrens(site_id);
Now, if I remove the if statement
//if (myself == null)
in that case i am not getting the exception. I am using postgresql-9.0-801.jdbc4.jar driver. Can anyone explain me the reason?
You have a multi-threading issue. It seems like you're sharing the the ResultSet member variables between different threads (a servlet (or JSP) can be shared by multiple threads), hence the error. Are you sure you're creating multiple instances of StoredProcedureDAO?