Paging large result sets

gstroud · #1 (**permalink**) 08-31-11, 09:35

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

begooden-it · #2 (**permalink**) 09-27-11, 15:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech