1 Reply Latest reply on Jun 5, 2006 12:59 PM by Martin Hermosilla

    Jboss.jca.ManagedConnectionPool Using PostgreSQL JBoss 4.0.0

    Martin Hermosilla Newbie

      Hi all,
      I have a Jboss 4.0.0(200409200419) Zion, a PostgreSQL version 7.4.6
      So i'm using NoTX dataSource for my webservices running on /ws4ee
      this is my WS-ds.xml

      <datasources>
       <no-tx-datasource>
       <jndi-name>jdbc/WS_ds</jndi-name>
       <connection-url>jdbc:postgresql://localhost:5432/DB_XXX</connection-url>
       <driver-class>org.postgresql.jdbc2.optional.PoolingDataSource</driver-class>
       <user-name>db_usr</user-name>
       <password>db_pass</password>
       <min-pool-size>0</min-pool-size>
       <max-pool-size>100</max-pool-size>
       </no-tx-datasource>
      </datasources>
      

      well i had to increase the max pool size because de AvailableConnectionCount decreases without release of connections.. so never i had available connections: y DB Connection Provider object is :
      public class DBConnProvider {
       private static final long serialVersionUID = 2345676785536765474L;
      
       private static final boolean DEBUG = false;
      
       protected static javax.naming.Context envCtx;
      
       protected static javax.sql.DataSource datasource;
      
       private static transient final Log log = LogFactory
       .getLog(DBConnProvider.class);
      
       /** Creates a new instance of DBConnProvider */
       public DBConnProvider() {
       datasource = null;
       envCtx = null;
       }
      
       // public static java.util.Vector vecConnections = new java.util.Vector();
      
       public static java.sql.Connection getConnection()
       throws java.sql.SQLException {
       return DBConnProvider.getConnection("WS_ds");
       }
      
       /**
       * @params dsDB String Nombre de la Base de Datos (u origen de datos)
       * @return datasource.getConnection() una conexion a la base de datos
       */
       private static synchronized java.sql.Connection getConnection(String dsDB)
       throws java.sql.SQLException {
       if (DEBUG) {
       log.info("Obteniendo DataSource: jdbc/" + dsDB);
       }
      
       if (datasource == null) {
       try {
       envCtx = (javax.naming.Context) (new javax.naming.InitialContext())
       .lookup("java:comp/env");
       datasource = (javax.sql.DataSource) envCtx.lookup("jdbc/"
       + dsDB);
       datasource.setLogWriter(new java.io.PrintWriter(System.out));
       envCtx.close();
       log.info("DataSource: java:comp/env/jdbc/" + dsDB);
       } catch (Exception e) {
       log.error("(getConnection) " + e.getMessage() + ":" + dsDB);
       datasource = null;
       }
       }
      
       if (datasource == null) {
       try {
       envCtx = (javax.naming.Context) (new javax.naming.InitialContext())
       .lookup("java:jdbc");
       datasource = (javax.sql.DataSource) envCtx.lookup(dsDB);
       datasource.setLogWriter(new java.io.PrintWriter(System.out));
       envCtx.close();
       log.info("DataSource: java:jdbc/" + dsDB);
       } catch (Exception e) {
       log.error("(getConnection) " + e.getMessage() + ":" + dsDB);
       datasource = null;
       }
       }
      
       if (datasource == null) {
       try {
       envCtx = (javax.naming.Context) new javax.naming.InitialContext();
       datasource = (javax.sql.DataSource) envCtx.lookup("java:jdbc/"
       + dsDB);
       datasource.setLogWriter(new java.io.PrintWriter(System.out));
       envCtx.close();
       log.info("DataSource: java:jdbc/" + dsDB);
       } catch (Exception e) {
       log.error("(getConnection) " + e.getMessage() + ":" + dsDB);
       datasource = null;
       }
       }
      
       java.sql.Connection conn = datasource.getConnection();
      
       /*
       * Se elimina pues 1.- AutoCommit, You cannot set autocommit during a
       * managed transaction! 2.- ResultSet holdability no implementado, This
       * method is not yet implemented.
       *
       * try { conn.setAutoCommit(false); } catch(java.sql.SQLException sqle) {
       * log.warn("(getConnection) AutoCommit, " + sqle.getMessage()); }
       *
       * try {
       * conn.setHoldability(java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT); }
       * catch(java.lang.Exception e) { log.warn("(getConnection) ResultSet
       * holdability no implementado, " + e.getMessage()); }
       */
       return conn;
       }
      
      }
      

      Can some one tell to me what is happening.. all my connections are closed at the end of use..

      Martin Hermosilla