Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Tie'ing a dbh result set?

by jfroebe (Parson)
on Jul 25, 2011 at 15:25 UTC ( #916537=perlquestion: print w/replies, xml ) Need Help??
jfroebe has asked for the wisdom of the Perl Monks concerning the following question:

UPDATE: This is what happens when you're out sick for nearly two weeks. Your brain goes to mush. A simple thing: create a Tie::Array subclass. head*desk

I have a bunch of legacy code that requires an array of arrayrefs. Basically:

  1. array ref to a result set
  2. another result set
  3. ...

For relatively small sets of data, this works quite well. However, it throws everything into memory. :( There are hundreds of scripts that rely on this array+arrayref structure so changing the scripts are impractical.

fetchall_arrayref() allows you to retrieve X many rows at a time. I'm thinking of something that behaves like an array but uses an iterator internally. Basically a TIE::Array to an iterator. An intelligent array. Anyone know of anything like that?

157 do { 158 my $tmp_rows = $sth->fetchall_arrayref; 159 push @result_sets, $tmp_rows; 160 } while ( $sth->{syb_more_results} );

Jason L. Froebe

Blog, Tech Blog

Replies are listed 'Best First'.
Re: Tie'ing a dbh result set?
by NetWallah (Canon) on Jul 25, 2011 at 18:30 UTC
    Here is what I use as a "callback" style iterator:
    sub Fetch_w_callback{ # Main DBI retrieval mechanism---- my ($sql, $callback) = @_; my $state = $_[2] ||= {}; # ** This THIRD param is CALL-by-ref ** $debug{SQL} = $sql; (my $sth=$dbh->prepare($sql))->execute(); $state->{ROW} = 0; while (my $row = $sth->fetchrow_hashref()){ $debug{ROW} = $state->{ROW}++; last if $callback->($row,$state) < 0; # Return negative to quit } $sth->finish; } #.... #Call sequence ... (In this case, used with CGI ... Fetch_w_callback( $sql, sub{ # This sub gets called once per row returned.... my ($dbRow,$LocalState) = @_; push @{$LocalState->{RAWDATA}},$dbRow ; # Raw data is availa +ble later $RawDataOnly and return $render_helper->(@_); # Called ONCE + per row, on Rawdata. if ($LocalState->{ROW} == 1 ){ # No Rows yet $fieldname[0] eq "*" and @fieldname = keys %$dbRow; print Tr( {-class=>"tableHeader"}, map ({td(b($_))} @fieldname), $extrafields- +>()) . "\n"; $_ = lc($_) for @fieldname; # DBI apparently only LC's } print Tr( map( {td($render_helper->($dbRow->{$_},$_,$dbRow) +)} @fieldname),$extrafields->(@_) ) . "\n"; } , $state);

                "XML is like violence: if it doesn't solve your problem, use more."

Re: Tie'ing a dbh result set?
by thargas (Deacon) on Jul 25, 2011 at 18:02 UTC

    It depends on how you access the info. The docs for tieing an array don't include any hook for iteration; they've only got: FETCH, STORE, FETCHSIZE, STORESIZE, EXTEND, EXISTS, DELETE, CLEAR, PUSH, POP, UNSHIFT, SHIFT, SPLICE, UNTIE, DESTROY.

    If you're only pulling the info out via unshift or pop, you might be able to make something work, but you'd have to booby-trap FETCH to ensure that the data was only accessed via unshift and pop. Sounds ugly. You may have to bite the bullet.

    Or maybe someone cleverer than I will have a brilliant method.

      Actually, it isn't that difficult to implement. Ultimately, I will be using a 'sliding window' for each result set to retrieve all the data transparently. As I really don't care about what I've already retrieved, I can pull back say 5,000 rows, on the request for 5001st row, purge the first 5000 rows and pull back another 5,000 from the database. The only thing I have to worry about is to to make sure the FETCH() and FETCHSIZE() handle the window.

      Jason L. Froebe

      Blog, Tech Blog

        Actually, as part of playing with a new toy I wrote, I noticed that for( @tiedarray ) actually deals with the size of the array changing each iteration so you don't need to know the number of records up-front like I initially worried. You can just always fetch at least one record ahead and return the size of what you have fetched so far -- for for( @tiedarray ) to work fine as an iterator.

        - tye        

        If you can guarantee that the array will only be accessed:

        • in order
        • read only
        • only once per element
        • you know how many elements there are
        then you can do it by simply writing a FETCH which returns the next resultset and a FETCHSIZE which returns the correct size. It would work correctly for usage like:
        foreach my $result_ref (@array) { process($result_ref); }

        Otherwise, please explain how you make it function as an iterator.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://916537]
Approved by moritz
Front-paged by tye
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2018-06-24 19:11 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.