Paging through results

[scotth4v]

Hi Gang, I've built a bugtracker using servlets to pull data from my SQL 7 db and return the data as XML, format it with a series of XSL templates and display it. So far it's working great. Now I'm implementing a search feature. I could execute a search query that could potentially return hundreds of records... what would be the best way to implement a paging structure (ie. display records 1-15 of 225?) I've written one in ASP and JSP that looped through the recordset there, passing a parameter to the page and basing my next query on that, but using the XML batch-type data retrieval I'm currently using, what would be the best way?

There may be an easy answer that I've overlooked, I'm still trying to switch mindsets to get used to the servlet->Database->xml->xsl model instead of ASP/JSP->Database model.

Thanks!

-Scott

 

[chiph]

Ours writes the results of the SQL query to a temporary table, then using a counter, we loop through the cursor until we get to the starting point.

lStartRecord = (iPageSize * (iPageNumber - 1)) + 1


CREATE TABLE #tmpFetch(blahblahblah)

FETCH ABSOLUTE " & lStartRecord FROM test_cursor INTO @blahblah

WHILE (@COUNT<&quot; & iPageSize & &quot; AND @@FETCH_STATUS = 0)
BEGIN
INSERT INTO #tmpFetch VALUES(blahblahblah)
FETCH NEXT FROM test_cursor INTO
SET @COUNT=@COUNT+1
END

SELECT * FROM #tmpFetch
CLOSE test_cursor

 

[scotth4v]

Thanks a bunch, I hadn't thought about doing it with the database cursors...