ant has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm wondering what your thoughts are on a coding dilema I seem to have.

I'm maintaing some perl scripts, and I'm tidying up the way they connect to the database etc. I have a main Perl script that calls a database package that I wrote. So I simply call the database package and use the database handler accordingly. However the script is then calling in another package (lets call it TABLE), that also makes calls to the database.

So I'm trying to figure out the best approach, when creating the object TABLE, should I put the database handler in as one of it's attributes in the new method call, and use that or everytime I use a method/sub routine of TABLE should I pass in the database handle as a parameter?

Many thanks in advance
Ant

UPDATE: The Script and Package will always connect to the same database as the same user.

Replies are listed 'Best First'.
Re: Best Coding practice
by blue_cowdawg (Monsignor) on Dec 04, 2006 at 17:08 UTC
        So I'm trying to figure out the best approach, when creating the object TABLE, should I put the database handler in as one of it's attributes in the new method call, and use that or everytime I use a method/sub routine of TABLE should I pass in the database handle as a parameter?

    I usually create a module that does the database connections and a few other housekeeping chores and then I create modules that are derivitaves of that one. Example: 

    package dbObject;

my is_connected=0;  # important...
my $dbh=undef;

sub connect {
   | connection foo here...
}

sub prepare { 
   my ($self,$sql) = @_; 
   if ( ! is_connected) 
          $self->connect;
   $self->{sth} = $dbh->prepare(....);
}
1;

package myTable;
use vars qw / @ISA /;
use dbObject;

@ISA = qw/ dbObject /;

   #etc.

1;

    [download]
    and I have variations on that theme that I use. Lately rather than use DBI for my database layer I use Class::DBI::Loader which further encapulates the databse layer for me using a similar method where I have a "parent" class instantiate the loader and the "child" classes inherit it from there. In fact I'm working on a major project now that uses that methodology and other than some interesting bugs I've discovered in the way Class::DBI::Loader reacts to certain table names I've not had any problems.

    Hope this helps...

    UPDATE:Special thanks to GrandFather for pointing out my fat-fingering

    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
[reply]
[d/l]
Re: Best Coding practice
by brian_d_foy (Abbot) on Dec 04, 2006 at 20:26 UTC

    I wrote about this situation in my Singletons article for the second ever issue of The Perl Review (which means its one of the free issues :).

    Apache::DBI uses the concept to let many parts of the code get a database handle without knowing what anyone else is doing. If there is already a handle with the same connection details, it gives back a reference to the existing handle. Different parts of the code talking to the same database can do it by sharing the handle without even knowing it.

    It's a short bit of code, and if you already have your own database layer, you may be able to stick into what you already have without much trouble.

    There may be other design tricks, too, but that would take a more careful analysis of what you already have. I can't say whether or not you should make the handle a parameter to the other class. Will that class always talk to the same database as the rest of the script? Or might it connect to different databases?

    Good luck :)

    --
    brian d foy <brian@stonehenge.com>
    Subscribe to The Perl Review
[reply]
Re: Best Coding practice
by SheridanCat (Pilgrim) on Dec 04, 2006 at 16:59 UTC
    This is confusing me a bit. Are you asking whether it is better to set an attribute on an instance object that contains the database handle vs. passing it in through method calls on that instance object?

    I'm not sure what the best practice is on this. I've done it both ways. I tend to set it in the instance because I then I don't have to remember to pass it in, I can just call $self->{'dbh'} and get the handle I expect.

[reply]

Back to Seekers of Perl Wisdom