2 Replies Latest reply on Jan 17, 2005 3:27 AM by Utkarsh Panwar

    JBOSS connection pool caching problem

    Utkarsh Panwar Newbie

      Hi,

      I am using Jboss 4.0 and to connect to MySQL 4.0.21 using connection pool, But here i facing problem of the data caching. Even I delete some data from the tables, it is still visible when i run the query. what kind of caching is it?

      is there any setting to remove this caching?

      Thanks in advance.
      Utkarsh

        • 1. Re: JBOSS connection pool caching problem
          Hari Velappan Newbie

          Are you using entity beans for reding and writing from your tables. Please check your commit-option If you dont need caching you can use commit-option C. If the data can be changed from other applications running outside the jboss container you should not use commit-option A. Use either commit-option C or B.

          • 2. Re: JBOSS connection pool caching problem
            Utkarsh Panwar Newbie

            Hi,

            No i am not using any Are you using entity beans.
            See the code below

            To get the connection

            private static Connection getConnectionFromPool() throws Exception {
            if (ds == null) {
            ctx = new InitialContext();
            ds = (DataSource) ctx.lookup(env.getProperty("datasource"));
            }

            Connection conn = ds.getConnection();
            if (conn == null) {
            ctx = new InitialContext();
            ds = (DataSource) ctx.lookup(env.getProperty("datasource"));
            conn = ds.getConnection();
            }
            conn.setAutoCommit(false);
            return conn;
            }

            -------------------------------

            code how i am using this connection

            public void addAEC(AEC aec) throws Exception {
            logger.debug("initiating the process for adding new AEC");

            Connection dbConn = null;
            PreparedStatement addAECPStmt = null;
            try {
            // get the db connection
            dbConn = ImpactBOMySQLDAOFactory.createConnection();



            // Prepare a statement to insert a record
            addAECPStmt = dbConn.prepareStatement(getSQLQuery(AECSQLMappings.ADD_AEC));


            // set the placeholders' values
            logger.debug("fetching aec to be added: " + aec.toString());
            addAECPStmt.setObject(1, aec.getIdentifier());
            addAECPStmt.setObject(2, aec.getAsset());
            addAECPStmt.setObject(3, aec.getExchange());
            addAECPStmt.setObject(4, aec.getCurrency());

            // set change dates
            Timestamp currentDate = new Timestamp(new Date().getTime());
            addAECPStmt.setObject(5, currentDate);
            addAECPStmt.setObject(6, currentDate);

            // set user ids for tracking
            addAECPStmt.setObject(7, aec.getCreated_by());
            addAECPStmt.setObject(8, aec.getModified_by());
            addAECPStmt.setObject(9, aec.getIs_active());

            // execute
            addAECPStmt.execute();
            logger.debug("db query executed");

            // commit if auto-commit is false
            dbConn.commit();
            logger.debug("db committed");
            } catch (SQLException sqlExcep) {
            sqlExcep.printStackTrace();
            logger.fatal("Exception Occured: " + sqlExcep.getErrorCode() + " : " + sqlExcep.getMessage());

            if (sqlExcep.getErrorCode() == 1217) {
            throw new Exception(sqlExcep.getErrorCode() + " : " + ApplicationConstants.ACCESS_VIOLATION_ERROR);
            } else if (sqlExcep.getErrorCode() > 0) {
            throw new Exception(sqlExcep.getErrorCode() + " : " + sqlExcep.getMessage());
            } else {
            throw new Exception(sqlExcep.getMessage());
            }

            } finally {
            try {
            // close all the resources
            if (addAECPStmt != null) {
            addAECPStmt.close();
            logger.debug("prepared stament closed");
            }
            if (dbConn != null) {
            dbConn.close();
            logger.debug("db connection closed");
            }
            } catch (SQLException sqlException) {
            sqlException.printStackTrace();
            logger.fatal("Exception occured while winding up: " + sqlException.getErrorCode() + " : " + sqlException.getMessage());
            // no need to do anything here
            }
            }
            }

            Thanks
            Utkarsh