Re^6: DBI, prepare_cached, finish, and forks

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

Comment on Re^6: DBI, prepare_cached, finish, and forks Select or Download Code

Replies are listed 'Best First'.
Re^7: DBI, prepare_cached, finish, and forks by runrig (Abbot) on Feb 24, 2006 at 17:22 UTC
If you know there will be only one row returned, fetch it and then call `$sth->finish()`.	[reply] [d/l]
Re^8: DBI, prepare_cached, finish, and forks by Anonymous Monk on Feb 24, 2006 at 19:08 UTC
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]
Re^9: DBI, prepare_cached, finish, and forks by runrig (Abbot) on Feb 24, 2006 at 22:35 UTC
finish() should be implicitly called whenever you re-execute a statement (so you could wait to call it), but I think it's better practice to just put it right after the fetch. And what you may want to do instead of the separate execute/fetch/finish statements is a single selectrow statement: `($count) = $dbh->selectrow_array( $sth2, undef, $var );` [download]	[reply] [d/l]