in reply to Re^4: DBI, prepare_cached, finish, and forks
in thread DBI, prepare_cached, finish, and forks

The code you show does *not* present a problem, in other words, it doesn't need finish(). You don't break out of the while loops, which means you do fetch all rows and therefore finish() is called automatically. The only time you need finish is if you fetch less than all rows e.g. you call one of the fetch methods outside of a loop, or if you call it in a loop but break out of the loop before all rows are fetched.
  • Comment on Re^5: DBI, prepare_cached, finish, and forks

Replies are listed 'Best First'.
Re^6: DBI, prepare_cached, finish, and forks
by Anonymous Monk on Feb 24, 2006 at 17:03 UTC
    Ok, so I see how I was getting the disconnect errors:
    DBI::db=HASH(0x0000000)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting)...
    I had: 
    $dbh->connect(...);

$sth->prepare("SELECT COUNT(*) FROM table WHERE col=?");
$sth->execute($var);
($count)=$sth->fetchrow_array;

$dbh->disconnect;

    [download]
    And I know how to correct it in long form: 
    $dbh->connect(...);

$sth->prepare("SELECT COUNT(*) FROM table WHERE col=?");
$sth->execute($var);
# added while()
while(($count)=$sth->fetchrow_array) { ... }

$dbh->disconnect;

    [download]
    but is there a better way to call it (instead of fetchrow_array()) without the while() loop since I know there will only be one row returned?

    Also, if I use the while loop, then my $count is localized, so I'd instead have to assign it within the while() (or use shorthand, but complexity I'd like to get away from).
[reply]
[d/l]
[select]
      If you know there will be only one row returned, fetch it and then call $sth->finish().
[reply]
[d/l]
        Ok, great! And just to make sure I understand that I'm doing it right (since both ways work), should the finish be right after the execute() in a while() loop, or after the while(): 
        $dbh->connect(...);

# prepare statements
$sth->prepare("SELECT ...");
$sth2->prepare("SELECT COUNT(*) FROM table WHERE col=?");

# loop through sth results
$sth->execute;
while(($var)=$sth->fetchrow_array) {

    # select the count -- only 1 result row
    $sth2->execute($var);
    ($count)=$sth2->fetchrow_array;
    # finish() here after each fetch?
    $sth2->finish;

}
# or should finish() for sth2 be called here 
# when sth2 won't be used again?
#$sth2->finish;

$dbh->disconnect;

        [download]
        Thanks for your wisdom!!
[reply]
[d/l]

In Section Seekers of Perl Wisdom