Note - I've never used MS-SQL, so what follows may be slightly off, but as MS-SQL is based on the Sybase code line it should still be relevant

Getting "pages" of result from an RDBMS is a popular question, and one that is essentialy a SQL question, really.
For MS-SQL and/or Sybase there is no direct way of only retrieving rows x,y from a result set. However, you can trick the server into doing it for you. For example, with Sybase I can write the following: 

set rowcount $last_row
select tb.pk, id=identity(10) into #tmp
  from the_table tb
 where some condition
 order by something or other

set rowcount 0

select tb.pk, other columns
  from the_table tb
     , #tmp t
 where tb.pk = t.pk
   and t.id between $first_row and $last_row

[download]
This can be run as a single DBI prepare() call, and the second query will return the rows from the result set that are positioned between $first_row and $last_row.

The caveats with this technique is that as you page forward (or back) the results that you display may change if the underlying data changes. The initial set rowcount $last_row will limit the number of rows that are pulled from the base table and stuffed into the temporary table - usefull if the base table has a lot of rows that match the query.

An alternative solution is to fetch a large subset (say the first 10 pages worth) of primary keys and cache them locally. Then as the user pages forward (or back) issue a query with an IN clause with the primary keys for that set of rows.

As for the rows() not returning the correct (in your opinion :-) data - well that's documented. MS-SQL, Oracle, Sybase (and others) can't provide you with the count of the number of rows affected by a SELECT query until all the rows have been fetched. This is because the server doesn't know how many rows it has processed until it has processed all the rows (and finding out would take more time, and might not be correct in a multiuser environment anyway)

Michael

edited: to fix typo - thanks Abigail-II!

In reply to Re: Perl DBI MS SQL Question by mpeppler
in thread Perl DBI MS SQL Question by peppiv

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.