This discussion is archived
11 Replies Latest reply: Oct 4, 2012 10:49 AM by dsurber RSS

Database connection not closed inspite of close statements

Rajan.Panchal Newbie
Currently Being Moderated
I am facing a problem in closing database connections. I am getting the below error:

==================================================================================

267137 [http-8080-Processor23] FATAL com.abc.r2.dwrclasses.DBUtilities - Failed to get a connection from DataSource JNDI/TPXSRUDB
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at com.abc.r2.dwrclasses.DBUtilities.getNativeConnection(DBUtilities.java:110)
at com.abc.r2.dwrclasses.ProcedureCaller.callProcedure(ProcedureCaller.java:81)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:740)
at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:744)
at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:593)
at org.directwebremoting.dwrp.BaseCallHandler.handle(BaseCallHandler.java:90)
at org.directwebremoting.servlet.UrlProcessor.handle(UrlProcessor.java:120)
at org.directwebremoting.servlet.DwrServlet.doPost(DwrServlet.java:141)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.oracle.determinations.web.platform.util.CharsetFilter.doFilter(CharsetFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:756)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
... 42 more


I have written a DBUtilities.java class which gets the connection using JNDI and returns the connection object. This connection object is then used in other java classes for quering database (calling procedures) and then closed when done. However, after some amount of successful connection, its giving the above error. The code written in my class is given below. Can anyone please let me know whats wrong in the code or is there a problem with the database. I am using oracle 10g. Please note I am doing connection.close() in the class in which I am using the connection


DBUtilities.java
==============
package com.abc.r2.dwrclasses;


/*
* Utilites.java
*
* Created on 08 July 2004, 11:55
*/



import java.sql.*;

import javax.sql.*;
import javax.naming.*;

import java.util.*;

import org.apache.log4j.Logger;
import org.apache.log4j.NDC;
import org.apache.commons.dbutils.DbUtils;

import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;


/**
* Abstract class containing useful database methods.
* @author Rajan Panchal
*/
public abstract class DBUtilities {

private static final Logger log = Logger.getLogger(DBUtilities.class);

private static Context initCtx; // Initial context
private static Context envCtx; // Environment context
static ResourceBundle rb;
static String appServer;
DataSource ds;
// Only get the JNDI context once, it's expensive.
/* Get the JNDI context depending upon the configuration stored in appconfiguration.properties file in configuration folder */
static {
try {
rb = ResourceBundle.getBundle("configuration.queries");
appServer = rb.getString("APP_SERVER");
log.debug("Application Server: "+appServer);
if(appServer.equalsIgnoreCase("TOMCAT")){       
initCtx = new InitialContext();
envCtx = (Context)initCtx.lookup("java:/comp/env");
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
Hashtable env = new Hashtable();
env.put("java.naming.factory.initial","com.ibm.websphere.naming.WsnInitialContextFactory");
System.out.println("INITIAL_CONTEXT_FACTORY: com.ibm.websphere.naming.WsnInitialContextFactory");
initCtx = new InitialContext(env);
}
} catch (javax.naming.NamingException e) {
log.fatal("Failed to get JNDI context on "+appServer, e);
throw new RuntimeException(e);
}
}

/**
* Get a JDBC connection
* @param jndiName the JNDI name of the datasource
* @return a Connection to the datasource
*/
public static Connection getConnection(String jndiName) {
int maxtry = 10;
int DBConnectTry = 0;
while( DBConnectTry++ < maxtry){
log.info("trying to connect:"+DBConnectTry);
try {
if(appServer.equalsIgnoreCase("TOMCAT")){              
DataSource ds = (DataSource)envCtx.lookup(jndiName);
log.debug("Returning connection object for "+appServer);
return ds.getConnection();
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
DataSource ds = (DataSource)initCtx.lookup(jndiName);
log.debug("Returning connection object for "+appServer);
return ds.getConnection();
}
} catch (javax.naming.NamingException e) {
log.fatal("JNDI lookup failed for DataSource "+jndiName, e);
throw new RuntimeException(e);
} catch (SQLException e) {
log.fatal("Failed to get a connection from DataSource "+jndiName, e);
log.info("Wait for 1 sec...");
Long WaitTime = System.currentTimeMillis()+1000L;
while(System.currentTimeMillis()<WaitTime){

}
log.info("Wait Over retrying...");
}
}
return null;

}
/* This method returns the native connection for executing the pl/sql procedure with array descriptors.*/
public static Connection getNativeConnection(String jndiName) {
int maxtry = 10;
int DBConnectTry = 0;
Connection conn;
while( DBConnectTry++ < maxtry){
log.info("trying to connect:"+DBConnectTry);
try {
if(appServer.equalsIgnoreCase("TOMCAT")){
DataSource ds = (DataSource)envCtx.lookup(jndiName);
conn = ds.getConnection();
if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
log.debug("detected apache commons dbcp datasource");
conn = ((org.apache.commons.dbcp.DelegatingConnection) conn).getInnermostDelegate();
}
return conn;
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
DataSource ds = (DataSource)initCtx.lookup(jndiName);
conn = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)ds.getConnection());
return conn;
}
} catch (javax.naming.NamingException e) {
log.fatal("JNDI lookup failed for DataSource "+jndiName, e);
throw new RuntimeException(e);
} catch (SQLException e) {
log.fatal("Failed to get a connection from DataSource "+jndiName, e);
log.info("Wait for 1 sec...");
Long WaitTime = System.currentTimeMillis()+1000L;
while(System.currentTimeMillis()<WaitTime){

}
log.info("Wait Over retrying...");
}
}
return null;

}






}

