Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Question Unanswered: Paging large result sets

    I have a web application that displays paged grid information. The information it displays could be thousands of rows of data, but I only want to return a subset of say 50 rows at a time and the rows return dependo on the page the user selects.

    I'm new to Informix. Most of my experience has been with SQL Server and Oracle.

    SQL Server provide "Row_Number" "Over". Rown_Number returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

    I've done some research and it looks like maybe the use of a scrolling cursor might do it. Do anyone have any examples or links to more information on this type of problem?

    thanks

    Greg
    Last edited by gstroud; 08-31-11 at 11:11. Reason: Add post Icon type

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi greg,

    Informix syntax has the SCROLL CURSOR since the very beginning ( 1984 or so..)

    Syntax is:
    Code:
    DECLARE yourcursor SCROLL CURSOR FOR
    SELECT columns list FROM TABLE WHERE 
    <your where clause (eventually using ? placeholders>
    then
    Code:
    OPEN CURSOR yourcursor ( eventually USING values list )
    <some loop code>
    FETCH NEXT yourcursor INTO variables
    more code here....
    <end loop code>
    the SCROLL CURSOR can go forward: FETCH NEXT yourcursor
    go backwards : FETCH PREVIOUS yourcursor
    go relative: FETCH relative +10, FETCH relative -10 yourcursor
    go absolute : FETCH ABSOLUTE 23 yourcursor

    Caution, a SCROLL CURSOR uses "some temp table like" dataset to move between rows. If you go forwards and backwards and modify a row in the meantime, give preference to only selecting the primary key with the SCROLL cursor and read the row contents from this primary key. If you SELECT * in the SCROLL CURSOR, data will be read from the temp table and may remain stale if updated in the meantime.

    Good luck
    Eric

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •