RE: connect_cached, mod_perl && Oracle connection pooling

[Ed Park <ep...@athenahealth.com>]

Hey--

I know that this is mad late, but this caught my eye, and it doesn't look
like anyone has responded since then.

For anyone else-- if you've even been in a situation where you've wanted to
create persistent DBI connections to multiple Oracle schemas, read on.

In short, here's the solution for that particular problem: there is a
completely undocumented function that is particular to DBD::Oracle that
allows you change your default schema within a particular database
connection.

See http://www.geocrawler.com/archives/3/183/2000/4/0/3652431/
In particular, if you look at the official DBD::Oracle install-test
directory (you can see it in /tmp/cpan/DBD-Oracle-1.03/t on titan), there's
a file called t/reauth.t. Here's a small chunk of code from that:
ok(0, ($dbh->selectrow_array("SELECT USER FROM DUAL"))[0] eq $uid1 );
ok(0, $dbh->func($dbuser_2, '', 'reauthenticate'));
ok(0, ($dbh->selectrow_array("SELECT USER FROM DUAL"))[0] eq $uid2 );

Early tests indicate that reauthentication is not a very expensive function
at all. We are currently testing this approach in development, and plan to
put it into production in the near future. If you use it, lemme know how it
works for you.

hope this helps,
Ed

-----Original Message-----
From: spidaman@salon.com [mailto:spidaman@salon.com]
Sent: Saturday, September 23, 2000 6:09 PM
To: dbi-users@isc.org; modperl@apache.org
Subject: connect_cached, mod_perl && Oracle connection pooling


DBI's connect_cached has been "new" for quite sometime now and has been
labeled with "exact behavior of this method is liable to change" since
it first showed up last year.

In November last year, Tim clarified his intentions for connect_cached
and suggested that DBI::ProxyServer be enhanced to provide a pool from
which connections can be checked in and out (or something like that).
Well, I'm now looking at possibly having a multiplicity of connect
strings in a mod_perl environment.  So Apache::DBI doesn't sound
suitable, I don't want every child to maintain connections nailed up for
every connect string (20 apache children * 20 connect strings = 400
nailed up connections, yowza!).  At any given time, the processing
happening in the mod_perl apache child process will only need one of
those connect strings.  Persistence connections are important just
because of the expense of setting up the Oracle connection.  So, I'm
wondering what folks think of non-persistent connections between
mod_perl and the dbiproxy but persistent connections with connect_cached
between dbiproxy and Oracle... does this make sense?  I was thinking
it'd be cool to be able to specify how many of each connections should
be maintained in the pool.  Is anybody doing this and care to share
their experiences with it?
thanks,
-Ian

--
Salon Internet 				http://www.salon.com/
  Manager, Software and Systems "Livin' La Vida Unix!"
Ian Kallen <id...@salon.com> / AIM: iankallen / Fax: (415) 354-3326