Tie::STHRows for HTML::Template loops

I use HTML::Template to generate webpages. An annoyance of mine has always been needing to collect hashrefs from a DBI statement handler into an array so I can use the information in loops. I finally broke down and wrote this module. Yes, I am aware of $sth->fetchall_hashref. Unfortunately, this is quite memory intensive if there is a lot of data to be fetched. I hope someone else finds this module useful.

package Tie::STHRows;

use base Tie::Array;

sub TIEARRAY {
  my $class = shift;
  my $sth = shift;
  die "You must pass a statement handler!" unless ref($sth) eq "DBI::s
+t";
  my %row;
  $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
  bless ([$sth,\%row],$class);  
}

sub FETCH {
  my $self = shift;
  $self->[0]->fetch;
  return +{ %{$self->[1]} }; # per tye's suggestion
}

sub FETCHSIZE {
  ${+shift}[0]->rows;
};

sub DESTROY {
  ${+shift}[0]->finish;
}

1;

__DATA__
sample snippet for how it works:

$sth = $dbh->prepare(...);
$sth->execute;
tie @loopdata, Tie::STHRows, $sth;
$template->param(LOOPDATA => \@loopdata);

and your template:

<TMPL_LOOP NAME=LOOPDATA>
...
</TMPL_LOOP>
[download]

Comment on Tie::STHRows for HTML::Template loops Download Code

Replies are listed 'Best First'.
(jeffa) Re: Tie::STHRows for HTML::Template loops by jeffa (Bishop) on Jul 30, 2003 at 13:51 UTC
Very cool stuff, but i think i may have found a bug. I tested with this: Read more... (4 kB) The problem is context. `Tie::STHRows::FETCH` is returning a list of one hash ref (the rest in that list are references back to that hash ref) if you use your tied array outright. If you use it like an iterator, then all is well. I modifed `Tie::STHRows::FETCH` to return a (get this) new anonymous hash reference that contains the contents of the hash reference to be returned: `sub FETCH { my $self = shift; $self->[0]->fetch; my %row = %{$self->[1]}; return {%row}; }` [download] However, this may hinder performance ... but it works for me now: Read more... (324 Bytes) Like i said, i really like this module and would more than likely use it often if it were available via CPAN. I do suggest that you rename it to `Tie::Array::STHRows` - a while back japhy called for authors to pick better names for Tie'ed modules, and since yours inherits from Tie::Array, i think it is fitting to name it appropriately. Fun stuff. :) antirice++ jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: (jeffa) Re: Tie::STHRows for HTML::Template loops by antirice (Priest) on Jul 30, 2003 at 14:03 UTC
Agh! I showed a friend how to use this module when just trying to display database results day and he thought it was darned nifty and suggested I post it somewhere. When I went to copy and paste, I seem to have used an old version (was wondering why I had to remove code for shifting). Ah well, thanks for pointing this out to me. :) antirice The first rule of Perl club is - use Perl The ith rule of Perl club is - follow rule i - 1 for i > 1	[reply]
Re: Tie::STHRows for HTML::Template loops (extra copy) by tye (Sage) on Jul 30, 2003 at 16:20 UTC
`return { my %row = %{$self->[1]} };` [download] Note that this copies the hash to %row and then copies that into an anonymous hash. This second copy is of no use. Either avoid the copy into %row or just return a reference to it: `return +{ %{$self->[1]} }; #or return do { my %row = %{$self->[1]}; \%row }; #or my %row = %{$self->[1]}; return \%row;` [download] And there are other ways to do it, of course. Several ways that don't work: `return { %{$self->[1]} }; # {} interpretted as a block return \my %row = %{$self->[1]}; # \ binds tighter than =, so same as: # (\my %row) = %{$self->[1]}; return \( my %row = %{$self->[1]} ); # flattens hash and returns ref to each item` [download] and several others that don't work either. (: - tye	[reply] [d/l] [select]