Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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

Comment on Tie'ing a dbh result set?
Download Code
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

        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.

        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        

Re: Tie'ing a dbh result set?
by NetWallah (Abbot) 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."

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 the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (8)
As of 2015-07-04 02:16 GMT
Find Nodes?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...

    Results (57 votes), past polls