11 Replies Latest reply on Jul 23, 2003 3:45 PM by Marcelo Alcantara

    Is connection close needed?

    lee_yuki2000 Newbie

      Can anyone tell me that do i need to close connection after i have execute a query. When i close my connection after the query, there is a statement stated as follows:

      [CachedConnectionManager] Successfully closed a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@695527
      java.lang.Exception: Stack Trace
      at org.jboss.resource.connectionmanager.CachedConnectionManager.closeAll(CachedConnectionManager.java:376)
      at org.jboss.resource.connectionmanager.CachedConnectionManager.popMeta
      wareObject(CachedConnectionManager.java:199)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.inv
      ke(CachedConnectionInterceptor.java:190)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInt
      rceptor.java:84)
      at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTx
      nterceptorBMT.java:144)
      at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:
      2)

      If i don't close the connection, i will not have this statement. WIll Jboss close the connection if the connection is not in used?
      I am using Jboss-3.2.1 and using oracle 9i.
      Besides that, can i know where to set the minimum and maximum connection pool? In jboss-3..0.6, i can set it at oracle-service.xml but in Jboss3.2.6 oracle-ds.xml there isn't any max and min connection pool. The following is the example for oracle-ds.xml


      <local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>

      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>x</user-name>
      y
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>



      Pls help. Thank you

        • 1. Re: Is connection close needed?
          Jon Barnett Master

          In general, you should close the statement and the connection in that order. The connection is not truly closed but merely returned to the pool. If you do not "close" the connection it will not return the connection to the pool until the bean dies or the connection times out, whichever comes first. Under the EJB spec, your bean may not be passivated until the container is assured that the transaction is completed.

          The max and min pool sizes are supported, they just aren't included in the example template you used. Look at docs/examples/jca/generic-ds.xml to see where these values should go within your own template.

          • 2. Re: Is connection close needed?
            lee_yuki2000 Newbie

            Thanks for your reply. I have error if i close the connection. If i do not close the connection everything will be fine. Once i close it, i cannot get the connection.
            Do you know why?

            [org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener] throwable from unregister connection
            java.lang.IllegalStateException: Trying to return an unknown connection1! org.jboss.resource.adapter.jdbc.WrappedConnection@49494e
            at org.jboss.resource.connectionmanager.CachedConnectionManager.unregisterConnection(CachedConnectionManager.java:264)
            at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.connectionClosed(TxConnectionManager.java:550)


            • 3. Re: Is connection close needed?
              lee_yuki2000 Newbie

              Sometimes i have this error after i have close the connection.

              java.sql.SQLException: Connection handle has been closed and is unusable
              at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:800)
              at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:218)
              at sqlj.runtime.profile.ref.JDBCProfile.prepareCachedStatement(JDBCProfile.java:334)

              Do you know why?

              • 4. Re: Is connection close needed?
                Jon Barnett Master

                Sounds like there is a physical connection problem or there is a problem completing the transaction. Have you tried accessing and using a connection from a stateless session bean? And does this problem occur for a 3.0.x implementation when you explicitly close the connection?

                • 5. Re: Is connection close needed?
                  lee_yuki2000 Newbie

                  I am using stateless bean and my transaction type is BMP.

                  <session-type>Stateless</session-type>
                  <transaction-type>Bean</transaction-type>

                  Do u know what's wrong?
                  Thank you very much

                  • 6. Re: Is connection close needed?
                    Jon Barnett Master

                    BMP doesn't really exist in your instance as this term really only applies properly to Entity beans. However, I take your meaning.

                    Perhaps if you share how you go about obtaining the connection, it might allow folks here to diagnose the issue.

                    I haven't seen this actual problem before but it smells like the underlying physical connection is perhaps being lost. I would check your Oracle logs for loss of connection.

                    Does the transaction complete? Does the data get written or read?

                    • 7. Re: Is connection close needed?
                      lee_yuki2000 Newbie

                      The reason why i cannot reuse the connection when i have close the connection is because i didn't set the connection = null. I am using the default connection.

                      I just need to know it is i have to close the connection after every sql statement? Some people say yes and some people say no. So i m a bit confused here.

                      If i need to close the connection, i need to set the connection = null instead of getting the default connection.

                      public void activate() throws NamingException, SQLException {
                      if (dataSource == null) {
                      try {
                      dataSource = DataSourceDomain.getDataSource();
                      } catch (NamingException ne) {
                      throw ne;
                      } catch (SQLException se) {
                      throw se;
                      }
                      }
                      }

                      public void initContext() throws SQLException{
                      ctx = DefaultContext.getDefaultContext();
                      if (ctx == null) {
                      try {
                      conn = dataSource.getConnection();
                      conn.setAutoCommit(false);
                      ctx = new DefaultContext(conn);
                      } catch (SQLException se) {
                      throw se;
                      }

                      DefaultContext.setDefaultContext(ctx);
                      }
                      }

                      Pls help. Thank you very much.

                      • 8. Re: Is connection close needed?
                        Jon Barnett Master

                        You don't need to close the connection after every SQL statement in the method of a stateless session bean. However, you must close it before you leave the method. Otherwise the connection will be unavailable to other requesters until the connection times out.

                        So let us look at a working routine for a stateless session bean method:
                        public void addEvent(EventEntry eventEntry)
                        {
                        DataSource dataSource = null;
                        if (genericInsert == null)
                        genericInsert = generateInsert(TABLE, ELEMENTLIST, HOLDERLIST);
                        if (eventEntry == null)
                        return;
                        // Find the JBoss pool (datasource - JNDI: OracleDS)
                        try
                        {
                        Context naming = new InitialContext();
                        dataSource = (DataSource)naming.lookup("java:/OracleDS");
                        }
                        catch(Exception e)
                        {
                        System.out.println(error + e.toString());
                        }
                        Connection connection = null;
                        if (dataSource != null)
                        try
                        {
                        connection = dataSource.getConnection();
                        }
                        catch(Exception e)
                        {
                        System.out.println(error + e.toString());
                        }
                        }
                        // Use the connection if we successfully got one
                        if (connection != null)
                        {
                        // Write an event using the connection
                        PreparedStatement insertEvent = null;
                        try
                        {
                        insertEvent = connection.prepareStatement(genericInsert);
                        insertEvent.setInt(1, eventEntry.getSeverity());
                        insertEvent.setString(2, eventEntry.getSystem().trim());
                        insertEvent.setString(3, eventEntry.getModule().trim());
                        insertEvent.executeUpdate();
                        }
                        catch(Exception e)
                        {
                        System.out.print("Event Logger Error: ");
                        System.out.println(e.toString());
                        System.out.print("Sql: ");
                        System.out.println(genericInsert);
                        generateMessage(eventEntry);
                        }
                        try
                        {
                        if (insertEvent != null)
                        insertEvent.close();
                        }
                        catch(Exception e)
                        {
                        System.out.print("Event Logger Error: ");
                        System.out.println(e.toString());
                        }

                        //... do some other things with the connection

                        // Release connection back to pool now we have finished
                        try
                        {
                        if (connection != null)
                        connection.close();
                        }
                        catch(Exception e)
                        {
                        System.out.print("Event Logger Error: ");
                        System.out.println(e.toString());
                        }
                        }
                        else
                        {
                        System.out.print("Event Logger Error: Could not create connection");
                        generateMessage(eventEntry);
                        }
                        }

                        • 9. Re: Is connection close needed?
                          lee_yuki2000 Newbie

                          Jonlee,
                          Thank you very much

                          • 10. Re: Is connection close needed?
                            kar2000 Newbie

                            Well, I have a doubt in this regard - is there any means of finding in the jboss 3.0.7 server side and not in the Oracle 8 database server side,

                            how many connections are there in the pool,

                            how many connections are in use and

                            how many connections are being returned at a given point of time?

                            • 11. Re: Is connection close needed?
                              Marcelo Alcantara Newbie

                              I have the same doubt as you.

                              Maralc