Reg: Pagination query in Informix 9.x / linux
A developer needed to paginate a 50,000–100,000 row result set (1,000 rows per page) from IDS 9.x over JDBC, with users able to jump to arbitrary pages, and found FIRST couldn't be used in a subquery. Suggestions were: keyset paging (fetch rows with key greater than the last key of the previous page, which rules out page skipping), middleware that caches the result set and returns a cookie plus row offset/page size, and SELECT SKIP n FIRST m. The poster rejected keyset paging because users jump pages and delete rows, and reported that SKIP...FIRST is not available in 9.x (it came later). A confusing "That works!" reply with no quoted context left the thread without a clear recorded resolution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I need to display the details of recipients in a application.This could be around 50,000 to 100,000 records. We wanted to display 1000 records per page so as to avoid the java.lang.OutOfMemory error. We use JDBC to communicate to the IDS. I found out that we cannot use FIRST in sub query(P.S I am new to Informix.) The user will give the page that he wants to view and the size of the page. Can Someone kindly provide me a solution. Thanks, Ganesh
I assume each record has a unique key that you are ordering on. Page>1 requests just have to specify that the key be > the last key retrieved on the prior page. You can't do skip page requests easily this way, but for page-at-a-time retrieval it works well as long as the sort keys are indexed. Art S. Kagel ----- Original Message ----- From: Vijayakumar Ganesh Kumar <ids@iiug.org> At: 2/15 11:24:48 Hi All, I need to display the details of recipients in a application.This could be around 50,000 to 100,000 records. We wanted to display 1000 records per page so as to avoid the java.lang.OutOfMemory error. We use JDBC to communicate to the IDS. I found out that we cannot use FIRST in sub query(P.S I am new to Informix.) The user will give the page that he wants to view and the size of the page. Can Someone kindly provide me a solution. Thanks, Ganesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Another option is to use middleware that caches the query results and recognizes paging. First requests loads cache and returns a 'cookie'. Client returns the cookie with first row ordinal and pagesize and gets back the appropriate page. That even works for page skipping. I've done that one. Art S. Kagel ----- Original Message ----- From: Art Kagel <ids@iiug.org> At: 2/15 11:30:11 I assume each record has a unique key that you are ordering on. Page>1 requests just have to specify that the key be > the last key retrieved on the prior page. You can't do skip page requests easily this way, but for page-at-a-time retrieval it works well as long as the sort keys are indexed. Art S. Kagel ----- Original Message ----- From: Vijayakumar Ganesh Kumar <ids@iiug.org> At: 2/15 11:24:48 Hi All, I need to display the details of recipients in a application.This could be around 50,000 to 100,000 records. We wanted to display 1000 records per page so as to avoid the java.lang.OutOfMemory error. We use JDBC to communicate to the IDS. I found out that we cannot use FIRST in sub query(P.S I am new to Informix.) The user will give the page that he wants to view and the size of the page. Can Someone kindly provide me a solution. Thanks, Ganesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I think you can use a query like select "select skip ? first ? * from table_name". Here the user may skip a certain number of records and fetch the first n records. eg: select skip 0 first 1000 from table1. After this you could change the value of "0" to "1000" and "1000" to "2000" and so on based on your requirements. Thanks, Ramesh. "VIJAYAKUMAR GANESH KUMAR" <ganesh17jan@gmail.com> Sent by: ids-bounces@iiug.org 15/02/2007 21:54 Please respond to ids To ids@iiug.org cc Subject Reg: Pagination query in Informix 9.x / linux [8411] Hi All, I need to display the details of recipients in a application.This could be around 50,000 to 100,000 records. We wanted to display 1000 records per page so as to avoid the java.lang.OutOfMemory error. We use JDBC to communicate to the IDS. I found out that we cannot use FIRST in sub query(P.S I am new to Informix.) The user will give the page that he wants to view and the size of the page. Can Someone kindly provide me a solution. Thanks, Ganesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks for the reply. Obviously that is not the option for me as the user can go to page 100 from 1. And the user will also delete entries in between . so keeping key as the option to page wont be accurate in my case.
That Works!!! Thank you very much.....
UnFortunately this doesn't work in Informix 9.x :( On 2/16/07, Ramesh G Srinivasan <ramesrin@in.ibm.com> wrote: > > I think you can use a query like select > > "select skip ? first ? * from table_name". > Here the user may skip a certain number of records and fetch the first n > records. > > eg: select skip 0 first 1000 from table1. > After this you could change the value of "0" to "1000" and "1000" to > "2000" and so on based on your requirements. > > Thanks, > Ramesh. > > "VIJAYAKUMAR GANESH KUMAR" <ganesh17jan@gmail.com> > Sent by: ids-bounces@iiug.org > 15/02/2007 21:54 > Please respond to > ids > > To > ids@iiug.org > cc > > Subject > Reg: Pagination query in Informix 9.x / linux [8411] > > Hi All, > > I need to display the details of recipients in a application.This could be > > around 50,000 to 100,000 records. > > We wanted to display 1000 records per page so as to avoid the > java.lang.OutOfMemory error. > > We use JDBC to communicate to the IDS. > I found out that we cannot use FIRST in sub query(P.S I am new to > Informix.) > The user will give the page that he wants to view and the size of the > page. > Can Someone kindly provide me a solution. > > Thanks, > Ganesh > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
WHAT WORKS??? It's bad to strip all prior text when replying to the forums. Some of us use emailer that don't thread, and even where we do thread emailers can get confused and we might want to delete threads but are interested in later replies. Please quote relevant sections of prior postings when replying. Art S. Kagel ----- Original Message ----- From: Vijayakumar Ganesh Kumar <ids@iiug.org> At: 2/16 2:30:02 That Works!!! Thank you very much..... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.