in reply to Object Oriented Pattern help

I'd say that the $dbh connection should remain open for the lifetime of the object, which means it should be created during the constructor. You will also want to add the DBI->connect string to the items passed to the constructor in that case, and maybe give the option for an already created dbi session to be passed in instead. When you have the session, you can store it in $self->{dbh}.

As for the individual queries they should be either created with $dbh->prepare_cached('select...');, or stored in the object. This code may give an idea of what I mean. 

sub delete {
     my $obj = shift;
     my $sth = $obj->{queries}{delete} || $obj->{dbh}->prepare( 
                               "DELETE FROM guest_list
                               WHERE  last_name = ? and
                                      first_names = ?" );
     $sth->execute($obj->{_last_name},
                   $obj->{_first_names});
     $obj->{queries}{delete} = $sth;
     return 1;
  }

[download]

Replies are listed 'Best First'.
Re: Re: Object Oriented Pattern help
by steveAZ98 (Monk) on May 12, 2001 at 10:21 UTC
    I agree here, I open a connection to the database in the constructor and disconnect in the destructor. If your running mod_perl you'll most likely have cached open handles which can be used and you might not need the disconnect, but I do it out of habit. I use code something like this: 
    
use vars qw( %conn );

sub new {
    my $class = shift;
    $self = bless { _dbh => undef, }, $class;
    $conn{'dbname'} ||= DBI->connect( ... );
    $self->dbh( $conn{'dbname'} );
    return $self;
}

sub DESTROY {
    $_[0]->dbh->disconnect() if $_[0]->dbh;
}

sub dbh { @_ > 0 ? $_[0]->{_dbh} = $_[1] : $_[0]->{_dbh} }

    [download]
    HTH,
    Steve
[reply]
[d/l]

      This may not be so good of an idea if using multiple instances of the class in a single program. Even with persistent connections, it seems too cumbersome to pass in database info to the constructor each time. If you're not using persistent connections and you create 5 objects in a script, each object will create a new connection to the database.

      This also doesn't scale very well if using another similar class in the program. Again, multiple handles to the same database for each guest object and each XYZ object and you have some pretty greedy perl code.

      If you're only using one class to access the database, then maybe use a class variable: 

      package Guest;
use vars qw($DBH);

sub new {
  $DBH ||= DBI->connect(...);
}

END {
  $DBH->disconnect();
}

      [download]

      The original code showed passing in a reference to the database handle to each method and that would work fine. A better way may be to pass that in to the constructor and have each object store that instead. But if you do that, make sure you don't have the destructor close the database handle either. Doing database opens/closes in a central location (in this case, the main program) seems like the cleanest way.

[reply]
[d/l]
Re: Re: Object Oriented Pattern help
by DrZaius (Monk) on May 13, 2001 at 02:28 UTC
    You probably don't want your objects creating the dbh handles for you. This doesn't scale and is not portable; you have to edit your perl modules if you change your database or use on a different server.

    What if you already have the database connection open in your code? Do you want your objects to open a new connection per object?

    But creating a $self->{dbh} is probably a good idea. I would do something like: 

    my $person = People->new(dbh => $dbh, %other_data);

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom