Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

fetchrow_hashref within a loop yields "fetch() without execute()"

by Jazz (Curate)
on Oct 30, 2009 at 22:36 UTC ( #804231=perlquestion: print w/ replies, xml ) Need Help??
Jazz has asked for the wisdom of the Perl Monks concerning the following question:

Update: The second I posted this, I noticed that the while statement is making the fetchrow go out of scope (d'uh!). So the updated question is what is the most efficient/elegant way to achieve the structure that I need without it going out of scope?

Hello,

I'm receiving the "DBD::mysql::st fetchrow_hashref failed: fetch() without execute() " error when invoking fetchrow_hashref within loops. First the code:

my $sth_spaces = $dbh->prepare( "SELECT Spaces.*, SpaceTypes.SpaceTypeID,SpaceTypes.SpaceTypeName, COUNT(Spaces.SpaceID) from Spaces LEFT JOIN SpaceTypes ON SpaceTypes.SpaceTypeID = Spaces.Sp +aceTypeID WHERE Spaces.RegioID = $regioID GROUP BY SpaceID ORDER BY SpaceName "); $sth_spaces->execute(); my @regioloop = (); foreach my $regid ( sort { $a cmp $b } keys %$regia ){ my @insulaloop = (); foreach my $insid ( sort { $a cmp $b } keys %$insulae ){ my @spaceloop = (); while ( my $space = $sth_spaces->fetchrow_hashref() ){ push @spaceloop, { 'SpaceID' => $space->{'SpaceID'}, 'Doorway' => $space->{'Doorway'}, 'SpaceName' => $space->{'SpaceName'}, 'SpaceTypeID' => $space->{'SpaceTypeID'}, 'SpaceTypeName' => $space->{'SpaceTypeName'} }; } push @insulaloop, { 'InsulaID' => $insid, 'InsulaName' => $insulae->{ $insid }, 'SPACELOOP' => \@spaceloop }; } push @regioloop, { 'RegioID' => $regid, 'RegioName' => $regia->{ $regid }, 'INSULALOOP' => \@insulaloop }; push @{$fields->{'REGIOLOOP'}}, @regioloop; }
$fields is going to get tossed to HTML::Template for some happy TMPL_LOOP nesting, which is why I need this data structure.

If I pull the while statement outside of the foreach loops, it works fine. What is it about being inside the loop that makes this error come up, and how can I fix it, oh wise ones?

Thanks!
Jasmine

Comment on fetchrow_hashref within a loop yields "fetch() without execute()"
Download Code
Re: fetchrow_hashref within a loop yields "fetch() without execute()"
by ikegami (Pope) on Oct 31, 2009 at 01:44 UTC
    $sth_spaces->execute(); ... foreach my $regid ( ... ){ ... foreach my $insid ( ... ){ ... while ( my $space = $sth_spaces->fetchrow_hashref() ){

    In the first pass of the inner foreach in the first pass of the outer foreach, the while loop fetches all available data. There's none left for any other pass of the inner foreach loop, much less for other passes of the outer foreach.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://804231]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2014-09-24 03:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (244 votes), past polls