Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Passing Database handles to other modules.

by thunders (Priest)
on May 06, 2003 at 13:37 UTC ( #255870=perlquestion: print w/replies, xml ) Need Help??
thunders has asked for the wisdom of the Perl Monks concerning the following question:

I'm looking to implement a large project under CGI::Application. I'd like to keep most database manipulation out of the main WebApp module, and into other modules that represent more tangible objects.

I'm wondering how to pass the database handle around. I plan to run under mod_perl with Apache::DBI. Is it ok to pass handles back and forth this way? or should the objects i'm calling create and destroy the database handles themselves. Or should i be going about this some other way

Below is some code showing what i'm thinking of doing:

package MyWebApp; use base 'CGI::Application'; use MyWebApp::DB; use MyWebApp::TicketCollection; use strict; # Needed for our database connection sub setup { my $self = shift; my $db = new MyWebApp::DB; my $dbh = $db->get_dbh; $self->start_mode('menu'); $self->tmpl_path(''); $self->run_modes( tickets => 'show_tickets', ); # Connect to DBI database $self->param('dbh' => $dbh); } sub teardown { my $self = shift; # Disconnect when we're done $self->param('dbh')->disconnect(); } sub show_tickets{ my $self = shift; my $q = $self->query(); my ($start_date, $end_date) = ( $q->param('start_date'), $q->param('start_date') ); #more code to verify user input my $tc = new MyWebApp::TicketCollection( $self->param('dbh'), $start_date, $end_date ); #code to display tickets } 1;

Replies are listed 'Best First'.
Re: Passing Database handles to other modules.
by gmax (Abbot) on May 06, 2003 at 17:03 UTC

    You can pass an already initialized database handler, store it inside the object and then reuse it at will. Here's a tested example.

    #!/usr/bin/perl -w use DBI; use strict; package Other; sub new { my $class = shift; my $self = bless {}, $class; my $dbh = shift || return undef; $self->{dbh} = $dbh; # store the dbh # for future use return $self; } sub do_it { my $self = shift; my $query = shift; # # reuses the stored dbh # my $sth= $self->{dbh}->prepare($query); $sth->execute; while (my $row = $sth->fetchrow_arrayref()) { print "@$row\n"; } } package main; sub do_it_differently { my $db = shift; my $query = shift; my $rows = $db->selectall_arrayref($query); for my $row (@$rows) { print "<@$row>\n"; } } my $dbh = DBI->connect("DBI:driver:database", # change it 'username', 'password', {RaiseError => 1}) or die "can't connect\n"; my $query = qq{select * from departments }; my $ext = new Other $dbh # passes a dbh to an object or die "can't create\n"; $ext->do_it($query); # calls a method that uses a stored +dbh do_it_differently ($dbh, $query); # passes a dbh to a sub $dbh->disconnect();
     _  _ _  _  
    (_|| | |(_|><
      The only trouble with this approach is that in a mod_perl setting that object could exist for hours with no use, during which time that connection might time out. If you fetch your $dbh from Apache::DBI at least once per request, you will avoid this. Apache::DBI will notice that the connection is dead and reconnect.
Re: Passing Database handles to other modules.
by hardburn (Abbot) on May 06, 2003 at 13:56 UTC

    I don't see why you couldn't. You might want your modules to do an isa() check on what was passed in, but other than that, it should be OK to just pass around the database handle. Especially true with using Apache::DBI, since you know the handle will always be open (or if not, that it will be automatically reopened), so there's no worry an underlieing module might try to do something with it after its been disconnected.

    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    Note: All code is untested, unless otherwise stated

Re: Passing Database handles to other modules.
by arturo (Vicar) on May 06, 2003 at 14:14 UTC

    Given a pooling mechanism, my inclination is to let each bit of code that needs a DB handle just grab it from the pool (and you can simplify this by creating a connector that just returns a connection for you, instead of having to manually call DBI->connect all the time). The one exception to this model would be if you have a backend that supports transactions1, so that the session associated with a given handle really matters, in which case, of course the thing to do is simply pass the handle around as you would any other object, as has already been pointed out.

    1 I suppose it might also apply if your DBMS has something equivalent to MySQL's last_insert_id, as that may be session-and-thus-connection-dependent as well.

    If not P, what? Q maybe?
    "Sidney Morgenbesser"

      Apache::DBI will give you the same handle every time if you connect with the same parameters. Grabbing it from the "pool" is no different from passing it, since there is actually only one database connection per process when you keep using the same connection string. Apache::DBI provides persistent connections, not pooled ones.
Re: Passing Database handles to other modules.
by UnderMine (Friar) on May 06, 2003 at 17:53 UTC

    The real question is are you using transactions in SQL? if not Apache::DBI can hide a lot of this from your modules. You can get a new handle for each module and let Apache::DBI sort out the optimisation of handle usage.

    You need to be careful if you wish to use transactions within you SQL. Both queries in a transaction need to be passed the same handle to work on otherwise you generate two independant transactions.

    • Generally selects rarely need to be part of a transaction (except when doing a FOR UPDATE lock)
    • Single updates or inserts do not need to be part of a transaction.
    • Multiple table updates or inserts do need to be part of a transaction if it is required that a single failure causes all updates/insets to fail.

    Hope it helps

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://255870]
Approved by sschneid
Front-paged by broquaint
[erix]: handy, no blindfold needed
[Discipulus]: ++erix

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (9)
As of 2017-09-25 13:50 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (280 votes). Check out past polls.