[freetds] dbnextrow() vs dbskiprow() (skipping rows in FreeTDS)

David Chang 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 
rowcount {n}

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 
> am
> 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 
> queries;
> 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
>> dbnextrow()
>> > 900 times to get to that row. By calling dbnextrow; I have wasted all
>> this
>> > 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
>> want.
>> 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"
>> approach?
>> - Joel
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

More information about the FreeTDS mailing list