Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Re: Is this Bad form? (DBI)

by LanceDeeply (Chaplain)
on Aug 25, 2003 at 16:40 UTC ( #286401=note: print w/replies, xml ) Need Help??

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); }
I like to use a factory to ask for a DBI handle. Like so.


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


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

      -- zigdon

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://286401]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2021-05-13 05:25 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (134 votes). Check out past polls.