in reply to Is this Bad form? (DBI)

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

}

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

-HTH

Replies are listed 'Best First'.
Re: Re: Is this Bad form? (DBI)
by Grygonos (Chaplain) on Aug 25, 2003 at 16:48 UTC
    are there any benefits to passing the dbh by reference?
[reply]
      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

[reply]
[d/l]
[select]
      Isn't dbh already a reference - to the object hash? A reference to it would be pointless?

      -- zigdon

[reply]

In Section Seekers of Perl Wisdom