in reply to References of Eternal Peril

I've been wondering if the method below is terribly inefficient or if there is a better way to do the same thing. I'm using this method to return the results of a select statement to object attributes which can then latter be accessed. I'm thinking that this may be a bit of a memory hog, but the convenience is nice. Just looking for some thoughts and ideas on this.
Thanks
sub select {
    my $s       = shift;
    my $sql     = shift;
    my $sth = $s->dbh->prepare( $sql ) ||    
        $s->write_log("Can't prepare select ($sql): ".$s->dbh->errstr)
+;
    $sth->execute() ||
        $s->write_log("Can't execute select ($sql): ".$sth->errstr);
    $s->fields  = $sth->{NAME};    
    my $x = 0;
    my @ar = ();
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $ar[$x++] = $hr;
    }
    $s->rows = $x;     
    $s->ar   = \@ar;   
    $sth->finish();
}

[download]

Replies are listed 'Best First'.
RE: RE: References of Eternal Peril (while we're on the subject)
by merlyn (Sage) on Aug 11, 2000 at 18:01 UTC
    You can simplify most of this code: 
          my $x = 0;
      my @ar = ();
      while ( my $hr = $sth->fetchrow_hashref() ) {
          $ar[$x++] = $hr;
      }
      $s->rows = $x;     
      $s->ar   = \@ar;

    [download]
    to this: 
    my @ar = ();
while (my $hr = $sth->fetchrow_hashref() ) {
  push @ar, $hr;
}
$s->ar = \@ar;
$s->rows = @ar;

    [download]
    But I think there's a "fetchall_hashref" or something like that that could do this all in one fell swoop. Durn. Not enough window space on this speaker-lounge-area dumb PC to go looking up "man DBI". Sorry.

    -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]
[select]
      But I think there's a "fetchall_hashref" or something like that that could do this all in one fell swoop. Durn. Not enough window space on this speaker-lounge-area dumb PC to go looking up "man DBI". Sorry.

      Indeed there is:

      my @ar = ();
while (my $hr = $sth->fetchrow_hashref() ) {
  push @ar, $hr;
}
$s->ar = \@ar;
$s->rows = @ar;

      [download]
      ... can be written ... 
      $s->ar   = $sth->fetchall_arrayref({});
$s->rows = @{$s->ar};

      [download]

      On a side note, fetchall_arrayref() is one of my favorite methods because HTML::Template templates take an arrayref of hashrefs as a parameter for loops. You can output a table really easily like so:

      # Assume $sth is a valid statement handle
my $params   = $sth->fetchall_arrayref({});
my $template = new HTML::Template (filename => 'file.name');

$template->param(TABLENAME => $params);
print $template->output();

      [download]
      The template file looks like this: 
      <TABLE>
<TMPL_LOOP NAME=TABLENAME>
  <TR><TD><TMPL_VAR NAME=COLUMN1></TD><TD><TMPL_VAR NAME=COLUMN2></TD>
+</TR>
</TMPL_LOOP>
</TABLE>

      [download]
      The stuff between the <TMPL_LOOP> tags outputs for each row returned by the query, and it only takes two lines of perl to get the rows from the db to the template.

      -Matt

[reply]
[d/l]
[select]
        ... can be written ... 
          $s->ar   = $sth->fetchall_arrayref({});
  $s->rows = @{$s->ar};

        [download]
        Excellent. I've been looking for fetchall_hashref for ages! This is exactly what I need - I also use a template system (not HTML::Template) and it can do the same trick with arrayrefs of hashrefs (or arrayrefs).
        I guess I should look into converting my stuff to use bind_columns. Is there any (efficient) way you could tie an array to the results do you think?

        Thanks Matt.

        - Rich
[reply]
[d/l]

In Section Seekers of Perl Wisdom