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

Asho!

I guess I was paying more attention to the:
"free up resources"
part of the documentation.

Will I run into problems using the same variable name $sth in multiple calls without a finish(), like this: 
$sth=$dbh->prepare($sql);
$sth->execute;
while(($var)=$sth->fetchrow_array) { ... };

$sth=$dbh->prepare($sql2);
$sth->execute;
while(($var2)=$sthf->fetchrow_array) { ... };

[download]
I'm trying to make sure I understand how to do it right!

Replies are listed 'Best First'.
Re^5: DBI, prepare_cached, finish, and forks
by jZed (Prior) on Feb 24, 2006 at 02:41 UTC
    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.
[reply]
      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]

In Section Seekers of Perl Wisdom