OracleOCIConnectionPool problems

[l8zer]

Are company uses the OracleOCIConnectionPool bundled with 9i client for enhanced connection pooling (user proxying, etc.)

We have a servlet that initializes our pool and binds it to JNDI. All works well except that Oracle session's are not beeing released (sessions are kept inactive) until we shutdown the JBoss (3.03) instance. This is a problem because eventually the maximum process allowed by our DB will be reached and we no longer will be able to retreive a connection.

I tried our app under Oracle 9iAS, and sessions are being release fine.

here is a simple code sniplet of what we are doing. And yes the connections are being closed.

try {
Properties props = new Properties( );
props.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT,connPoolMinLimit);
props.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT,connPoolMaxLimit);
props.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT,connPoolIncrement);
props.put(OracleOCIConnectionPool.CONNPOOL_TIMEOUT,connPoolTimeout);
props.put(OracleOCIConnectionPool.CONNPOOL_NOWAIT,connPoolNowait);

cPool = new OracleOCIConnectionPool(userName,
passWord,
jdbcUrl,
props);
cPool.setPoolConfig(props);


System.out.println("isCreated? " + cPool.isPoolCreated());

System.out.println("1 Active size: " + cPool.getActiveSize());
System.out.println("1 Pool size: " + cPool.getPoolSize());

InitialContext ctx = new InitialContext();
ctx.rebind(ociDataSource,cPool);

}
catch (Exception ex) {
ex.printStackTrace();
}
OracleOCIConnection conn = null;
try {
InitialContext ctx = new InitialContext();
OracleOCIConnectionPool apool = (OracleOCIConnectionPool)ctx.lookup(ociDataSource);



conn = (OracleOCIConnection )apool.getConnection("webtos6", "webtos6");

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT user from dual");
while(rs.next()) {
System.out.println(rs.getString("user"));

}
}
catch (Exception ex) {
ex.printStackTrace();
}
finally{
try{
System.out.println("closing the connection");
conn.close();
conn = null;
}
catch(Exception e){}
}

Thanks in advance!
Dan

[dwong]

I have similar problem. All the connection used in the entity beans are getting from the pool. Eventually, run out of processes. I am using Oracle8i and JBoss 3.0.3. It is a headache problem as I need to restart JBoss frequently.

It is actually buring issue. Anyone has solutions / directions.

[draftdog]

hi,

i see you are experiencing the same problems I was having since some time, finally I found a solution.

The Javadoc for the 'Connection' interface says that calling the close() method will release its JDBC resources, but I would not trust this too much since the implementation behind will be Oracle (if you specify to use the OracleDriver).

Anyway, in your code, I would explicitely close() the ResultSet and Statement instances (I even set them to 'null'). And then see what happens. In my case this solved two issues: the problem with "maximum open cursors reached", and a strange "timeout" problem on the connection.

Anyway, I am not an expert, but my guess is that your connection is garbage collected while its native resources (you are using OCI) are not properly finalized through the JDBC layer. I once checked the Oracle pages and read somewhere these objects are not finalized as expected, meaning the developer needs to close() them explicitely.

Just give it a shot, it might do the trick :)

And something else, for people using the 'thin' Oracle JDBC client, when you move your JBoss App into a secure environment, say behind some firewalls etc... you might risk to have problems where connections time out, are lost, or are simple closed for no apparent reason. The solution is to use the OCI client and call the native libraries (at least this worked for me).



cheers