#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = connect_db();
print ">>$dbh<<\n";
query($dbh);

sub connect_db {
    my $dbh = DBI->connect('DBI:mysql:dbname', 'user', 'pass') ||
        die "can't open DB: $DBI::errstr\n";
    return $dbh;
}

sub query {
    my $handle = shift;
    my $sth = $dbh->prepare('Select id FROM page_data');
    my $id;
    $sth->execute();
    $sth->bind_columns(\$id);
    while ($sth->fetch()) {
        print "\tFound id $id.\n";
    }
    $sth->finish();
}
[download]

However, I'd be more likely to do what you suggest, adding the DB handle to $self and adding wrapper methods to the class to operate on the handle and return data. Why not abstract all the way and make an OO interface around the database object? That way, you don't have to pass it to different objects, you just have one object that knows how to get at your data.

My main thought is that the

my $dbh = DBI->connect( " yada yada " ), { PrintError => 0},
      or die "cannot open db connection:  $DBI::errstr\n";
[download]

  eval {
      my $dbh = DBI->connect( " yada yada " ), { PrintError => 0},
      or die "cannot open db connection:  $DBI::errstr\n";
  }
  return $dbh;
[download]

Try using something like

sub db_connection {

    my ( $self ) = @_;
    my $dbh;
[download]

Standard statements of making sure you are using -w and strict and a request to use the <code></code> tags.

mikfire

Thanks everyone for your replies. I am currently experimenting with your suggestions to see which one works best. Any code that works gets stuck in the TIMTOWTDI drawer. I tried Merlyn's suggestion first and it works great. I am also messing around with other people's suggestions and they work too.... I spent several hours researching your responses. I will try to post a "here is what I learned" summary for any monks new to DBI. Thanks, moo

{
  my $dbh;
  sub get_db_prod {
    unless (defined ($dbh)) {
      $dbh =  DBI->connect(
        ('DBI:Sybase:' . prod_server),
        prod_user, prod_pass,
        {PrintError => 0, AutoCommit => 0}
      ) or die $DBI::errstr;
    }
    return $dbh;
  }
}
[download]

Note that I use a wrapper for errors, so I set PrintError to 0, you may want to change that default. Look at the DBI docs to decide what defaults make sense to you.

A big win with the above code. This is really, really nice in programs which may or may not need a database connection, but if they do will need it in several places. Just place calls to the above function wherever you need it and don't worry about any custom logic for whether or not to initiate a connection. I have some scripts that this idea has really simplified. Depending on what you are trying to do, YMMV.

And to answer your final question, building and destroying connections is very expensive, maintaining them is pretty cheap. So you want to open a connection and not close it again if you can at all help it.

Make sure your named subroutine is inside a BEGIN block, like so:

BEGIN {
  my $dbh;
  sub get_db_handle {
    ..
    ..
    return $dbh;
  }
}
[download]

If you fail to do that, and manage executing that block twice in a program, you'll get the wonderful $dbh won't stay shared error, indicating some serious bad voodoo.

-- Randal L. Schwartz, Perl hacker

Right you are of course.

OTOH another way to guarantee the right behaviour is to put the subroutine in a module. And that module not coincidentally in my case is where the name and password are, making it easy to change the password periodically.

Of course dealing with mod_perl you probably get to curse the "won't stay shared" error much more than I do. :-)