[freetds] dbnextrow() vs dbskiprow() (skipping rows in FreeTDS)
dchang at fsautomation.com
Sun Jul 27 21:50:50 EDT 2008
There's no single statement syntax in MSSQL to support the LIMIT (with two
parameters) functionality. To support LIMIT (with one parameter), use: set
If you are open to a work around method for LIMIT (with two parameters),
this is what you can do...
select identity(int, 1, 1) as pg_id, * into #temp1 from mytable
set rowcount 1
select * from #temp1 where pg_id >= 900
When you're done scrolling through mytable, then do: drop table #temp1
The caveat is that you must pick a column name that doesn't conflict with
'mytable' columns (I used 'pg_id'). Also, this doesn't work very well
(performance wise) for large tables because it makes a copy of the records.
I would also add a 'set rowcount 10000' at the top and use a WHERE criteria
to limit the rows retrieved from mytable.
----- Original Message -----
From: "Navdeep Shergill" <jatshergill at gmail.com>
To: "FreeTDS Development Group" <freetds at lists.ibiblio.org>
Sent: Sunday, July 27, 2008 5:32 PM
Subject: Re: [freetds] dbnextrow() vs dbskiprow() (skipping rows in FreeTDS)
>I am trying to implement a mysql like paging; and this is one approach that
> I am looking at. Mysql makes it realy easy with the LIMIT keyword; but I
> not having such luck with MSSQL. I am currently looking at the ROW_NUMBER
> function ; but even that one requires me to do a order by on some column.
> The problem is that the user may be trying to run some very generic
> and I need to be able to provide paging.. so I was looking at some way to
> exec a query; and then go right to some arbitary row in the result set.
> so does that mean that there is no such thing as dbskiprow() in FreeTDS?
> On Sun, Jul 27, 2008 at 5:15 PM, Joel Fouse <joel at fouse.net> wrote:
>> On Sun, 2008-07-27 at 15:57 -0700, Navdeep Shergill wrote:
>> > Here is a sample scenerio. Lets say I have a table with 1000 rows and I
>> > issue this query
>> > select * from mytable;
>> > If I wanted to read the '900th' row; then I would have to call
>> > 900 times to get to that row. By calling dbnextrow; I have wasted all
>> > time loading data value for the previous 900 rows; even though I did
>> > not
>> > need them at all. I am looking for some way to advance the cursor to
>> > the
>> > 900th row. (Without doing another db query).
>> At first glance, this seems like the sort of thing better addressed by a
>> more targeted query, like:
>> select * from mytable where id = 900
>> ...or something similar. Generally, if you're looking for a specific
>> row or group of rows, you're better off narrowing that down in the query
>> in the first place using an appropriate WHERE clause rather than
>> grabbing the whole thing and looping through until you get the row you
>> Even if there's some kind of dependent logic, where the data you get in
>> the first row somehow determines what the next row you want is (which,
>> even then, would strongly suggest a database redesign), you would be
>> better off getting your determining info, closing that query, and
>> opening a new one asking for the specific row(s) you want (again, back
>> to the WHERE clause).
>> Does this help? Or is there something specific about your scenario that
>> requires this kind of "give me everything except I don't really want it"
>> - Joel
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
More information about the FreeTDS