Meaningless Drivel is fun!*
The moose likes JBoss/WildFly and the fly likes The result set is closed exception. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "The result set is closed exception." Watch "The result set is closed exception." New topic
Author

The result set is closed exception.

Indira Koncur
Greenhorn

Joined: Dec 26, 2007
Posts: 8
Hello,

What would be the reasons why I would be getting a "The result set is closed" exception when trying to execute a SQL query using a datasource through JBoss. Do you know how can I turn on debugging on a datasource or how can I track the queries that I am trying to execute in the database through Jboss?

The datasource as far as I can tell is well-formed and appears as bound in the JNDI tree, however I can't seem to be able to execute any queries with any of its connections. Database is Oracle 9.2.0.6

Much Appreciated.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9327
    
110

Indira,

Welcome to JavaRanch



What would be the reasons why I would be getting a "The result set is closed" exception when trying to execute a SQL query using a datasource through JBoss.


You will have to post the entire exception stacktrace and probably even your code to figure out why you might be getting that exception.


Do you know how can I turn on debugging on a datasource or how can I track the queries that I am trying to execute in the database through Jboss?


The server.log should contain enough information by default about the queries being fired. If not, you can change the log4j.xml (or jboss-log4j.xml depending on the version of JBoss you are using) present in %JBOSS_HOME%/server/< serverName>/conf folder.

Also, post the contents of your *-ds.xml file.


[My Blog] [JavaRanch Journal]
Indira Koncur
Greenhorn

Joined: Dec 26, 2007
Posts: 8
Hi,

Thanks for your reply. Here is the full stack trace:

com.fness.core.exception.SQLFnessException: An error occured in class com.fness.ora11i.common.mi.dao.UsersDAO.retrieve
The result set is closed.
at com.fness.ora11i.common.mi.dao.UsersDAO.retrieve(UsersDAO.java:110)
at com.fness.ora11i.inquiry.service.LogonBD.invoke(LogonBD.java:47)
at com.fness.ora11i.inquiry.struts.action.LogonAction.processRequest(LogonAction.java:43)
at com.fness.core.struts.action.BaseAction.perform(BaseAction.java:140)
at org.apache.struts.action.ActionServlet.processActionPerform(ActionServlet.java:1787)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1586)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
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.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:543)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)


After I dug around some more, I found that the problem is that for some reason I cannot call getConnection() twice in the same code. I'll elaborate: basically, I have this code below:

public BaseValueObject retrieve(String pId) throws FnessException {

Connection conn = null;

ResultSet rs = null;
PreparedStatement stmt = null;
// @TODO: look up table name from IConstants
String query = "SELECT ID, FIRST_NAME, +
"FROM USERS;
try {
conn = getConnection();
stmt = conn.prepareStatement(query);

rs = stmt.executeQuery();
if (rs.next()) {

user.setPwd(rs.getInt("pwd"));

user.setUserRoles(getRoles(pId));

}
} catch (SQLException sqle) {
throw new SQLFnessException("An error occured in " + this.getClass() + ".retrieve", sqle);
} finally {
close(rs);
close(stmt);
close(conn);
}
mLog.mark("leaving " + this.getClass() + ".retrieve");
return user;
}


and getRoles(pId) is this function:



private ArrayList getRoles(String pId) throws FnessException {

Connection conn = null;
ResultSet rs = null;
ArrayList returnAL = null;
PreparedStatement stmt = null;
String query = "SELECT SYS_ROLE_ID " +
"FROM USER_ROLES WHERE USER_ID = ?";
try {
conn = getConnection();
stmt = conn.prepareStatement(query);
stmt.setString(1, pId);
rs = stmt.executeQuery();
while (rs.next()) {
if (returnAL == null) {
returnAL = new ArrayList();
}
returnAL.add(rs.getString("role_id)"));
}
} catch (SQLException sqle) {
throw new SQLFnessException("An error occured in " + this.getClass() + ".getRoles", sqle);
} finally {
close(rs);
close(stmt);
close(conn);
}
mLog.mark("leaving " + this.getClass() + ".getRoles");
return returnAL;
}


