JBOSS connection pool caching problem

[utkarshpanwar]

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

[hariv]

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.

[utkarshpanwar]

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