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

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;
      }
[download]

$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

Replies are listed 'Best First'.
Re: fetchrow_hashref within a loop yields "fetch() without execute()" by ikegami (Patriarch) on Oct 31, 2009 at 01:44 UTC
`$sth_spaces->execute(); ... foreach my $regid ( ... ){ ... foreach my $insid ( ... ){ ... while ( my $space = $sth_spaces->fetchrow_hashref() ){` [download] 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.	[reply] [d/l]


go ahead... be a heretic
	PerlMonks