FAQ
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.

Search Discussions

  • Rutherdale, Will at Aug 1, 2007 at 5:17 pm
    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
    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 confidential,
    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, you are
    not authorized to read, print, retain, copy or disseminate this message or 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.
  • John Costello at Aug 1, 2007 at 8:11 pm

    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:

    <http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI_AdvancedTalk_200708.pdf>
    <http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/Gofer-200707.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
  • Henri Asseily at Aug 1, 2007 at 10:34 pm
    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:

    <http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/
    DBI_AdvancedTalk_200708.pdf>
    <http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/Gofer-200707.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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedAug 1, '07 at 1:21a
activeAug 1, '07 at 10:34p
posts4
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase