1 Reply Latest reply on Aug 17, 2005 3:09 PM by Adrian Brock

    Problems to return a connection to ConnectionPool

    Alexandre Barbosa Newbie

      I've searched through the forums, and I could see that this problem was faced by older versions of JBoss. I'm using the version 4.0.2

      I've built an EJB to insert an entity to a table in our Oracle DB (version 9.2.0.4), and it's working fine, including the correct behaviour for close connections (returning the connection to ConnectionPool).

      Now I'm building another EJB that performs a select on a table from this same DB. But in this case, I can only perform as many calls as the number of maximum connections setted for my datasource.

      The only difference from both cases is that in the first, all work is done by non-EJB classes in my application (the EJB only calls a method from a business class), while in the second I'm doing all work in the EJB class.

      Below is the oracle-ds.xml being used by my application (deployed on default/deploy)

      <datasources>
       <local-tx-datasource>
       <jndi-name>OracleDS</jndi-name>
       <use-java-context>false</use-java-context>
      
       <connection-url>jdbc:oracle:oci:@teste</connection-url>
      
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <connection-property name="autoCommit">false</connection-property>
      
       <user-name>xxxxx</user-name>
       <password>yyyyyy</password>
      
       <min-pool-size>1</min-pool-size>
       <max-pool-size>3</max-pool-size>
       <idle-timeout-minutes>1</idle-timeout-minutes>
      
       <track-statements/>
      
       </local-tx-datasource>
      </datasources>
      


      Next, the way I've configured the beans (in ejb-jar.xml)
       <session>
       <ejb-name>Login</ejb-name>
       <home>myapp.auth.deploy.impl.LoginHome</home>
       <remote>myapp.auth.deploy.impl.Login</remote>
       <ejb-class>myapp.auth.deploy.impl.LoginBean</ejb-class>
       <session-type>Stateless</session-type>
       <transaction-type>Bean</transaction-type>
       </session>
      
       <!-- EJBs da aplicacao -->
       <session>
       <ejb-name>Organism</ejb-name>
       <home>myapp.clustering.deploy.impl.OrganismHome</home>
       <remote>myapp.clustering.deploy.impl.Organism</remote>
       <ejb-class>myapp.clustering.deploy.impl.OrganismBean</ejb-class>
       <session-type>Stateless</session-type>
       <transaction-type>Bean</transaction-type>
       </session>
      


      And now, the way I'm handling the connection in both cases;

      1. the working bean
       Connection conn = null;
      
       try {
       conn = ds.getConnection();
       conn.setAutoCommit(false);
       IOrganismDAO organismDAO = OrganismDAO.getInstance();
       organismDAO.saveOrganism(conn, organism);
       conn.commit();
       } catch (SQLException sqle) {
       if (logger.isErrorEnabled())
       logger.error("Erro apos gravacao de organismo: "
       + sqle.getMessage());
      
       throw new BOException(sqle.getMessage(), sqle.getCause());
       } finally {
       try {
       conn.rollback();
       conn.close();
       } catch (Throwable t) {
       if (logger.isWarnEnabled()) {
       logger.warn("Erro ao fechar conexao com banco de dados\n"
       + t.getMessage() + "\n");
       }
       }
       }
      


      2. the not working
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       try {
       conn = ds.getConnection();
       pstmt = conn
       .prepareStatement("SELECT u.id_pessoa" + " FROM usuario u"
       + " WHERE u.senha = ?");
      
       pstmt.setString(1, password);
      
       rs = pstmt.executeQuery();
      
       if (rs.next()) {
       userID = rs.getInt("ID_PESSOA");
       }
      
       // Fechando objetos de busca
       rs.close();
       pstmt.close();
       } catch (SQLException sqle) {
       throw new RemoteException(
       "Erro ao buscar dados de login no banco de dados: " +
       sqle.getMessage());
       } finally {
       try {
       conn.close();
       } catch (Exception e) {
       System.out.println("Erro ao fechar conexao");
       }
       }
      


      What I've seen is that my application can't return the connection to the ConnectionPool in the second case, but does in the first, allowing the connection reuse. The error I get is the following (extracted from my log)

      No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))
      


      Can someone give me any hint on this?

      Thanks,
      Alexandre