Results 1 to 9 of 9

Thread: Pagination

  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Pagination

    Hi, i'm new to the boards...and somewhat new to Sybase (come from an oracle/mysql background). Anyways, one of the concepts that I see hard to grasp when building a web based front end for my Sybase ASE 12.5.3 database is pagination.

    This is when records are split into a list of pages that users can view at one at a time. With oracle I used a combination of functions such as START WITH etc. to do the job. With MySql you can use LIMIT (startrowindex, endrowindex).

    Is there an equivalence in Sybase? I have done exhaustive searching on this board and have found suggestions with set record count but this is hard to implement as I don't know what I would use to tell Sybase to start with which particualr row number on the next page (as is the case in ORDER BY queries that don't necesseraliy return the primary key in sequential order)

    please help

  2. #2
    Join Date
    Feb 2005
    Posts
    4
    anyone ?

  3. #3
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    There are several suggestions that I have seen before. One of them is to select the result set into a temp table with an indentity column, and then use that as your "rownumber" in subsequent queries:

    ie:

    select *,rowid = identity(10)
    into #mylist
    from sysobjects
    where <my criteria>

    select * from #mylist where rowid between 1 and 50
    select * from #mylist where rowid between 51 and 100
    etc.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    http://www.isug.com/Sybase_FAQ/ASE/s....2.html#6.2.12

    Shows my stored procedure trick (as learned from John McVicker).
    Thanks,

    Matt

  5. #5
    Join Date
    Feb 2005
    Posts
    4
    ok, thanks guys...both methods look promising.

    so it looks like the first method (KSherlock's) builds a temp table with a pseduo-key called rowid. Then, it querys that temp table where the identity column matches the range of the first and last records. OK..couple questions:

    1. What is the lifespan of the temp table? is it only for the life of the procedure?
    2. How would it perform if loaded with 15,000 records?
    3. Are temp tables joinable with physical tables?

    the second method (MattR's), i've seen before while searching google. it looks like the ideal smart solution...however, I have trouble grasping what @min_postid exactly does...could you please explain its role?

    Thanks,
    Mike

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Code:
        SELECT @min_postid = postid
         FROM post
        WHERE ...
        ORDER BY postid ASC
    
       SET ROWCOUNT @perpage
    
       -- we know where we want to go (say the 28th post in a set of 50).
        SELECT ...
          FROM post
         WHERE postid >= @min_postid
               ...
      ORDER BY postid ASC
    Well, the code was for a forum system like this, so you only want to display a certain subset of posts on a given page. PostID, in this case, is an IDENTITY column although it could be any sort of sortable column.

    You first determine how many posts you want per page then calculate how many rows you need to offset (posts per page * page number to display). You then iterate through all of your posts to get the postID which is the first post to be displayed. This is @min_postid. You can then say "Show me all posts greater/equal to @min_postID and stop after #per_page of them).
    Thanks,

    Matt

  7. #7
    Join Date
    Feb 2005
    Posts
    4
    thanks for the reply MattR.

    Everything makes sense to me except exactly what is happening here:
    (It could be due to my lack of experience)
    Code:
        SELECT @min_postid = postid
         FROM post
    What does this exactly do/return? Does this create a variable called min_postid with the value being the table identity postid? Would that be the record with the highest value of postid? Sorry for my ignorance, I just haven't seen this syntax used before...i'm not quite sure what its doing.

    Thanks,
    Mike

  8. #8
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    As it says in the FAQ warning:
    You should check that it works as expected in your environment since it relies on the fact a variable will be set using the last row that is returned from a result set. This is not published behaviour and is not guaranteed by Sybase.
    .

    Basically if your table consists of the IDs:
    1
    2
    3
    4
    5
    6

    when that SQL is done with the value of 6 will be stuck in the variable.
    Thanks,

    Matt

  9. #9
    Join Date
    Apr 2008
    Posts
    20
    Hi,
    The stored procedure solution for pagination is not generic. We have to write individual stored procedures for each table.

    Is there any other solution for this

Posting Permissions

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