Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

SQL Paging with Perl

by Anonymous Monk
on Oct 05, 2006 at 13:01 UTC ( #576524=perlquestion: print w/replies, xml ) Need Help??

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

Hi Monks!

I need some help here, I have a table im my DB with about 2000 records, I would like to use Perl to show 20 records at the time to the user, I am using MS SQL Server, I can't use LIMIT, but I think it can be done using Perl.
Anybody out there that would know how I could to this?

Thanks very much!

Replies are listed 'Best First'.
Re: SQL Paging with Perl
by LTjake (Prior) on Oct 05, 2006 at 13:38 UTC

    You could try using SQL::Abstract::Limit. It has a way to generate queries that will only retrieve the page of results you're looking for. MSSQL would require the use of the Top dialect: (see the docs)

    use SQL::Abstract::Limit; my $sql = SQL::Abstract::Limit->new( limit_dialect => 'Top' ); # generate SQL: my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order +, $limit, $offset ); # Then, use these in your DBI statements my $sth = $dbh->prepare( $stmt ); $sth->execute( @bind );

    --
    "Go up to the next female stranger you see and tell her that her "body is a wonderland."
    My hypothesis is that she’ll be too busy laughing at you to even bother slapping you.
    " (src)

Re: SQL Paging with Perl
by davorg (Chancellor) on Oct 05, 2006 at 13:06 UTC

    You might find something like Data::Page to be useful.

    --
    <http://dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: SQL Paging with Perl
by kwaping (Priest) on Oct 05, 2006 at 15:04 UTC
    This is an object method that I wrote to paginate data in an array.
    sub paginate { # usage: my $indices = $obj->paginate($records,$per_page,$page); # # parameters: $records = numeric, number of records (scalar @results, +for example) # $per_page = numeric, how many records to display per page # $page = numeric, what page we're currently on # # return values: fail = empty array # success = array or array ref of indicies to use with the original @ +results # example: @results[@indices] or @results[@$indices] my $self = shift; my ($records,$per_page,$page) = @_; my $indices; # record count is required, can't make it up return (wantarray ? () : []) unless ($records && $records =~ /^\d+ +$/); # defaults for others $per_page ||= 10; $page ||= 1; # calcuate last page my $last_page = int($records / $per_page); $last_page++ if ($records % $per_page); # page safeguards $page = 1 if ($page < 1 || $page > $last_page); # calculate offset, starting with 0 my $offset = ($page - 1) * $per_page; ######################## ### results per page ### ######################## if ($offset + $per_page <= $records) { # normal operation $indices = [$offset .. $offset + $per_page - 1]; } else { # don't go past the end of the array! $indices = [$offset .. $records - 1]; } return wantarray ? @$indices : $indices; }

    Update: I just noticed I basically reinvented Data::Page. Doh! I find it funny that the author of that module and I even use the same parameters, and in the same order. From the docs:

    my $page = Data::Page->new($total_entries, $entries_per_page, $current_page);

    ---
    It's all fine and dandy until someone has to look at the code.
      Doh! I find it funny that the author of that module and I even use the same parameters, and in the same order.

      I often think about that when I hear about patent lawsuits, and the oft-quoted line "There is no stopping an idea whose time has come" (Victor Hugo?). When you consider that most of us are put through the same basic learning system, are bombarded daily with the same media, and have similar western philosophical minds, it definitely changes the odds that people will write identical code phrases.


      I'm not really a human, but I play one on earth. Cogito ergo sum a bum
Re: SQL Paging with Perl
by VSarkiss (Monsignor) on Oct 05, 2006 at 16:17 UTC
Re: SQL Paging with Perl
by radiantmatrix (Parson) on Oct 06, 2006 at 14:07 UTC

    Ah, I feel your pain: I recently had to paginate results from SQL Server, and wasn't allowed to modify the SQL in any way (stupid over-defensive stored proc devs...). Here's the basic idea of the solution I came up with.

    # a unique SessionID for the query already available # in package global $session my $cache_file = $session.'_cache.yaml'; my $result_set; if (-f $cache_file && -r $cache_file && -s $cache_file) { # we already ran this query (session), and we have a cache! $result_set = YAML::LoadFile($cache_file); } else { $result_set->{data} = run_dbQuery(); $result_set->{start} = 0; } display_results( $result_set->{data}, $result_set->{start}, 20 ); $result_set->{start} += 20; YAML::DumpFile($cache_file, $result_set); #---- display_results : shows slice of result set ---- sub display_results { my ( $data, $start, $length ) = @_; die "Data Set is not an ARRAY ref" unless ref $data eq 'ARRAY'; my $endpoint = ( $start+$length > @$data ? @$data-1 : $start+$lengt +h-1 ); show_toBrowser( @$data[$start..$endpoint] ); }

    Essentially, I cache the full result set in a YAML file. When I need the next page, I restore the array in the YAML file and just use an array slice to get the subset I want. This might be a bad idea for huge data sets, but it should get you thinking in the right direction (assuming you can't use SQL Server to do the pagination for you, which will likely be much faster).

    I deal with cache expiration and removal in session restoration and destruction calls elsewhere.

    <radiant.matrix>
    A collection of thoughts and links from the minds of geeks
    The Code that can be seen is not the true Code
    I haven't found a problem yet that can't be solved by a well-placed trebuchet

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://576524]
Approved by Joost
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2020-01-23 11:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?