Steve, thanks for the reply.

   I can see that you refer to Hibernate on the tutorial as a means to use the sessions. On my app, I'm not using Hibernate or any other enterprise technology, just straight through connection pooling. 
   Does this mean that i must use hybernate if I want to use connection pooling on the stand-alone app?

Many thanks again
Alex

On 10/25/07, Steve Waldman <swaldman@mchange.com> wrote:
Sorry for the slow response.

You really, really need to use Java's reliable resource cleanup idiom
when working with a Connection pool. Otherwise, and Exception is a
Connection leak, and eventually the pool will become exhausted.

Here's a tutorial I sent out a while back. The advice applies to any
resource that should be promptly closed or destroyed. In this
example, it was a Hibernate Session. In your code, it's a JDBC
Connection.

Anyway, advice below. Good luck!

      Steve


> If you have:
>
>    Session mySession = ...; /however you initialize Sessions
>
>    // do some hibernate work...
>
>    mySession.close();
>
> ...then every time an Exception occurs inside the hibernate work, the
> close() is skipped. You MUST use Java's "reliable resource cleanup"
> idiom if you want to avoid Connection leaks in a long-running server
> process. That would be like this:
>
>
>    Session mySession = null;
>    try
>    {
>      mySession = ...; //however you initialize Sessions
>
>      // do some hibernate work...
>    }
>    finally
>    {
>       try { if (mySession != null) mySession.close(); }
>       catch(Exception e) { log("Exception on close", e); }
>    }
>
> Note that if you are working with more than one closable resource,
> you must make a SEPARATE, BEST ATTEMPT to close each resource:
>
>    Session mySession = null;
>    InputStream is = null;
>    try
>    {
>      mySession = ...; //however you initialize Sessions
>      is = createControlFileInputStream();
>
>      // read from the input stream
>      // do some hibernate work, based on control file...
>    }
>    finally
>    {
>       try { if (is != null) is.close(); }
>       catch(Exception e) { log("Exception on close", e); }
>
>       try { if (mySession != null) mySession.close(); }
>       catch(Exception e) { log("Exception on close", e); }
>    }
>
> Note that the closing of the two separate resources occurs in
> distinct try/catch clocks (nested within the finally block). This is
> important -- if you put them ion a single try/catch (or don't nest
> any try/catch blocks), a failure to close() the first resource leads
> to a failure to close() the second.
>
> By the way, none of this is c3p0-specific advice. It's widely
> applicable good Java hygiene. Connection pool exhaustion is just one
> of many ways a failure to close() resources property can manifest
> itself.







On Oct 24, 2007, at 5:23 AM, alex perez wrote:

>
> Hi.
>
>      I must be doing something wrong but I can't seem to be able to
> return the connections to the pool after having used them.
>
>      I'm using: c3p0-0.9.1.2 on stand-alnone mode.
>
>       Following the docs I start my pool as:
>      public static DataSource ds_pooled = null;
>      try {
>           ds_unpooled = DataSources.unpooledDataSource(cString);
>           ds_pooled = DataSources.pooledDataSource( ds_unpooled,
> overrides);
>           Connection conn = ds_pooled.getConnection(); // Setup the
> first connection to save us time later
>           conn.close();
>            System.out.println("INFO: setupConnectionPool.
> Connection set");
>      } catch (SQLException ex) {
>           System.out.println ("EXCEPTION: setupConnectionPool. " +
> ex);
>           terminate();
>      }
>
>     Then I use it as:
>     String sql = "whatever query";
>     Connection conn = ds_pooled.getConnection();
>     PreparedStatement ps = conn.prepareStatement (sql);
>     ps.setString(1, some_value);
>     ResultSet rs = ps.executeQuery();
>     if ( rs.next()){
>       // do stuff with the results here
>     }
>     // And then close it:
>     rs.close(); rs = null;
>     ps.close(); ps = null;
>     conn.close(); conn = null;
>
>     None of the previous seems to close the pool, ie the resources
> stay active until the pool size is full and then the app hangs.
>
>     I solved it by supplying to the overrides config options
> unreturnedConnection data:
>
>     overrides.put("unreturnedConnectionTimeout", "300");
>     overrides.put("debugUnreturnedConnectionStackTraces", "true");
>
>     Then I have the error log full of:
> java.lang.Exception: DEBUG ONLY: Overdue resource check-out stack
> trace.
> at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource
> (BasicResourcePool.java :506)
> at
> com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnec
> tion(C3P0PooledConnectionPool.java:525)
> at com.mchange.v2.
> c3p0.impl.AbstractPoolBackedDataSource.getConnection
> (AbstractPoolBackedDataSource.java :128)
> ...
>
>
> Any thoughts will be greatly appreciated.
>
> Alex
>
>
>
>
>
>
> --
> __________________________
> Alex Perez (CTO)
> Y-Media S.R.O.
> Switchboard: +420 225 990 900
> Office Direct: +420 225 990 903
> Skype: y-media.alex
> ICQ: 403566897
> ----------------------------------------------------------------------
> ---
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a
> browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________
> c3p0-users mailing list
> c3p0-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/c3p0-users

~oo~
Steve Waldman
swaldman@mchange.com






--
__________________________
Alex Perez (CTO)
Y-Media S.R.O.
Switchboard: +420 225 990 900
Office Direct: +420 225 990 903
Skype: y-media.alex
ICQ: 403566897