I still haven't found any solution to DBD::mysql::st fetchrow

Furple has asked for the wisdom of the Perl Monks concerning the following question:

Oh wizened perl monks... help me! I've been poking around online to see if I can figure out what the error DBD::mysql::st fetchrow_array failed: fetch() without execute(). The first results told me that people had no clue, then I stumbled onto a response to a similar error that had happened here. The solution that time seemed to be simply removing a line of $sth->finish(); but unfortunately that's no help. The other thought is that the DB connection is just dying partway through. The second thing I can't figure out is why I get the DBD::mysql::st fetchrow_array failed: fetch() without execute() twice. ...in any case, any advice that can be offered would be very helpful.

Code Snippet:
if ($DEBUG) { print "Connected. \n";}
#populate names:
if ($DEBUG) { print "Populating names \n";}
my $sth = $dbh->prepare("SELECT distinct name FROM storeCategories");
$sth->execute();

my $count = 0;

while (my @names = $sth->fetchrow_array){
if ($DEBUG) { print "inputting $names[0] \n";}
$sth = $dbh->prepare("INSERT INTO names (name) VALUES (?)");
$sth->execute($names[0]);
$count++; #THIS IS LINE 27.
}
if ($DEBUG) { print "done with names inserted $count names \n";}

Gives output:
Connected.
Populating names
inputting Sample Kits
DBD::mysql::st fetchrow_array failed: fetch() without execute() at *******.pl line 27.
DBD::mysql::st fetchrow_array failed: fetch() without execute() at *******.pl line 27.

DB returns:
296 rows in set (for that same query).

Comment on I still haven't found any solution to DBD::mysql::st fetchrow_array failed: fetch() without execute()

Replies are listed 'Best First'.
Re: I still haven't found any solution to DBD::mysql::st fetchrow_array failed: fetch() without execute() by Your Mother (Archbishop) on Dec 11, 2008 at 00:31 UTC
`while (my @names = $sth->fetchrow_array){ $sth = $dbh->prepare("INSERT INTO names (name) VALUES (?)"); $sth->execute($names[0]); $count++; #THIS IS LINE 27. }` [download] You're clobbering your `$sth`. You have one to select (the `while` condition) and one to write (inside the `while` loop). Your selector is being overwritten, and replaced, by your writer. Try a new name for the inner `$sth`. Update- you should also move the writer out of the loop. You only need to prepare it once and you can reuse it all you like. `my $insert_sth = $dbh->prepare("INSERT INTO names (name) VALUES (?)"); while (my @names = $sth->fetchrow_array){ $insert_sth->execute($names[0]); } $sth->finish(); $insert_sth->finish();` [download]	[reply] [d/l] [select]
Re^2: I still haven't found any solution to DBD::mysql::st fetchrow_array failed: fetch() without execute() by Furple (Novice) on Dec 11, 2008 at 19:33 UTC
very helpful, thanks.	[reply]
Re: I still haven't found any solution to DBD::mysql::st fetchrow_array failed: fetch() without execute() by bradcathey (Prior) on Dec 11, 2008 at 02:33 UTC
Make your life easier with: `my $stmt="SELECT distinct name FROM storeCategories"; my $names = $dbh->selectrow_hashref($stmt, undef, $id);` [download] and get a nice human readable hash. Required reading. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l]