Edited by: RP on Oct 3, 2012 10:49 AM
  • 1. Re: Database connection not closed inspite of close statements
    rp0428 Guru
    Currently Being Moderated
    >
    I am facing a problem in closing database connections.
    >
    Looks like you are having a problem 'getting' a connection; there are no more connections in the pool.
    >
    org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
    >
    You didn't post the lines of trace that point to your code.
  • 2. Re: Database connection not closed inspite of close statements
    939520 Explorer
    Currently Being Moderated
    I Didn't follow your code too well, but from what I've seen, here are some suggestions:

    You get a datasource once at the start of your application and assign it to a member variable such as
    'private static void Datasource datasource' and keep it in existance until your application shuts down (even if its on for weeks). You do not instantiate multiple datasource objects. If you were to do so, each one probably creates a minimum number of connections in its pool. Multiply that number by how many datasource objects you create and you will see why you run out of connections.

    In each function you need a connection, get a connection (local variable), use it, and close it all as quickly as possible (so you don't run out of connections). Do so within a try/catch/finally block in the same function. A bad design is where you hold onto a connection such as for each user logged in.

    The connection is from a connection pool. When you get it and close it, you are really just getting it and returning it from the connection pool for reuse so there is no performance penalty to get and close it countless times. There is more to all this that you can read from various on-line articles such as on JDBC.
  • 3. Re: Database connection not closed inspite of close statements
    Rajan.Panchal Newbie
    Currently Being Moderated
    Hi, The error points to the below line number:
    at com.abc.r2.dwrclasses.DBUtilities.getNativeConnection(DBUtilities.java:110)

    We are getting the connection using the below lines :

    conn = DBUtilities.getNativeConnection(rb.getString("JNDITPXSRT"));

    and closing the connection in finally block as well as after retrieving resultset.

    conn.close();
  • 4. Re: Database connection not closed inspite of close statements
    Tolls Journeyer
    Currently Being Moderated
    if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) { 
       log.debug("detected apache commons dbcp datasource"); 
       conn = ((org.apache.commons.dbcp.DelegatingConnection) conn).getInnermostDelegate(); 
    }
    return conn;
    }else if(appServer.equalsIgnoreCase("WEBSPHERE")){
       DataSource ds = (DataSource)initCtx.lookup(jndiName);
       conn = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)ds.getConnection()); 
       return conn;
    }
    Am I correct in assuming you are not using the pooled connection class, but are going straight to the underlying connection?
    Because it's no surprise your pool is running out, as it is never informed that the connection is closed (ie can be returned to the pool).
  • 5. Re: Database connection not closed inspite of close statements
    Rajan.Panchal Newbie
    Currently Being Moderated
    Hi,
    Thanks Tolls. Yes I am getting the underlying connection as ArrayDescriptor (Used to pass array to stored procedures) does not work without underlying connection object. Do you know How can I resolve this issue? How can I inform pool that the connection is closed?

    Thanks,
    Rajan
  • 6. Re: Database connection not closed inspite of close statements
    EJP Guru
    Currently Being Moderated
    Why are you doing all this stuff? What difference does it make to you what kind of Connection you get? Just get it, the easy way, then close it, the easy way. Everything else should be handled for you by the connection pool. By going behind its back you are just getting in the way.
  • 7. Re: Database connection not closed inspite of close statements
    Tolls Journeyer
    Currently Being Moderated
    RP wrote:
    Hi,
    Thanks Tolls. Yes I am getting the underlying connection as ArrayDescriptor (Used to pass array to stored procedures) does not work without underlying connection object. Do you know How can I resolve this issue? How can I inform pool that the connection is closed?

    Thanks,
    Rajan
    Then I suspect you ought to be using an OracleDataSource?
    Which probably (at least from some past experience with DBCP) means using a different pooling system.

    You want something that returns an OracleConnection.

    As EJP says, all that stuff above is simply getting in the way.
  • 8. Re: Database connection not closed inspite of close statements
    Rajan.Panchal Newbie
    Currently Being Moderated
    @EJB: I have to do all this so that I can pass connection object to ArrayDescriptor.
    example:
    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TP_STRING_ARRAY_TABLE", conn );
    ARRAY arrayToPass = new ARRAY( descriptor, conn, pArray);

    I have to pass native connection object so that an array of strings can be passed to stored procedure from java.
    and whatever, type casting I am doing,it is to ensure compatibility between tomcat and Websphere servers. Both has different way to get the underlying connection object.


    @Tolls: You mean I should use OracleDataSource instead of java.sql.Datasource? Any Idea how it can be used?

    Edited by: RP on Oct 4, 2012 4:38 AM
  • 9. Re: Database connection not closed inspite of close statements
    Tolls Journeyer
    Currently Being Moderated
    RP wrote:
    @Tolls: You mean I should use OracleDataSource instead of java.sql.Datasource? Any Idea how it can be used?

    Edited by: RP on Oct 4, 2012 4:38 AM
    Your google-fu is as good as mine, and even if it isn't it's good practice for you.

    I'm sure there's a lot of documentation on it all somewhere round here on Oracle.com.
  • 10. Re: Database connection not closed inspite of close statements
    Joe Weinstein Expert
    Currently Being Moderated
    Hi. Let's assume your connection-using code is written to always
    close the connection ASAP, without fail, and it's thread-safe
    so multiple threads can never accidentally overwrite each others
    connection reference, leaking connection objects...
    The trouble may be that when you unwrap the connection
    object from the pool's wrapper via your getNativeConnection()
    code (not shown), your connection-closing call is likely closing
    the real DBMS connection object out from under the pool, and
    you are not closing the pool's wrapper, which is the only way
    the pool has of knowing that you're done with the real connection.

    Whatever the pool provides you initially is what you have to close.

    HTH,
    Joe
  • 11. Re: Database connection not closed inspite of close statements
    dsurber Explorer
    Currently Being Moderated
    Don't call the ARRAY constructor directly. Use OracleConnection.createARRAY(String, Object[]) instead. This avoids having to unwrap the connection in order to pass it to the constructor. It does assume that the connection wrapper exposes Oracle specific methods.

    Calling the various constructors in oracle.sql is not good practice. It is strongly discouraged in 11 and will likely be desupported in some future release. Use the createFoo methods in Connection instead.

    There is a JDBC standard method java.sql.Connection.createArrayOf(String, Object[]). The Oracle JDBC driver does not and cannot support this method. ANSI SQL specifies that array types be anonymous and be described by the element type. Oracle SQL does not support anonymous array types. The string argument to the createArrayOf method is the name of the element type. The String argument to the Oracle extension method createARRAY is the name of the array type itself, not the element type.

    Starting in 11.2.0.5.0 you should use createOracleArray instead of createARRAY. This will completely eliminate the need to use oracle.sql.ARRAY.

    java.sql.Array a = oracleConn.createOracleArray(myArrayTypeName, myElements);

    Unfortunately this is still (of necessity) an Oracle proprietary method. Not much we can do about that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points