asked on
OCI sessions do not close
Hi, I have a java application that runs on websphere 6.1
My problem is that every time I perform a connection to the oracle database, a session is left open on the DB.
In the oracle console I see many inactive sessions running program java.exe
I tried setting CONNPOOL_TIMEOUT but the connections stay open even after the time I specified. The manual says that CONNPOOL_TIMEOUT works only on physical sessions.
I don't know Java very well and the program was made by another person, but as far as I can see there is nowhere in the app where connections are closed with the .close() method.
Also, if I stop the websphere service, all the sessions opened by the program are closed.
Thanks for any help
My problem is that every time I perform a connection to the oracle database, a session is left open on the DB.
In the oracle console I see many inactive sessions running program java.exe
I tried setting CONNPOOL_TIMEOUT but the connections stay open even after the time I specified. The manual says that CONNPOOL_TIMEOUT works only on physical sessions.
I don't know Java very well and the program was made by another person, but as far as I can see there is nowhere in the app where connections are closed with the .close() method.
Also, if I stop the websphere service, all the sessions opened by the program are closed.
Thanks for any help
final class OracleConnectionPoolingFactory {
private static Connection pool = null;
private OracleConnectionPoolingFactory() {
}
static synchronized Connection getInstance(String connString, String userConnName, String userConnPassword) throws SQLException, ClassNotFoundException {
if (pool == null || pool.isClosed()) {
Class.forName("oracle.jdbc.driver.OracleDriver");
OracleOCIConnectionPool ocpds = new OracleOCIConnectionPool();
ocpds.setURL(connString);
ocpds.setUser(userConnName);
ocpds.setPassword(userConnPassword);
java.util.Properties p = new java.util.Properties();
p.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT,"1");
p.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT,"20");
p.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");
p.put(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "5");
ocpds.setPoolConfig(p);
System.out.println("Stringa di connessione: " + connString + " User: " + userConnName + " Password:" + userConnPassword);
OracleConnectionPoolingFactory.pool = ocpds.getConnection();
}
return pool;
}
JavaOracle Database
Last Comment
uilli
ASKER
I'm sorry, the program does actually close the connections, but somehow they are still open as inactive ones.
I forgot to mention I'm using the OCI driver to connect to the database. The problem started either with the change of the drver from thin to OCI or the passage from tomcat to websphere. I picked up the program after these changes and I believe it was working fine before them.
In the attached code below, I see that in the finally part there is a rs.close();
do I need a conn.close(); as well?
I forgot to mention I'm using the OCI driver to connect to the database. The problem started either with the change of the drver from thin to OCI or the passage from tomcat to websphere. I picked up the program after these changes and I believe it was working fine before them.
In the attached code below, I see that in the finally part there is a rs.close();
do I need a conn.close(); as well?
public List<UserBean> findUserByLogin(UserBean user) throws DAOException {
Connection conn = null;
Statement stm = null;
ResultSet rs = null;
List<UserBean> out = new LinkedList<UserBean>();
try {
conn = OracleConnectionPoolingFactory.getInstance(connString,
userName, userPassword);
stm = conn.createStatement();
String query = SELECT + " WHERE upper(login) = '" +
user.getLogin().toUpperCase() + "' ";
rs = stm.executeQuery(query);
while(rs.next()){
UserBean tmp = new UserBean();
tmp.setIdUtente(rs.getInt("idUtente"));
tmp.setLogin(rs.getString("login"));
tmp.setPassword(rs.getString("pwd"));
tmp.setName(rs.getString("anagrafica"));
tmp.setRole(rs.getString("ruolo"));
out.add(tmp);
}
} catch (SQLException e) {
throw new DAOException(e);
} catch (ClassNotFoundException e) {
throw new DAOException(e);
} catch (Exception e) {
throw new DAOException(e);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e){
}
}
if (stm != null){
try {
stm.close();
} catch(SQLException e){
}
}
if (conn != null){
try {
conn.close();
} catch(SQLException e){
}
}
}
return out;
}
You perhaps ought to use a profiler on the app
rs.close only closes the record set. You need the conn.close to close the database connection.
why are you using the oci driver and its pool?
ASKER
OCI driver required by the company the program is made for.
Looking into the program I found out that conn is being closed but doesn't actually close the connection.
From what I've understood oci pooling creates a physical connection and uses it to create many logical connections (sessions in oracle). The conn.close() however doesn't seem to close the logical connection, no idea why.
Also, the application never closes the physical connection, any idea where should I close it?
Looking into the program I found out that conn is being closed but doesn't actually close the connection.
From what I've understood oci pooling creates a physical connection and uses it to create many logical connections (sessions in oracle). The conn.close() however doesn't seem to close the logical connection, no idea why.
Also, the application never closes the physical connection, any idea where should I close it?
static synchronized Connection getInstance(String connString, String userConnName, String userConnPassword) throws SQLException, ClassNotFoundException {
if (pool == null || pool.isClosed()) {
Class.forName("oracle.jdbc.driver.OracleDriver");
OracleOCIConnectionPool ocpds = new OracleOCIConnectionPool();
ocpds.setURL(connString);
ocpds.setUser(userConnName);
ocpds.setPassword(userConnPassword);
java.util.Properties p = new java.util.Properties();
p.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT,"1");
p.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT,"20");
p.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");
p.put(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "5");
ocpds.setPoolConfig(p);
System.out.println("Stringa di connessione: " + connString + " User: " + userConnName + " Password:" + userConnPassword);
OracleConnectionPoolingFactory.pool = ocpds.getConnection();
}
return pool;
}
no it won't close the connection, thats the job of the pool
closeing just returns the connection to the pool
closeing just returns the connection to the pool
That code you posted looks wrong. You should be returning type OracleOCIConnection for your connection. See
http://www.oracle.com.cn/other/9ionlinedoc/java.920/a96654/oci_func.htm#1014118
http://www.oracle.com.cn/other/9ionlinedoc/java.920/a96654/oci_func.htm#1014118
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
that's indeed the problem. I just found it out debugging the program.
The logical connection gets actually closed correctly, but the problem is that a new pool is created everytime because it enters the if clause as pool.isclosed() is always true
thank you very much for your help
The logical connection gets actually closed correctly, but the problem is that a new pool is created everytime because it enters the if clause as pool.isclosed() is always true
thank you very much for your help
That would be the problem then ;-) They should be closed in a finally block when finished with