| Author |
Topic |
|
malikh
Starting Member
7 Posts |
 Posted - 2002-07-24 : 18:15:11
|
| I am calling some stored procedures from a JSP front end. The JSP calls the specific stored procedure associated with the page. The result set is then displayed as an HTML block in the JSP page.The problem is that it can only display upto 50 records at a time. And the result set may contain 300 records. How can I tell the stored procedure which next 50 to get without passing an out variable? |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-24 : 19:19:41
|
| Not really a good site for a JSP question. I do know they've done an article on this before... Try www.4guysfromrolla.com-----------------------Take my advice, I dare ya |
 |
|
|
Billpl
Yak Posting Veteran
71 Posts |
Posted - 2002-07-25 : 00:29:23
|
| I think what you're asking for is just returning a page (subset) of a larger query. The best way I know is to run a query with a Row Number. But since SQL doesn't have a "RowNumb" feature you have to create it yourself. See http://www.sqlteam.com/item.asp?ItemID=1491.You will however have to pass to the SP the page number and amount of rows in the page. |
|
|
|
rksingh024
Yak Posting Veteran
56 Posts |
Posted - 2002-07-25 : 00:45:10
|
| Search for ArticleWhat's after topRamesh |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-07-25 : 07:48:48
|
| Not sure about JSP but in ASP with ADO the recordset object has a built in paging mechanism. You might want to look and see if the JSP data objects allow the same functionality (I'd imagine they do).Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
Billpl
Yak Posting Veteran
71 Posts |
Posted - 2002-07-25 : 20:53:31
|
| The question I've never got an answer on with ADO paging is when some joker put a search on 'A%' and the table has 30,000 qualified rows. Does SQL actually send all 30,000 or is it smart enough to only send the 50 or so rows that ADO only needs? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-25 : 22:08:16
|
That depends on the CursorLocation property that you set. If it is set to server then the cursor resides on the server and only those rows specifically requested/fetched will be transmitted to the client. Technically anyway.  |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-07-26 : 08:02:30
|
| Also, there is a CacheSize property which you can set to the number of rows you wish to return, this (at least when using Access) only returns the number of records you want, no matter how large the parent, and I do believe you have to have the cursor on the server. I have successfully paged 100's of thousands of records (in Access believe it or not) by setting CacheSize = 50 (for 50 records).Yeah I know, it's insane to be paging that amount of data, no argument...but that's a discussion I've already had previously in these forums. :)Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-07-27 : 04:27:34
|
| Take a look at Solution 3 in this article:[url]http://www.15seconds.com/Issue/010308.htm[/url]The trick is to perform the search query and only store the primary key in the temp table. Then you can join this table to to any others when outputting the results.macka. |
|
|
|
Billpl
Yak Posting Veteran
71 Posts |
Posted - 2002-07-29 : 19:51:49
|
| Thanks MacKa for the Link. We do a lot of work with FoxPro as a client and middle tier. We use Stored Procedures exclusively for SQL and Oracle databases. All the pages on large queries (or any paged data for that matter) are resolved in the stored procedure level via a row numbering schema. I was told if we converted to ADO (instead of Foxpro Cursors) we could eliminate the paging process. I appears from the Link that this is not true. |
|
|
|
|
How to return 50 records at a time?