Guidance on setting up multiple database handles to be used in one script

Over time the need to connect to the same database in different
scripts became apparent. To do this I began by creating a package
that contained all the $dbh handles (not advisable) as if one fails
then your whole script is no good. So I then split them into
individual packages for each connect handler. Again not good if you
need to connect to multiple databases in your script, but aren't
dependent on all of them having to work.

So is there a good way to manage database connections only. So that
multiple scripts can use the same connection but it is defined in the
one place. Should I explore DBI::Ingres. or is there another easier
way, other than defining the connection in each script I create.

RE: Guidance on setting up multiple database handles to be used in one script

It's not clear from your message what you want.

If you mean that you want to open a db handle or set of handles in the
same way with the same options across a number of scripts, why don't you
just create a function for that? You can create your own Perl module
exporting that function and just call it from the different scripts.

If this doesn't solve your problem, then please specify what is needed.

-Will


> -----Original Message-----
> From: Russ [mailto:kropporama@gmail.com]
> Sent: Tuesday 31 July 2007 21:21
> To: dbi-users@perl.org
> Subject: Guidance on setting up multiple database handles to
> be used in one script
>
>
> Over time the need to connect to the same database in different
> scripts became apparent. To do this I began by creating a package
> that contained all the $dbh handles (not advisable) as if one fails
> then your whole script is no good. So I then split them into
> individual packages for each connect handler. Again not good if you
> need to connect to multiple databases in your script, but aren't
> dependent on all of them having to work.
>
> So is there a good way to manage database connections only. So that
> multiple scripts can use the same connection but it is defined in the
> one place. Should I explore DBI::Ingres. or is there another easier
> way, other than defining the connection in each script I create.
>



- - - - - Appended by Scientific Atlanta, a Cisco company - - - - - =

This e-mail and any attachments may contain information which is confiden=
tial,
proprietary, privileged or otherwise protected by law. The information is=
solely
intended for the named addressee (or a person responsible for delivering =
it to
the addressee). If you are not the intended recipient of this message, yo=
u are
not authorized to read, print, retain, copy or disseminate this message o=
r any
part of it. If you have received this e-mail in error, please notify the =
sender
immediately by return e-mail and delete it from your computer.

Re: Guidance on setting up multiple database handles to be used inone script

On Tue, 31 Jul 2007, Russ wrote:

> Over time the need to connect to the same database in different
> scripts became apparent. To do this I began by creating a package
> that contained all the $dbh handles (not advisable) as if one fails
> then your whole script is no good. So I then split them into
> individual packages for each connect handler. Again not good if you
> need to connect to multiple databases in your script, but aren't
> dependent on all of them having to work.
>
> So is there a good way to manage database connections only. So that
> multiple scripts can use the same connection but it is defined in the
> one place. Should I explore DBI::Ingres. or is there another easier
> way, other than defining the connection in each script I create.


If I understand right, you want to create you database handle ($dbh) and
then have multiple scripts use that same $dbh, right? If so, read on; if
not, the talks linked below may still be useful.

Tim talked about caching database handles (and other things) and their
gotchas in his Advanced DBI talk, which he posted to the list earlier.

Two things come to mind off the top of my head, both really based on Tim's
talks, which I recommend that you peruse:




One approach would be to have a parent script that creates the database
handle and then manages all of the other scripts. This assumes several
things about what you are doing, and may not be practical to you. I
suppose you could launch a separate script that maintains the $dbh and
allows the other scripts to communicate to it.

Another approach is that the scripts are called from Apache, then it seems
that it is possible to share the $dbh between processes.

I'm still looking at DBI::Gofer, and can't say with confidence that it
would work for this situation.

John

Re: Guidance on setting up multiple database handles to be used in one script

Defining connections in one place is one thing.
Using the same connection from different places is another.

The first point, defining connections in one place, is something
quite trivial that is unnecessary to discuss. Basically it means
specifying the dsn string, and that can be done in a dozen ways
without a problem.

However, taking a connection that was created in one central place
and using it in a bunch of other places is a more complicated issue.
The situation depends on your setup.
Say that the creator of the connection is code A, and that code B and
C use that connection.

Case 1:
Code A, B and C reside inside the same process, i.e. are run by the
same Perl interpreter inside the same process. For example, you could
have a mod_perl process initializing a connection (A) and then
calling that connection each time a page is requested (B, C, etc...).
The only problem in this case is that when the connection goes down,
any subsequent use of the connection fails.
Solution:
Create something like DBIx::HA to reconnect seamlessly by utilizing
the swap_inner_handle() method of DBI. Basically if you see that the
connect goes down, you create a new one and swap its inner handle
with the old one, effectively making the original one active again,
unbeknownst to the codebase. So say A connects, then B uses it
successfully, then it goes down and C sees a failure. C does the
swap_inner_handle magic and reuses the connection, and after that B
can use it again without noticing anything happened.
In simpler words, assume you have a global $dbh and at some point it
becomes bad. You create a local $dbh2, connect, and when you're happy
with it, you swap_inner_handle between $dbh and $dbh2. From that
point on, $dbh is good and $dbh2 is bad. Then you can keep going with
your code that uses the $dbh global variable. It's perfectly elegant.

Case 2:
Code A, B and C do NOT reside inside the same process. That's much
more difficult. As Ross says below, you can cache the database handle
in some way. Or you can use DBI::Gofer. One way to think about
DBI::Gofer is that you have a central process (think of it as a
daemon) that runs all the queries, and you connect to it from your
script, give it the sql you want, and it gets you back your data. In
effect it's a centralized DBI "server". So Code A resides inside that
server, and code B and C use DBI::Gofer to communicate with the DBI
server. You can communicate using a number of protocols (stream,
http, ssh...) so you can have that DBI server in the same machine or
on another machine (or cluster).

H

On Aug 1, 2007, at 11:11 PM, John Costello wrote:

> On Tue, 31 Jul 2007, Russ wrote:
>
>> Over time the need to connect to the same database in different
>> scripts became apparent. To do this I began by creating a package
>> that contained all the $dbh handles (not advisable) as if one fails
>> then your whole script is no good. So I then split them into
>> individual packages for each connect handler. Again not good if you
>> need to connect to multiple databases in your script, but aren't
>> dependent on all of them having to work.
>>
>> So is there a good way to manage database connections only. So that
>> multiple scripts can use the same connection but it is defined in
>> the
>> one place. Should I explore DBI::Ingres. or is there another easier
>> way, other than defining the connection in each script I create.
>
>
> If I understand right, you want to create you database handle
> ($dbh) and
> then have multiple scripts use that same $dbh, right? If so, read
> on; if
> not, the talks linked below may still be useful.
>
> Tim talked about caching database handles (and other things) and their
> gotchas in his Advanced DBI talk, which he posted to the list earlier.
>
> Two things come to mind off the top of my head, both really based
> on Tim's
> talks, which I recommend that you peruse:
>
> > DBI_AdvancedTalk_200708.pdf>
>
>
> One approach would be to have a parent script that creates the
> database
> handle and then manages all of the other scripts. This assumes
> several
> things about what you are doing, and may not be practical to you. I
> suppose you could launch a separate script that maintains the $dbh and
> allows the other scripts to communicate to it.
>
> Another approach is that the scripts are called from Apache, then
> it seems
> that it is possible to share the $dbh between processes.
>
> I'm still looking at DBI::Gofer, and can't say with confidence that it
> would work for this situation.
>
> John
>