Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

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?


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?


Replies are listed 'Best First'.
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?

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (2)
As of 2021-12-04 15:00 GMT
Find Nodes?
    Voting Booth?
    R or B?

    Results (30 votes). Check out past polls.