Link to home
Start Free TrialLog in
Avatar of uilli
uilli

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
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;
	}

Open in new window

Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

>> but as far as I can see there is nowhere in the app where connections are closed with the .close() method.

That would be the problem then ;-) They should be closed in a finally block when finished with
Avatar of uilli
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?

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;
	}

Open in new window

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?

Avatar of uilli

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?
        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;
        }

Open in new window

no it won't close the connection, thats the job of 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
ASKER CERTIFIED SOLUTION
Avatar of Jim Cakalic
Jim Cakalic
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of uilli

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