Fwd: DBI sql and pagination for a flat-file

From: Leegold (leegold_at_fastmail.fm)
Date: 05/27/04

  • Next message: Jeff Zucker: "Re: Fwd: DBI sql and pagination for a flat-file" 
    To: dbi-users@perl.org
Date: Wed, 26 May 2004 21:10:45 -0400

    If someone could just tell me very schematically what they
    think...I'm not looking for anyone to write the code for me.
    ie. what's the best way to paginate a very big flat-file db?
    Actually it's looking like:
    http://www.stonehenge.com/merlyn/WebTechniques/col02.html
    to get pagination on a web page. Ok, I got more reading to do...

    On Wed, 26 May 2004 09:47:44 -0400, "leegold" <leegold@fastmail.fm> said:
    > I can access a flatfile with the DBI sql and have the pager "working",
    > But I'm not sure how to blend them. In reality i'll have a flat file
    > with over 1000 recs eventually paginated in CGI. There are some points
    > that confuse me. my newbie 1st try see below.
    >
    > *Question:* What I really want to do: If I just read the flatfile into an
    > array, or used tie-file, the pager as it is, would make more sense. But I
    > want to interface with the AnyData and use SQL - keep it on that level,
    > abstracting the fact I got a text file db. So, is there a way I can
    > fetch via the DBI/SQL *only* the recs I need for the pager - ie as the
    > pager needs them - more dynamically vs. a static monolithic array that
    > the
    > pager feeds off of as it needs. So DBI/SQL fetching 3 recs at a time per
    > below to get "stuff" for the pager. Wouldn't that be the way to go? How
    > can i do this?
    >
    > ---------------------code-----------------------------
    > #!/usr/bin/perl -w
    >
    > # use strict;
    > use DBI;
    > use Data::Page;
    > use Tie::File;
    >
    > my $file_name = 'H:\threesharp\datatest.threesharp';
    > my $table_name = 'pips';
    >
    > tie my @rec_array, 'Tie::File', $file_name;
    > my $n_rec = @rec_array - 1;
    >
    > my $dbh = DBI->connect('dbi:AnyData(RaiseError=1):');
    > $dbh->func( $table_name, 'ThreeSharp', $file_name, 'ad_catalog');
    > my $sth = $dbh->prepare("SELECT * FROM $table_name");
    > $sth->execute();
    >
    > while (my @row = $sth->fetchrow_array) {
    > print "@row\n";
    > }
    > print "\n\n";
    > my $i=1;
    > my $last;
    > do {
    > my $page = Data::Page->new($n_rec, 3, $i);
    > $i++;
    > print " Current Page: ", $page->current_page, "\n";
    > print "First entry on page: ", $page->first, "\n";
    > print " Last entry on page: ", $page->last, "\n\n";
    > $last =$page->last_page;
    > } until $i > $last;
    >
    > -----------------------output---------------------------
    >
    > H:\THREES~1>perl ThreeSharp.pl
    > o20473418 5th dimensional relativistics Johnson City, N.Y.
    > o09222777 A.S.M. review of metal literature Cleveland, Ohio
    > ocm13035605 AAOHN journal Atlanta, Ga.
    > ocm03569079 AAPG bulletin Tulsa, Okla.
    > o03050993 AAS photo-bulletin Gainesville, Fla.,
    > ocm10445058 AAVSO alert notice Cambridge, MA
    > o02884114 Abhandlungen der Akademie Gottingen,Vandenhoeck
    > o01478689 Abhandlungen der Deutschen Berlin
    > o05149137 American Mathematical Providence, R.I.
    > ocm00853306 Geological Society of America Boulder, Colo.
    > ocm38492184 Accident analysis and prevention New York, NY
    > ocm41383033 Accounting Journal Bradford, England
    > ocm37342142 Accounts of chemical research Easton, Pa.
    > o01731026 ACM computing surveys New York
    >
    >
    > Current Page: 1
    > First entry on page: 1
    > Last entry on page: 3
    >
    > Current Page: 2
    > First entry on page: 4
    > Last entry on page: 6
    >
    > Current Page: 3
    > First entry on page: 7
    > Last entry on page: 9
    >
    > Current Page: 4
    > First entry on page: 10
    > Last entry on page: 12
    >
    > Current Page: 5
    > First entry on page: 13
    > Last entry on page: 14
    >
    >
    > H:\THREES~1>
    >
    > --
    > http://www.fastmail.fm - Sent 0.000002 seconds ago 

    -- 
http://www.fastmail.fm - mmm... Fastmail...
  • Next message: Jeff Zucker: "Re: Fwd: DBI sql and pagination for a flat-file"

    Relevant Pages

    • DBI sql and pagination for a flat-file
      ... I can access a flatfile with the DBI sql and have the pager "working", ... with over 1000 recs eventually paginated in CGI. ... First entry on page: 1 ...
      (perl.dbi.users)
    • Re: Fwd: DBI sql and pagination for a flat-file
      ... > ie. what's the best way to paginate a very big flat-file db? ... The part of your question that has some DBI ... >>array, or used tie-file, the pager as it is, would make more sense. ... >>First entry on page: 1 ...
      (perl.dbi.users)