So, if I comment out one method and leave the other one uncommented, I won't get the error, but if I leave both uncommented and make the method retrieve call getRoles then that's when the error happens, so I was thinking it had to do something with the XA datasource, but this is how I have it defined:

<datasources>

<xa-datasource>
<jndi-name>myXADS</jndi-name>
<track-connection-by-tx/>
<isSameRM-override-value>false</isSameRM-override-value>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

<xa-datasource-property name="URL">jdbc racle:thin:@lady:1785:mcprd</xa-datasource-property>
<xa-datasource-property name="User">la</xa-datasource-property>
<xa-datasource-property name="Password">la</xa-datasource-property>
<!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the
pool -->
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<!-- Checks the Oracle error codes and messages for fatal errors -->
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global
transaction and vice-versa -->
<no-tx-separate-pools/>
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
<min-pool-size>5</min-pool-size>
<max-pool-size>5</max-pool-size>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>2</idle-timeout-minutes>
</xa-datasource>
</datasources>

I also modified the jboss-service.xml to set Pad to true. What else do you think I should do?

Also I understand about modifying the jboss-log4j.xml to get more information, I just don't know what class should I put as the one that will give me more debug information. The server.log is not really giving me anything useful.

Thanks a lot!
Indira
[ December 27, 2007: Message edited by: Indira Koncur ]
Indira Koncur
Greenhorn

Joined: Dec 26, 2007
Posts: 8
One more thing, after I turned on <track-statements>true</track-statements> I see now these new errors in the log:

2007-12-27 11:30:17,825 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
java.lang.Throwable: STACKTRACE
at org.jboss.resource.adapter.jdbc.WrappedConnection.registerStatement(WrappedConnection.java:576)
at org.jboss.resource.adapter.jdbc.WrappedStatement.<init>(WrappedStatement.java:62)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.<init>(WrappedPreparedStatement.java:56)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:187)
at com.fness.ora11i.common.mi.dao.UsersDAO.retrieve(UsersDAO.java:93)
at com.fness.ora11i.inquiry.service.LogonBD.invoke(LogonBD.java:47)
at com.fness.ora11i.inquiry.struts.action.LogonAction.processRequest(LogonAction.java:43)
at com.fness.core.struts.action.BaseAction.perform(BaseAction.java:140)
at org.apache.struts.action.ActionServlet.processActionPerform(ActionServlet.java:1787)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1586)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
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.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:543)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)
2007-12-27 11:30:17,826 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a result set you left open! Please close it yourself.
java.lang.Throwable: STACKTRACE
at org.jboss.resource.adapter.jdbc.WrappedStatement.registerResultSet(WrappedStatement.java:617)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:237)
at com.fness.ora11i.common.mi.dao.UsersDAO.retrieve(UsersDAO.java:95)
at com.fness.ora11i.inquiry.service.LogonBD.invoke(LogonBD.java:47)
at com.fness.ora11i.inquiry.struts.action.LogonAction.processRequest(LogonAction.java:43)
at com.fness.core.struts.action.BaseAction.perform(BaseAction.java:140)
at org.apache.struts.action.ActionServlet.processActionPerform(ActionServlet.java:1787)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1586)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
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.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:543)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)
2007-12-27 11:30:17,988 INFO [STDOUT] 1198783817054
2007-12-27 11:30:17,988 INFO [STDOUT]
2007-12-27 11:30:17,988 INFO [STDOUT] ERROR



What is this "Closing a result set you left open!", basically yes,there is a statement that is open in the first method and that it remains open while it calls the getRoles method which opens another statement, then I close that one and go back to the first method from which I was called from....is there some kind of transaction problem going on here?

By the way Jboss is 4.2.0 and database is 9.2.0.6

Thanks!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18138
    
    8

You call a method getConnection() in both of those methods. I don't see the code for that method but my guess is that it returns the same connection to both methods. Then when you close it in the getRoles() method, the retrieve() method cannot use it any more because it's closed.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: The result set is closed exception.
 
Similar Threads
Exception com .ibm. ejs. cm. proxy. OracleConnectionProxy is closed
Connection Class design
DataSource.getConnection() returning closed connections
Apache Tomcat DBCP pool
DataSource Connections not closed