4 Replies Latest reply on Dec 16, 2005 8:25 PM by kyleyj

    How to keep Connection/ResultSet open across EJB methods

    kyleyj

      Within my application, there is a query that potentially can return large amounts of data. In order to display this data in the client app without freezing the UI waiting for the entire data set, we have three EJB methods to get the data. 1) Do the query and store the ResultSet 2) Get a specified number of the data 3) close the ResultSet.

      My initial thought would be that I would get an Error such as "fetch sequence out of bounds" or "connection closed". However with JBoss 3.2.3 and earlier it works fine. In the first method I get a Connection and query for the ResultSet (which is a class variable). Then the second method is called iteratively until the entire ResultSet is returned. The third method is called to close the ResultSet.

      When my EJB is deployed in JBoss 4.0, after retuning from the first method, because the Connection is closed the ResultSet is closed too -- thus causing an Exception when trying to iterator over a closed ResultSet.

      I thought that by changing my client to handle the transactions that I would be able to keep the ResultSet open. But that doesn't seem to work. So, my questions are if what I have been doing should/could work? Are there deployment settings that need to changed to allow for the ResultSet to remain open across EJB method calls? Am I correct in my thinking that if the client handle the transactions that the ResultSet could remain open?

      Any help would be appreciated in figuring out whether my application is not adhering to the J2EE EJB specs or if there is a problem with JBoss 4.0.

      Cheers!
      Kyley

        • 1. Re: How to keep Connection/ResultSet open across EJB methods

          Did you happend to find the solution for this issue. I am running into something similar to this. I have my EJB's and also some custom class which access the database. Both are using JNDI and are returning the connection back to the pool after creating the ResultSet(by closing the connection). I think that only the ResultSet is closed when we explictly tell it to close by calling rs.close(), or else if we close the statement. But by closing the Connectin object i never thought that it will close the ResultSet.
          My problem here is the in the custom code where i access the database i do a JNDI lookup from the pool and then use this Connection to query the database (I tried closing and keeping open the Connection object). But when i try to do rs.next() i get an exception saying
          'Operation not allowed after ResultSet closed'

          I am using JBoss 4.0.2 and this code worked perfectly in JBoss 3.2.3 which was my app server and now i am trying to migrate to JBoss 4.0.2.

          12:58:36,544 ERROR [ApplicationVersionBean] [select name, version_number from system_components order by name]
          12:58:36,700 ERROR [ApplicationVersionBean] java.sql.SQLException: Operation not allowed after ResultSet closed
          at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:639)
          at com.mysql.jdbc.ResultSet.next(ResultSet.java:6116)
          at org.jboss.resource.adapter.jdbc.WrappedResultSet.next(WrappedResultSet.java:520)
          at com.abs.compliancepro.application.utility.ApplicationVersionBean.retrieveDatabaseVersions(ApplicationVersionBean.java:94)
          at com.abs.compliancepro.application.utility.ApplicationVersionBean.(ApplicationVersionBean.java:39)
          at com.abs.compliancepro.application.utility.ApplicationVersionBean.getReference(ApplicationVersionBean.java:47)
          at org.apache.jsp.login_jsp._jspService(org.apache.jsp.login_jsp:75)
          at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
          at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
          at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
          at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
          at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
          at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
          at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:398)
          at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
          at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:238)

          Please help,

          Thanks in advance,
          Jobby

          • 2. Re: How to keep Connection/ResultSet open across EJB methods

            I found out the solution.
            in your -ds.xml add

            <!-- 3.2.6 the default value for track-statements is 'nowarn' which closes Statement's and
            ResultSet's without any warning, http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources
            -->
            <track-statements>false</track-statements>

            Thanks,
            Jobby

            • 3. Re: How to keep Connection/ResultSet open across EJB methods

              Correction, not in 3.2.6 but in 4.0.2 the default value is 'nowarn'

              Sorry,
              Jobby

              • 4. Re: How to keep Connection/ResultSet open across EJB methods
                kyleyj

                Thanks for the information about the solution. Your response with the solution arrived even before I could respond to your original post -- which wouldn't have helped you much, because I only had a hack of a work around. The solution that you've found works much better and is much easier to implement.

                I never would have thought that almost a year and a half later that I'd find a solution. That's awesome! Thanks.

                Cheers,
                Kyley