That said, for smaller scripts, I will often put $dbh as a global. *shrugs* YMMV, TMTOWDI, and all that.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Like the others here I sometimes decalre a database handle as a global, it is perfectly valid to do so.

On the other hand, I think it is far more elegant and easier to 'self-document' your code if you pass the handle into the sub. I got caught big time just a few days ago. I was using the $dbh as a global, then I realised that I needed to connect to a second database on another machine to get some data. To avoid duplicating the database subs I went back and passed the handle in to the subs so I didn't have a maintenanace nightmare.

It only takes one line of code (maybe only a part of a line!) per subroutine, but for me it has been a lifesaver when I do it, and the one time I don;t do it - I get caught!

As always, YMMV & TIMTOWTDI, good luck!

jdtoronto

Abigail

I agree: I use a similar style. If I expect to only have a single database handle for the program, I'll keep it global. On the other hand, if I expect to have lots of handles running around, I move all the connection code into sub-routines (e.g. one sub per transaction), like this:

InsertRecord($val1, $val2);
my @array = RetrieveValues($val2, $val3);

sub InsertRecords {
      my $dbh = DBI->connect(xxxx);
      # stuff
      }

sub RetrieveValues {
      my $dbh = DBI->connect(xxxx);
      # stuff
      }
[download]

This is undoubtably inefficient, but it is very clear, and is the first step before I move the subs off into their own module (e.g. StandardTransactions.pm). YMMV

I'd would tend to write the subroutines so I would always pass $dbh if I was writing in a functional/procedural style.

In an OO style I would just pass the $dbh once when I initialise the object.

In either case my motivation would be to decouple the $dbh from the code to make things like testing, changing handles, etc. a lot easier.

If you pass in $dbh, it's easier to write unit tests against your functions. You can use a Mock DBI or a testing database. With a lexical $dbh, there's a coupling that can be hard to test around. A package global would be easier in that case, but I don't mind passing the variable.

use strict;

my $dbh = DBI->connect(XXXX);

runSomeQueries();

$dbh->disconnect();

sub runSomeQueries
{
   my $query = "select * from table";
   my $sth = $dbh->prepare($query);
   if ( $sth->execute() )
   while (my $data = $sth->fetchrow_hashref )
   { 
      runSomeSubQueries(some$$hash{ItemID});
   }
}

sub runSomeSubQueries
{
   my $itemID = shift;

   my $query = "select * from detail_table where ItemID = ?";
   my $sth = $dbh->prepare($query);
   $sth->execute($itemID);

}
[download]

-HTH

are there any benefits to passing the dbh by reference?

Actually- I was only concerned that you cant ask the same handle to prepare a statement while iterating through a previously prepared statement. But after testing it, my assumption was wrong. In the subquery below, I delete data from the original query. And DBI handles it OK. --Me.

sub runSomeQueries
{
    my $query = "select LocationID, LocationName from Location";
    my $sth = $dbh->prepare($query);
    if ( $sth->execute() )
    {
        while (my $data = $sth->fetchrow_hashref )
        {
            runSomeSubQueries($$data{LocationID});
        }
    }
}


sub runSomeSubQueries
{
    my $locationID = shift;
    my $query = "Delete from Location where LocationID = ?";
    my $sth = $dbh->prepare($query);
    $sth->execute($locationID);
}
[download]

So- back to your question, passing the handle. Aside from the points raised by above. If you code your functions to accept a handle, you can pass differently configured handles through to your function.
• you can hit different databases

  my $dbh_primary = DBI->connect(XXXX);
  my $dbh_backup = DBI->connect(YYYY);
  runSomeQueries($dbh_primary);
  runSomeQueries($dbh_backup);
  [download]

• you can pass in a handle with AutoCommit turned off

  my $dbh = DBI->connect(XXXX);
  my $dbh_tx = DBI->connect(XXXX, AutoCommit => 0);
  
  #  non-transactioned
  runSomeQueries($dbh);
  runSomeSubQueries($dbh);
  
  #  if you need to transaction a bunch of queries together
  runSomeQueries($dbh_tx);
  runSomeSubQueries($dbh_tx);
  if ( $OK )
  {
      $dbh->commit();
  }
  else
  {
      $dbh->rollback();
  }
  [download]

-HTH

Isn't dbh already a reference - to the object hash? A reference to it would be pointless?

-- zigdon

$SOME_CONSTANT = 42;
$Some_Global   = '/some/directory';
$some_local    = shift;
[download]

YuckFoo

or you could just do use constant MYCONST => XXXas stupid as it is. sometimes aliasing 1 and 0 to true and false is handy for readability. While we all know that 0 is false and 1 is true, it makes it easier, for those of us who have a nosy boss who wants to see your code but knows nothing about programming, if you write self-documenting/easy to read code. The big advantage to using use constant that I have found is that they are bareword operators, so you spend less time trying to remember whats a constant and whats not.