Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Record Paging Question

    Hi Everyone. This is my question - I have a stored proc that retrieves rows from X to Y, given a WHERE and ORDER BY clause. [thanks to your help] this works fine. OK - cool.

    This is the dilemma...This is being tied to an ASP page, and the records being paged 15 at a time. Again, this works without issue however the problem is when the results are done, I just get an [ungraceful] error. I cannot use the EndOfFile command because the stored procedure has only received 15 results and therefore does not even know if it is the EndOfFile. I posted something like this here and someone was nice enough to mention oracle does not know if there are more results until the last record has been attained. This makes sense. Which as far as I see it, leaves me a few choices...
    1-Page everything into an array in the SP
    2-Create a temporary table [that is deleted at the end of the session] and retrive the records from there
    3-Create something in the SP that tells me the number of records that will be returned
    4- I have no idea do you?

    For reference purposes my Stored Procedure follows:
    Code:
    CREATE OR REPLACE PACKAGE gett
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE gett
    	(
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY gett
    AS
    	PROCEDURE gett
    	(	
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 'select * from (select p.*, rownum rnum from (select * from 
    
    tbl_Reqs ' || wh || ' '|| srt || ') p where rownum < '||lastrec||') where 
    
    rnum >= '||firstrec;
    
    END;
    
    
    END;
    /

  2. #2
    Join Date
    Dec 2003
    Posts
    148
    Scratch that. What if I add something like this to my SP:
    select count (*) from tbl_reqs where Column1 = 'SomeData'

    and I move that value into a variable...Then I Page records until that value is reached? Is that plausible? My only question with that would be, let's say I was paging 15 records and there were 18 left....?

    Also, How would I move that value into a variable (the select count (*)) and run both select statements in my SP?

    Thank you for your help

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    My feeling is that the approach in your first post is the right one. The issue is how to gracefully handle the EndOfFile in ASP (not in the stored procedure). All result sets come to an end at some point, whether it is after fetching 15 records, 1 record or 0 records, and ASP must be able to deal with that.

    Maybe this needs to be posted in the ASP forum?

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    Andrew.. You're right, I'd much rather deal with this on the ASP side of things but I have the feeling they're going to tell me to walk my butt back to the oracle forum. But hey, one way to find out!

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I am curious as to how you manage the cursor back in ASP ?

    Show us how are you doing it, so we can have more ideas maybe. Alternatively, you could add another output variable in your stored procedure, outputting a count(*) of the current query your cursor has.

  6. #6
    Join Date
    Dec 2003
    Posts
    148

    ...

    JM- That is pretty much what i was thinking. If I could run a count(*) given my WHERE clause and ORDER BY arguments, and then run that number as the higherbound, that should work. The problem is if I am paging records 15 at a time, I'm not sure if I would be able to make my "next" button disappear. The only thing I could think of is this..
    Creating a temp table for the session that is populated given the stored procedure arguments -
    that way I have one table and I can determine the EOF easily.
    And syntax-wise I'm not sure how to go about that.

    In terms of calling my SP in my ASP page, as you asked - I am only doing the following:
    Code:
    objRS.Open "gett.gett('" & ipage & "','" & ipage + 15 & "'," & strWhere & "','" & strSort & "',:r)",objConn
    Then, I reference that and loop through a table until the recordset is done (in this case the table is always 15 records long)
    However, when there are no more records, it nosedives.

    ANy ideas?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    All the ASP programs I have ever worked with cope quite happily with reaching the end of the result set (it would be pretty hopeless if they couldn't!). The code looks like:
    Code:
    do while not objRS.EOF
      ...
      objRS.MoveNext
    Loop
    Why won't that work for you?

  8. #8
    Join Date
    Dec 2003
    Posts
    148
    In terms of the table being built to display the data, that is the exact code I use - and it works fine, for building the table (of 15 rows since I am paging by15)

    I am using this code to display the next button:
    Code:
    If  NOT objRS.EOF then
    
    Response.Write "<A HREF='temp.asp?page=" & Request("page") + 15 &"&fld=" & iFld & "&comp=" & iComp & "&val=" & cVal & "&sort="& iSort & "'>next</a>"
    
    else 
    
    response.write "No More Records To Display"
    
    end if
    Which does not work?

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    From the procedure definition, I can see that you are telling how many rows you are about to retrieve actually (rownum < lastrec; which in your case is 15), so that would avoid the exectuion of a second select!

    As of the ASP solution:
    (1) Use the the Recordset PageSize property to set it to 15 (or whatever you want it to be).
    (2) Scroll records from 15 to 15 with the AbsolutePage Property. AbsolutePage = 1 will result in the first 15 record, 2 will be next 15 records, and so on.
    (3) Make a condition within your page on whether or not to show the "Next" button depending on a condition of:

    Code:
     If objRs.PageCount > objRs.AbsolutePage Then
      ' I will show the Next Button since they are more records to retrieve
     End If
    Last edited by JMartinez; 07-22-04 at 15:41.

  10. #10
    Join Date
    Dec 2003
    Posts
    148

    ...

    JM, using that code I get:

    Error Type:
    ADODB.Recordset (0x800A0CB3)
    Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.
    /temp.asp, line 142

    Do you happen to have a full example of the asp code doing this with oracle? if so it'd be fantastic to see it.
    Thanks

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What provider are you using with your connection object? Try to open the recordset with a adOpenStatic cursor.

    This is not Oracle's especific code, but rather standard ASP code to scroll through page. Try www.aspin.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •