Re: [c3p0-users] Abort request from ConnectionCustomizer or ConnectionTester?

[John H. <joh...@jc...>]

Thanks for the reply.  Sorry for my delay in responding -- I've been out on
vacation.  ;-)

Please see my comments below.

Thank you, in advance, for your continued assistance.

-John Hardin


On Thu, Dec 20, 2012 at 2:19 AM, Steve Waldman <swa...@mc...> wrote:

> hi,
>
> so the short answer is that what you propose to do is not a use c3p0
> currently anticipates or supports.
>
> an Exception in onCheckout, or the failure of a Connection test, are
> interpreted as an indication that the Connection is broken and should be
> retried. onCheckout is intended to set-up Connections for client use, but
> is not designed to control access to Connections or discriminate among
> clients. (how do you discriminate among clients in your initialization? and
> what sort of per-tenant initializations do you perform? do you ensure that
> whatever you do is undone on checkin, or consistently "overwrite" the
> initializations on checkout, to ensure that all pooled Connections are
> interchangeable on checkout?)
>

A thread local variable is set with the ID of the current Tenant very early
on in request processing.  Among other things, we execute "ALTER SESSION
SET CURRENT_SCHEMA ..." to "pin" this Connection to the schema of the
current Tenant.  We don't "undo" this on checkin -- we simply execute it
upon every checkout.

As you note, this allows our pooled Connections to be interchangeable
amongst all Tenants.


it wouldn't be too hard to support what you want, to define an Exception
> that could be thrown from onCheckout to signal not a failure but a client
> ejection. the Exception might indicate whether the Connection should be
> interpreted as broken or returned to the pool for future use, or else the
> library could just err conservatively and purge. but...
>
> 1) i don't want to integrate this as a new feature until i get 0.9.2-final
> released and start the next dev series. the current release [ 0.9.2-pre8 ]
> is pretty much a release candidate. if there are no showstoppers, i hope to
> be done this series within a few weeks.
>

Understood.



> 2) i'm not sure that inside the pooling library is the right place to do
> what you want. do you think it might be better to wrap a facade around
> DataSource and do your tenant-specific stuff there? DataSource is a pretty
> easy interface to wrap.
>

That's what I've already done, but I'm not sure, at this level, how to tell
c3p0 to "toss" a Connection.  For example, I want to test Connections to
see if they need to be reestablished (e.g., they're currently connected to
a node that needs to be removed from the cluster for maintenance).  c3p0
already conveniently has this "retry" logic built-in; so, implementing this
behavior in a ConnectionCustomizer or ConnectionTester is trivial. However,
as my original email describes, I have no means of aborting a request
altogether which could happen, if, say the Connection initialization
("ALTER SESSION...") fails.

Is there any way to tell c3p0 to "toss" a Connection?


that said, i'm not opposed to adding this, if it would be helpful. but it
> won't be part of 0.9.2.
>

Personally, I think this could be VERY helpful (certainly to me  :-).  But
I'm not sure I can wait until 0.9.2.  ;-)

Suggestions?



>      smiles,
>         steve
>
>
>
> On Dec 19, 2012, at 4:07 PM, John Hardin wrote:
>
> > I'm working on a multi-tenant application using c3p0.  I'm managing a
> single connection pool for all Tenants.  Upon checkout, I want to test the
> connection as well as initialize it for use by the current Tenant (which is
> established early in request processing).
> >
> > ConnectionCustomizer.onCheckout() or
> (Unified)ConnectionTester.activeCheckConnection() seem like ideal locations
> for my test and initialization logic. In some cases, I want to toss the
> current connection, and retrieve another from the pool (or create a new
> connection as necessary).  The "retry" built into the callers of these
> functions implements this very logic.
> >
> > HOWEVER, in other cases, I want to abort the current request altogether,
> and the caller's retry logic seems to prevent me from doing so.
> >
> > While ConnectionCustomizer.onCheckout() is declared to throw Exception,
> the caller catches Exception, tosses the connection, and tries again.
>  ConnectionTester.activeCheckConnection() returns the status of the
> connection, but there is no status for "abort the request".  So, in both
> locations, I see no way to abort the current request.  :-(
> >
> > Any ideas?
> >
> > Thank you, in advance, for your assistance.
> >
> > -John Hardin
> >
> >
> >
> ------------------------------------------------------------------------------
> > LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
> > Remotely access PCs and mobile devices and provide instant support
> > Improve your efficiency, and focus on delivering more value-add services
> > Discover what IT Professionals Know. Rescue delivers
> >
> http://p.sf.net/sfu/logmein_12329d2d_______________________________________________
> > c3p0-users mailing list
> > c3p...@li...
> > https://lists.sourceforge.net/lists/listinfo/c3p0-users
>
>