Re: Is this Bad form? (DBI)

[LanceDeeply]

The global handle is much simpler to implement. But consider what you will have to do if you need to run subqueries on your first result set. The following example is a bit contrived, but it's easy to imagine an instance with nested procedures that can produce same problem.

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);

}

I like to use a factory to ask for a DBI handle. Like so.

-HTH

[Grygonos]

are there any benefits to passing the dbh by reference?

[LanceDeeply]

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);
}

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);
  

• 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();
  }
  

-HTH

[zigdon]

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

-- zigdon