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

I am creating a Perl Module that has atleast 3 sub routines that could be called externally. One of these routines calls all the rest of the routines. So you could get a routine to run in two ways: by calling it directly or by calling the "all" routine.

Now here is my problem: Each routine connects to the database using DBI or the "all" routine connects and passes the database handle along. I am trying to determine when I should disconnect from the database when calling the routines in the 2 scenarios.

Here are some code snippets

sub make_all_reports {

  # connect to the database
  my $dbh = &_get_dbh;

  &make_summary_report($dbh);
  &make_report1($dbh);
  &make_report1($dbh);

  ...

  # close the database connection
  $dbh->disconnect;

  # return happy and healthy
  return (1);

}

[download]

That was for reference, my problem is in the next routine.

sub make_report1 {

  # connect to the database
  my $dbh = shift;
  $dbh = &_get_dbh if (!defined($dbh));

  ...

  my $caller = caller(1);

  # close the database connection if opened here
  $dbh->disconnect if (!defined $caller);

  # return happy and healthy
  return (1);

}

[download]

OK, so I need the conditional to work better on the database disconnect. I need to only disconnect if the $dbh was not passed in as a argument from a caller routine. If someone calls this routine from a different package (even main:: ?) it could break.

Any help you could provide would be wonderful, thank you

Replies are listed 'Best First'.
Re: DBI: $dbh-disconnect; yes, now, now, but not now!
by stephen (Priest) on Mar 15, 2001 at 01:15 UTC
    Conditionals can get confusing very quickly. Why not avoid them altogether? Here's the idea:

    For each of your individual subroutines, you can define a private subroutine that actually does the work, plus a public subroutine that gets the database handle, calls the private subroutine, and then disconnects. For example: 

    sub make_report1 {

  my $dbh = &_get_dbh;
  &_do_make_report($dbh);
  $dbh->disconnect;
  1;
}

sub _do_make_report {
   my $dbh = shift;

   ## etc...
}

sub make_all_reports {

  # connect to the database
  my $dbh = &_get_dbh;

  &_do_make_summary_report($dbh);
  &_do_make_report1($dbh);
  &_do_make_report1($dbh);

  ...

  # close the database connection
  $dbh->disconnect;

  # return happy and healthy
  return (1);

}

    [download]

    If you get tired of writing the stub subroutines, you could autogenerate them with autoload. This would add some overhead. Namely, you could define an autoload subroutine like this one: 

    package your_package;

sub AUTOLOAD {
    no strict qw(refs);

    our $AUTOLOAD;
    $AUTOLOAD =~ m{ (.*) :: (.*?) $ }x;
    my $dispatch_name = "$1::_do_$2";
    if (defined &{$dispatch_name}) {
        *$AUTOLOAD = sub {
            my $dbh = &_get_dbh;
            my $rc = &$dispatch_name(@_);
            $dbh->disconnect();
            return $rc;
        };
        goto &$AUTOLOAD;
    }
    else {
        croak("Unable to autogenerate method for '$AUTOLOAD': no such 
+function '$dispatch_name'");
    }
}

    [download]
    That way, you could just define your _do_* subroutines, and the public versions would be autogenerated the first time they are called.

    On the other hand, Masem's point (and others) are well taken. You can avoid doing all of the above by having your _get_dbh function work like so: 

    $your_package::DBH = undef;

sub _get_dbh() {
    defined($your_package::DBH) 
        and return $your_package::DBH;
    $your_package::DBH = DBI->connect...
    return $your_package::DBH;
}

    [download]

    Update: Added autoload section.

    Update: Added "on the other hand..."

    stephen

[reply]
[d/l]
[select]
      Thanks for your help. The stub routine method will work fine. And I can use it as an excuse to learn the AUTOLOAD features of making perl modules. And that is alright!
[reply]
Re: DBI: $dbh-disconnect; yes, now, now, but not now!
by reyjrar (Hermit) on Mar 15, 2001 at 02:48 UTC
    I believe you'll encounter that error everytime that $dbh leaves scope.. What I normally do to get around this is check the "environment" first, after I've verified my program has all the options and info needed to run, I'll open up a global $DBH, and then I'll do an end block like: 
    END {
    $DBH->disconnect;
    # whatever else you want to clean up neatly
}

    [download]
    which I guess is a cheap hack, but works..


    -brad..
[reply]
[d/l]
Re: DBI: $dbh-disconnect; yes, now, now, but not now!
by Masem (Monsignor) on Mar 15, 2001 at 01:19 UTC
    Not necessarily solving your problem, but is it a good thing to have to continually connect and disconnect to your database? There really isn't any harm in keeping the database connection open throughout the run of a program and it saves time and effort later.

    Now, if you are closing the database in order to commit changes, I know the DBI has the facility to call COMMIT at any point without closing the DB connection. Or, optionally, if you aren't doing transactions, you can tell the DBI to AutoCommit, for those databases that support it. Otherwise, I don't send any need to open and close a DB connection except at the start and end of a program , respectively.

    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
[reply]
Re: DBI: $dbh-disconnect; yes, now, now, but not now!
by turnstep (Parson) on Mar 15, 2001 at 02:42 UTC

    Here's one way to do it, if you just want a conditional:

    sub make_report1 {

  my $dbh = shift;
  my $newdbh=0;

  if (!defined $dbh) {
    $dbh = &_get_dbh;
    $newdbh=1;
  }

  ...

  # close the database connection if opened here
  $dbh->disconnect() if $newdbh;

}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom