1 Reply Latest reply on Feb 22, 2005 8:31 AM by Matthias von Arx

    closing connection, why must be done?

    Marco Vanoli Newbie

      On a book i read the following example (at the bottom). I imported it in a jar than run with jboss.

      The error on interroga(String str) method is:


      ON CLIENT
      java.sql.SQLException: Connection handle has been closed and is unusable
      ON SERVER
      [CachedConnectionManager] Closing a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@594680

      The problem is the that is in the apriConnessione() i got a call for make a db connection. This one is activated fist from ejbActivate(); then jboss close automatically connection without a ejbPassivate(). Then i enter in Interroga() method and the connection is no more avaible giving me exception.

      I read the FAQ , expecially http://www.jboss.org/wiki/Wiki.jsp?page=WhatDoesTheMessageDoYourOwnHousekeepingMean
      and so i correct the exercise putting alla connection statemente in the method interroga():
      con = ds.getConnection();
      &
      if(rs != null) rs.close();
      if(ps != null) ps.close();
      if(con != null) con.close();
      


      In this case the program has no error.
      BUT
      I do not understand the beaviour of jboss because i studied that in ejbPassivate / ejbActivate i must close connections; not in every method!
      Reading the FAQ i understand that i must open & close connection in every method.
      Can you explain me why? .. or what i misunderstand :)
      thankyou, Bye

      package StatefulDb;
      
      import javax.ejb.*;
      import java.sql.*;
      import javax.sql.*;
      import javax.naming.*;
      
      public class StatefulJdbcBean implements SessionBean {
      
       private SessionContext ctx;
       private DataSource ds = null;
      
       transient private Connection con = null;
       transient private PreparedStatement ps = null;
       transient private ResultSet rs = null;
      
       private String cognome = null;
       private String nome = null;
       private String p_iva = null;
       private String cod_fiscale = null;
      
       public StatefulJdbcBean() {}
      
       public void setSessionContext(SessionContext c) {
       ctx=c;
       }
      
       public void ejbCreate(){
       InitialContext initCtx = null;
       try {
       initCtx = new InitialContext();
       ds = (javax.sql.DataSource)
       initCtx.lookup("java:comp/env/jdbc/DSCliente");
       } catch(NamingException ne) {
       System.out.println("UNABLE to get a connection from
       [java:comp/env/jdbc/DSCliente]");
       throw new EJBException("Unable to get a connection
       from [java:comp/env/jdbc/DSCliente]");
       } finally {
       try {
       if(initCtx != null) initCtx.close();
       } catch(NamingException ne) {
       System.out.println("Error closing context: " + ne);
       throw new EJBException("Error closing context " + ne);
       }
       }
       }
      
       public void ejbRemove(){
       try{
       chiudiConnessione();
       }catch(SQLException ex){
       ex.printStackTrace();
       throw new EJBException("Error closing resources " + ex);
       }
       }
      
       public void ejbPassivate(){
       try{
       chiudiConnessione();
       }catch(SQLException ex){
       ex.printStackTrace();
       throw new EJBException("Error closing resources " + ex);
       }
       }
      
       public void ejbActivate(){
       try{
       apriConnessione();
       }catch(SQLException ex){
       ex.printStackTrace();
       throw new EJBException("Error in opening a connection " + ex);
       }
       }
      
       public void apriConnessione() throws SQLException {
       con = ds.getConnection();
       }
      
       public void chiudiConnessione() throws SQLException {
       if(rs != null) rs.close();
       if(ps != null) ps.close();
       if(con != null) con.close();
       }
      
       public void interroga(String str) throws SQLException {
       ps = con.prepareStatement(str);
       rs = ps.executeQuery();
       if (rs.next()){
       cognome=rs.getString("COGNOME");
       nome=rs.getString("NOME");
       p_iva=rs.getString("P_IVA");
       cod_fiscale=rs.getString("COD_FISCALE");
       }
       }
      
       public String getCognome(){
       return cognome;
       }
      
       public String getNome(){
       return nome;
       }
      
       public String getP_iva(){
       return p_iva;
       }
      
       public String getCod_fiscale(){
       return cod_fiscale;
       }
      }
      
      
      
      


        • 1. Re: closing connection, why must be done?
          Matthias von Arx Newbie

          buongiorno

          I had the same problem. My connector does not support transactions. The connector should be used indirectly through a (stateful) session fascade. This session fascade has the following (remote) interface:

          public interface ResourceFascade extends EJBObject
          {
           public void openConnection() throws RemoteException, MyResourceException;
           public void close() throws RemoteException, MyResourceException;
           public MyResultSet search(String searchbase, String filter) throws RemoteException, MyResourceException;
           //...
          }
          


          This session beans <trans-attribute> was NotSupported. When the openConnection() method was called the connection was closed automatically by the CachedConnectionManager.

          I have found two solutions for this problem:
          1) Set the "Debug" attribute in deploy\jbossjca-service.xml to false
          2) Using transactions. In my case the ResourceFascade session bean requires that the <trans-attribute> was Mandatory. In this case your connector should probably support transactions (I'm not sure)...

          Ciao

          Matthias