I found this to be a problem for me as well. In retrospect, I think I could have been well served to stick with DBI as suggested above. However, I wrote my own pager for Class::DBI and Oracle using Data::Page. If you are using MySQL you can find similar code on CPAN already.

The invoking code looks like this: 



   use Data::Page;
   my $total_rows = $table->get_count_sql($sql_where_clause);

   # 
   my $pager = Data::Page->new($total_rows,
                               $G::CF{rows_per_page}, 
                               $current_page_num);

   # Now get only the needed chunk from the DB
   my @results = $table->get_range_sql($sql_where_clause, 
                                       $pager->first,
                                       $pager->last);

[download]
The supporting functions added to Class::DBI are on my scratchpad

The interesting bit was getting the SQL that would work with Oracle to provide a "limit" or an "offset" like MySQL. This ends up being ridiculously complicated: 

Select
  *
From (
   SELECT
        %s
        ,rownum R
   FROM
        (SELECT *
        FROM %s
        %s
        )
   )
WHERE R BETWEEN %s AND %s

[download]
Update:
Note that you need to be very careful about what you pass into the where clause. This code will allow bad things to happen if a tainted where clause is passed in.

-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday

In reply to Re: LIMIT clause in Class::DBI by freddo411
in thread LIMIT clause in Class::DBI by zby

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.