5 Replies Latest reply on Sep 8, 2003 11:56 AM by Adrian Brock

    Problem with JDBC connection from stateless SessionBean

    ignac Newbie

      Hi,

      I've got problem executing queries to the Hypersonic database from my stateless session EJB.
      Below there are pieces of code and of jboss console output.
      I've tried on 3.2.2 and 3.2.1 versions of JBoss.
      Please, help!!!
      Thanks in advance.

      The code looks like that:
      -------------------------------------
      Context c = new InitialContext();
      ds = (DataSource) c.lookup("java:/DefaultDS");
      conn = ds.getConnection();
      getUser = conn.prepareStatement(GET_USER);
      getUser.setString(1, login);
      System.err.println(" %%%%%%%%%%%% Executing Query ... ");
      System.err.flush();
      ResultSet rs = getUser.executeQuery();
      System.err.println(" %%%%%%%%%%%% Executing Query ... DONE ! ");
      System.err.flush();
      if(rs.next()) {
      ....
      ------------------------------
      and the console says:
      ------------------------------
      14:10:51,718 ERROR [STDERR] %%%%%%%%%%%% Executing Query ...
      14:10:51,725 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
      14:10:51,729 INFO [TxConnectionManager$TxConnectionEventListener] throwable from unregister connection
      java.lang.IllegalStateException: Trying to return an unknown connection1! org.jboss.resource.adapter.jdbc.WrappedConnection@11cf0b
      at org.jboss.resource.connectionmanager.CachedConnectionManager.unregisterConnection(CachedConnectionManager.java:264)
      at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.connectionClosed(TxConnectionManager.java:550)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:287)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.close(WrappedConnection.java:127)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.resource.connectionmanager.CachedConnectionManager.closeAll(CachedConnectionManager.java:375)
      at org.jboss.resource.connectionmanager.CachedConnectionManager.popMetaAwareObject(CachedConnectionManager.java:199)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:190)
      at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
      at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
      at org.jboss.ejb.Container.invoke(Container.java:700)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:353)
      at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:83)
      at $Proxy52.getUser(Unknown Source)
      at org.sourcery.mim.facades.userauthorization.UserAuthorizationEJBBean.checkPassword(UserAuthorizationEJBBean.java:40)

        • 1. Re: Problem with JDBC connection from stateless SessionBean
          Adrian Brock Master

          You should close all connections and statements after
          you have finished with them. This returns them to the pool.

          Connection c = ds.getConnection();
          try
          {
          }
          finally
          {
          c.close();
          }

          And similar for statements.

          Regards,
          Adrian

          • 2. Re: Problem with JDBC connection from stateless SessionBean
            zielen2 Newbie

            hi, this is the second developer in the team.

            We would like to avoid closeing these connections. We have DAO object which is a stateless session bean. On create it opens a connection, and preperes the statements. On destroy, it closes them.

            This way, the statements are compiled once, and the only thing you have to do on method invocation, is fill in the parameters, and execute the query.

            Isn't there a possibility to do it this way with jboss?

            michal

            • 3. Re: Problem with JDBC connection from stateless SessionBean
              Adrian Brock Master

              The connections are not actually closed, they are
              returned to the pool.
              If you are really seeing a problem with the prepared
              statement information being discarded, I would suggest
              you get a better jdbc driver that understands connection
              pooling.

              Alternatively, you can enable "spec compliance" in the
              cached connection manager (transaction-service.xml)
              but this does not scale if you have many such queries.
              The idea being you can keep a connection specifically
              for the prepared statement.

              Regards,
              Adrian

              • 4. Re: Problem with JDBC connection from stateless SessionBean
                zielen2 Newbie

                ok. thanks for help. the change in transaction-service.xml, solved the problem.

                Now I've got a question. As I understood from what you wrote, the EJB developer, should not worry about pooling the connections, because jboss, does it for him (unless you change the thing in transaction-service.xml). Is there any mechanism for pooling prepared statements?
                Or is it : you either assign a group of prepared statements to a connection, and you pool the bundle like we do, using stateless session beans, or you just pool connections, using the jboss mechanism?

                michal

                • 5. Re: Problem with JDBC connection from stateless SessionBean
                  Adrian Brock Master

                  We don't pool statements.
                  A statement belongs to the connection.
                  You may not use the same connection on each request
                  when pooling.

                  The caching of prepared statement info is left to the quality of
                  implementation of the underlying db or driver support.

                  Regards,
                  Adrian