http://qs321.pair.com?node_id=390898

ketema has asked for the wisdom of the Perl Monks concerning the following question:

Is there a generic way using DBI to limit the number of rows returned by a query regardless of the DB you are connecting to? I am debugging a program that is fairly long and has several places where large RS are returned. I really don't feel like adding LIMIT syntax to each query(especially because in some places the SQL is dynamic), I'd rather just be able to do something to the excute where it just gives me back say 100 rows rather than the whole thing. My other problem is that this particular script connects to various DB types so the syntax for the SQL is different even if I chose to add a LIMIT clause to each query.
Thanks

Replies are listed 'Best First'.
Re: DBI Row Limiting
by ccn (Vicar) on Sep 14, 2004 at 18:04 UTC

    You can override fetchall_arrayref method.

    Just create dbilimit.pl 

    use strict;
package DBI;

my $fetchall_arrayref;
our $ROWS_LIMIT;

INIT {
    $ROWS_LIMIT = 10;
    $fetchall_arrayref = \&fetchall_arrayref;
}

no warnings;

sub fetchall_arrayref {
    my ($sth, $slice, $max_rows) = @_;

    $max_rows = $ROWS_LIMIT
      unless defined $max_rows;

    $fetchall_arrayref->($sth, $slice, $max_rows);
}

1;

    [download]
    and require it after use DBI; line

    P.S. the code is not tested so it may have some bugs

[reply]
[d/l]
[select]
      This what I was looking for! I didn't want to have to re-write every instance of a fetchall_arrayref or hashref in the code to a while fetch loop, that would have chaned the logic pf the program. Overridding the methods in this manner is much more concise and easier. Thank you, it did the job.
[reply]
•Re: DBI Row Limiting
by merlyn (Sage) on Sep 14, 2004 at 16:38 UTC
    Just fetch the rows one at a time, up to your limit, then use $sth->finish to tell DBI you're done. On the databases for which it works, it does the job. On databases for which it doesn't (the whole query comes back regardless), you haven't lost anything.

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

[reply]
[d/l]
      I may do that if I have too, but I like to keep the structure of the program intact if possible. I am debugging it in a visual debugger, and I want to see the results of the call, just not ALL the results. I am assuming then that the answer is no to a DBI specific method that limits the number of rows returned?
[reply]

        There in fact are method(s) that limit the number of results you get. They're called fetchrow_*(). They limit the number of results to one row so you can control however many you actually obtain. Straight out of the DBI documentation:

        $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

$sth->execute( $baz );

while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
}

        [download]

        Now you can transform this a little to suit your needs like merlyn was talking about:

        my $LIMIT = 30;

my $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
$sth->execute( $baz );

my ( @rows, @row );
for ( 1 .. $LIMIT ) {
    last unless ( @row = $sth->fetchrow_array );
    push @rows, [ @row ];
}

#do stuff with @rows

        [download]

        This code is untested but you get the general idea. There are other types of fetchrow_*() methods that you can take a look at. Read the DBI documentation.

        Update: You can also take a look at the fetchall_arrayref() method which appears to take a $max_rows parameter.

        Zenon Zabinski | zdog | zdog@perlmonk.org

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom