We are attempting to transition to using C3P0 connection pooling and have run into an issue with initializing the connections.

We are using a session-per-conversation pattern. We use Oracle to provide authentication for each user of our application (each user has their own oracle userId). Once a user succesfully authenticates via Oracle we switch the schema to the common hibernate schema via an "alter session set current_schema=e3". This is issued once right after the call to sessFactory.openSession(). This worked fine prior to our switch to C3P0. The e3 schema was visible during all subsequent connections and transactions. When we switched to C3P0 the "alter session set current_schema=e3" appears to be in effect only during the transaction within which it was issued.

Question: Is there a way to alter the session schema for all C3P0 connections in the pool once? (The current work around is to issue the "alter session set current_schema=e3" at the begining of each new transaction)

Hi.

I think this might be another issue related to hibernate's connection release mode. See section 11.5, http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html. If hibernate is releasing and reacquiring Connections between transactions, then you'll often end up with transactions that have not had the benefit of your customized authorization and initialization.

You should be able to resolve this easily by ensuring that all Connections acqured by the pool are properly initialized, rather than initializing Sessions when they are associated with Threads. Please try using the latest version of c3p0-0.9.1, and define a ConnectionCustomizer (see the docs) so that your initialization occurs at Connection acquisition rather than at Session acquisition.

If this is too disruptive, you can just change hibernate's Connection release mode to "ON_CLOSE", which will lead to more intuitive behavior, but implies a heavier footprint in terms of resource utilization, as all open Sessions will hold an open Connection even when nothing much is being done with them.

Good luck!

Steve