7 Replies Latest reply on Jul 29, 2003 8:09 AM by Bernd Zeitler

    Can I keep an open JDBC connection through the life of state

    moonrainbow Newbie

      I'd like to keep an open JDBC connection in a stateless session bean. The reason is to avoid creation/compilation of a statement every time a request comes to the bean. I use JBoss 3.0.7 and Oracle 8.
      In ejbCreate I fetch a connection from the pool and prepare a statement:
      InitialContext lContext = new InitialContext();
      String lDataSourceName = (String) lContext.lookup(
      "java:comp/env/BVDataSourceName"); // hardcoded, mentioned in jboss.xml
      DataSource ds = (DataSource) lContext.lookup(lDataSourceName);
      c = ds.getConnection();
      decypherCall = c.prepareCall("{...}");

      I tried both bean managed transaction demarcation and container managed t.d.
      For bean managed t.d. the code of a business method looks like this:
      ...
      javax.transaction.UserTransaction ut;
      String ret="";
      ut = sessionContext.getUserTransaction();
      // start the transaction

      try
      {
      ut.begin();
      decypherCall.setString(2, encrypted);
      boolean b = decypherCall.execute();
      ret = decypherCall.getString(1);
      decypherCall.clearParameters();
      ut.commit();
      }
      catch (Exception ex)
      {
      log.error("Could not decypher CC number.",ex);
      }

      ...

      For container managed t.d. I used "NotSupported" tansaction-type and the code looks the same with the
      UserTransaction calls removed.
      Either method works only ones - after current transaction is completed I'm getting the following exception and the connection is dead. What am I doing wrong? Is such approach possible in Jboss?
      2003-07-25 16:34:20,288 INFO [org.jboss.resource.connectionmanager.CachedConnectionManager] Throwable trying to close a connection for you, please close it yourself
      java.lang.reflect.InvocationTargetException
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.resource.connectionmanager.CachedConnectionManager.closeAll(CachedConnectionManager.java:376)
      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.StatefulSessionInstanceInterceptor.invoke(StatefulSessionInstanceInterceptor.java:268)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:107)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:237)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:98)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:208)
      at org.jboss.ejb.StatefulSessionContainer.invoke(StatefulSessionContainer.java:380)
      at org.jboss.ejb.Container.invoke(Container.java:738)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:383)
      at sun.reflect.GeneratedMethodAccessor198.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
      at sun.rmi.transport.Transport$1.run(Transport.java:148)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
      at java.lang.Thread.run(Thread.java:536)
      Caused by: java.lang.IllegalArgumentException: xaRes not enlisted
      at org.jboss.tm.TxCapsule.delistResource(TxCapsule.java:595)
      at org.jboss.tm.TransactionImpl.delistResource(TransactionImpl.java:92)
      at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalConnectionEventListener.delist(LocalTxConnectionManager.java:347)
      at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalConnectionEventListener.connectionClosed(LocalTxConnectionManager.java:389)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:280)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.close(WrappedConnection.java:97)
      ... 26 more

        • 1. Re: Can I keep an open JDBC connection through the life of s
          Jon Barnett Master

          Usually it is not recommended. Since a pool of stateless session bean instances can live for a long time (unless the pool constraints cause destruction of numbers), you end up reserving n JDBC connections from the connection pool for the n stateless session beans. This detracts from the purpose of connection pooling and ultimately adds to the number of database connections you need, particularly if the ones reserved by the stateless session beans are idle for the majority of the time.

          Also by using the connection pooling, you are engaging the connection pool timeout clause that checks whether a connection has been released by the component that has requested the connection. Since these are meant to be shared, the connection pool manager checks each connection periodically, and forces a release of the connection (and committing transactions in the pipe) if it hasn't been given back within a certain time - which is what you are seeing.

          You can increase the timeout but if you do that, I would recommend using a separate pool for these special connections. You could also just establish a standard JDBC connection in your stateless session bean as you are not really benefiting from using pooled connections. Another alternative is to look at hibernate and see if it can help solve your current needs - http://hibernate.bluemars.net/.

          I guess there are some alternatives for you. Hope it is of some use.

          • 2. Re: Can I keep an open JDBC connection through the life of s
            moonrainbow Newbie

            > Usually it is not recommended. Since a pool of
            I understand it's not recommended as I understand the reasons for that. I'd like to have a straight answer though: is such strategy as I described possible in JBoss or is there a bug (feature) which prevents me from doing so or I'm missing some obscure refrence in EJB 2.0 spec which directly says I can not do such things.
            Another question: If I create a separate pool will JBoss close connections from that pool also?
            > I guess there are some alternatives for you. Hope it
            > is of some use.
            Thank you. But I'd rather have an idea of what I'm working around before starting the actual work.

            • 3. Re: Can I keep an open JDBC connection through the life of s
              moonrainbow Newbie

              Sorry, forgot to mention: the timeout is set to 5 minutes, but connection is closed almost immediately - that's why I think the cause is something else.

              • 4. Re: Can I keep an open JDBC connection through the life of s
                Jon Barnett Master

                The close-off may be a feature related to transaction management if the close off is immediate. I vaguely remember going through a debug with someone on a hibernate transaction being committed and it related to the transaction manager determining that the SLSB had completed its work, the transaction chain was complete and the in-pipe transactions for a managed connection were ready to be committed.

                There is most likely nothing that directly prohibits you in the J2EE specs from using a managed connection in such a manner. However, there might be an inference in considering the impact of EJB transaction management requirements. Since a connection pool is J2EE infrastructure the implementation for transaction management probably needs to have hooks into the container managed connections, relating to EJB transaction chain atomicity. You don't get a straight JDBC connection from the pool, but what is called a WrappedConnection for this reason.

                I hope that answer is straight enough to cover the most likely requrements for transaction management and the impact on container managed connections. So most likely my suggestion of a separate connection pool will not help you. My apologies.

                You have one viable option of a resource not under direct transaction management control - direct JDBC. I can't remember what the resolution was for the hibernate problem but I think in that instance, there was no requirement for a permanently locked connection.

                • 5. Re: Can I keep an open JDBC connection through the life of s
                  Bernd Zeitler Expert

                  JBoss is closing connections for you left open with "SpecCompliant=false" in your transaction-service.xml .
                  Try to set it to true.
                  But jonlee is right. I even don't recommend doing this. If the operation is as expensive as you think, try another way. What about a MBean for expensive queries, the EJBs are talking to. The MBean can hold everything as long as you want.

                  Greetings,
                  Frito

                  • 6. Re: Can I keep an open JDBC connection through the life of s
                    gunter zeilinger Newbie

                    I ran into the same problem, using a stateFUL session bean to execute a query in its create method, according the keyset provided by the client as method param. The client fetches data record by record using a buisness method of the session bean, which fetches the data itself - just in time - from the contained ResultSet. ResultSet, Statement and Connection are closed, if there is no further record to return, or if the client explicit cancel the query by removing the session bean.

                    This solution was motivated by
                    - the number of returned result records may be quite large, in the case of a quite unspecfic keyset.
                    - the client may decide to cancel the query after fetching only a fraction of the matching records
                    - the client itself is a server process - wrapped in a MBean Service - which fetches the results as fast it can deliver them to its connected client (typically several results per second) and will also reliable remove the session bean after all results were delivered or the client has (asynchronly) canceled the query.

                    Ok, I may invoke the JDBC related code directly from the server MBean and trash the stateful session bean to avoid to have to modified "SpecCompliant=false" to "SpecCompliant=true". But then, the question arise, is there any reason to use EJB (Session Beans) for pure (=with no/only little "business logic" involved) read-only DB access at all?

                    • 7. Re: Can I keep an open JDBC connection through the life of s
                      Bernd Zeitler Expert

                      Well, ask yourself: am I using or abusing the technology? And why should I use an application server if I don't need it or if I have to work around its architecture?
                      Sure, you can achieve many functionality by poking every configuration parameter until it works for you, but... why?

                      Greetings,
                      Frito