PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Jeff Greif" <jgreif at befree dot com>
Subject: Re: Moving to a specific row
Date: Aug 31 2000 4:32PM

For MS Sql Server (which supports the SQL construct TOP), you can use a
cursor and remember what the last row you've seen is, assuming that whatever
is ordering the rows is distinct in every row.  In the example stored
procedure below, the sort is by the varchar data CATEGORY, and there are two
cases -- the beginning of the result set, and arbitrary other locations
therein.  Dynamic SQL must be used (a string constructed and executed)
because this variety of SQL does not let you put a variable in the TOP
clause.  Our webservers call procedures like this using ct_sql.

CREATE PROCEDURE CATEGORY_BY_PAGE @pagesize INTEGER, @pagenum INTEGER
AS
BEGIN
    declare @siteid INTEGER, @offset INTEGER, @str NVARCHAR(500)
    SET @offset = @pagesize*(@pagenum - 1)
    IF (@offset = 0)
    BEGIN
      SET @str = N'SELECT top ' + CAST(@pagesize AS NVARCHAR(12)) +
      N' CATEGORY, X,Y,Z
      FROM TNPS..CATEGORY
      ORDER BY CATEGORY'
      EXEC sp_executesql @str
    END
    ELSE
    BEGIN
      DECLARE @cat VARCHAR(100)
      DECLARE findnth CURSOR
      LOCAL DYNAMIC
      FOR SELECT CATEGORY FROM TNPS..CATEGORY ORDER BY CATEGORY
      OPEN findnth
      FETCH RELATIVE @offset FROM findnth INTO @cat
      CLOSE findnth
      DEALLOCATE findnth
      SET @str = N'SELECT top ' + CAST(@pagesize AS NVARCHAR(12)) +
      N' CATEGORY, X,Y,Z
      FROM TNPS..CATEGORY
      WHERE CATEGORY > @pcat ORDER BY CATEGORY '
      EXEC sp_executesql @str, N'@pcat VARCHAR(100)', @cat
    END
END

I have more messy stored procedures which do this for varying sort columns,
etc.  Not sure how much of this is applicable to Sybase.

Jeff


----- Original Message -----
From: "Michael Peppler" 
To: "SybPerl Discussion List" 
Sent: Thursday, August 31, 2000 7:07 AM
Subject: Re: Moving to a specific row


> Andrew Armstrong writes:
>  > I routinely have to report large numbers of database table rows to a
web page
>  > and was having a problem limiting the number of rows I display using
the
>  > standard CTlib methods. I can use set rowcount xxxx but this only
limits the
>  > resultset to the first xxxx rows. I want to be able to move the
resultset to a
>  > row that I choose. I can do this in PHP and was wondering how the best
way to do
>  > this in Perl would be.
>  >
>  > The only solution that i have found is to call ct_fetch() xxxx times
and ignore
>  > the results or to use ct_sql to move the results into an array and
start at the
>  > xxxx element. Neither of these seem very good for system performace.
>  >
>
> I don't see how PHP could do this any better than perl. It is limited
> by what Sybase understands, and in this particular case it is limited
> by the way SQL works.
>
> As SQL is set-based you can't say "get me rows x through y". Instead
> you have to use an appropriately constructed where clause that will
> give you the next x rows.