Re: retrieving data from the table in chunks for paging

You don't mention what you are using for a front-end, but in most of them including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage of IDS's SCROLL CURSOR feature: struct data *getapage( int pagenum, int pagesize ) { static int first = 1; exec sql begin declare section; int firstrow, row; exec sql end declare section; if (first) { first = 0; declare pages scroll cursor for SELECT * FROM mytable; <error handling> open pages; } firstrow = ((pagesize * (pagenum - 1) + 1); for (row= firstrow; row<(firstrow + pagesize); row++) { FETCH ABS pages INTO :dataarray[row].*; .... } } Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 27, 2009 at 3:57 PM, Gentian Hila <genti.tech@gmail.com> wrote: > I have a table that has huge amounts of data and I want to retrieve > the data from the table in blocks through an application. > > I don't want to load all the data into memory and then create pages > from there. Instead I want to get the data in blocks from the table > and display them at each page and when the user goes to next page I > can get the next block. > > So first page should execute something like: > > select * from invoice> > FOR ROWS FROM 1 -100 > > and then the second page from 101 - 200 and so on. > > > Is there any row number or any way that I can count the rows that I am > retrieving in IDS 9.40? > > Thank you > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote: > You don't mention what you are using for a front-end, but in most of them > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage of > IDS's SCROLL CURSOR feature: > Since she didn't say what application or front end tools, I don't know that it would be a good idea to use a scroll cursor. If they're using a web interface, you're kind of screwed. You have to maintain state and you can't pool your connection threads if you use a scroll cursor. (The cursor has to remain active until the session times out so when you dynamically create the cursor, you'd have append the session_id to the cursor name. It can be done, but its ugly and kind of violates some of the design patterns.) This would mean that you'd have to design a pk for the table and then track the last row retrieved and get the next set of rows. Even this has some issues in a web app... But I do agree, if you're doing straight client/server with 4GL and or Java, yeah you can use a scroll cursor. HTH -G

On Mar 27, 7:47 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote:> You don't mention what you are using for a front-end, but in most of them > > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage of > > IDS's SCROLL CURSOR feature: > > Since she didn't say what application or front end tools, I don't know > that it would be a good idea to use a scroll cursor. > If they're using a web interface, you're kind of screwed. You have to > maintain state and you can't pool your connection threads if you use a > scroll cursor. > (The cursor has to remain active until the session times out so when > you dynamically create the cursor, you'd have append the session_id to > the cursor name. It can be done, but its ugly and kind of violates > some of the design patterns.) > > This would mean that you'd have to design a pk for the table and then > track the last row retrieved and get the next set of rows. Even this > has some issues in a web app... > > But I do agree, if you're doing straight client/server with 4GL and or > Java, yeah you can use a scroll cursor. > > HTH > > -G I never think about using scrolling cursors because we have a web application and we don't keep any state information so we have to do it the way that I mentioned.