Re: Paging Results Sets (Next 10 records)

Michael E. Lucas wrote: > > Hi Gang, > > I'm looking for a way to page the results of a SELECT statement into > many pages. I have a database will x number of records and what to only > return 10 records at a time. I'm trying to do the web search engine > thing where I can give my users "next 10" or "previous 10" buttons for a > query they run. > > Is there an easy solution out there? Several, none great. Here: 1) Use SCROLL cursors and multiple connections. Use the cookie to identify which connection owns the cursor for a cookie. Problem: When to release the cursor? 2) Store cookie and data window data along with the query. When additional requests come in use the cookie to restore the query and either adjust the where clause to skip rows or skip over then yourself. 3) Read out the entire query when it is first executed and cache the data yourself, keyed by the cookie. Here you are simulating the SCROLL cursors yourself and don't have to worry about when to release the cursor. You could age out the data cache if it is needed to support other users and just keep enough info to fall back to something like 2) above and then restore the cache. That takes care of the WEB user who makes a query, goes to lunch and clicks NEXT-PAGE when he comes back an hour or so later. (I've done this one, not on a WEB application, but under similar conditions and even presented a middleware server idea based on this technique to the NY Users Group three years ago.) Art S. Kagel