On Fri, Sep 12, 2008 at 9:53 AM, dan <[EMAIL PROTECTED]> wrote: > On Thu, 11 Sep 2008 17:23:22 +0530, Amit Saxena <[EMAIL PROTECTED]> > wrote: > > > Hi all, > > > > I am looking for a "fetch" function to fetch "n" number of records at a > > time > > in Perl DBI ! > > I had to do this kind of thing to implement 'record paging' in > Gtk2::Ex::DBI. The way I did it was in a couple of steps. > > 1) Run a query to *only* select primary key(s) from the records that you > want > 2) Store these primary keys in an array > 3) Assemble a query to select the fields you want, and add your primary > key(s) to the where clause > > So your final query would look like: > > select field_1, field_2, field_3 > from some_table > where some_primary_key in ( 1, 5, 10, 24, 30 ); > > ... and the numbers in the 'in ( )' bit would be populated from your array > of primary keys. > > This is a nice DB-neutral way of doing it, and you don't get bitten by the > DB server returning rows in a different order each time you run a query, > possibly returning duplicates across different 'pages' ( DB servers don't > guarantee the order that records are returned in unless you explicitly add > an 'order by' clause and list all relevant columns ). > > Feel free to check out Gtk2::Ex::DBI for a complete working example. I've > considered moving this functionality out into a generic record paging > module, but haven't ever really had the time to do it. >
The only issue with this approach is that two queries needs to be run for the same. Considering the tables containing 1 million (and more) rows, this two pass approach will not be good. What others say ? Regards, Amit Saxena
