FreeTDS Development Group

["David Chang" <dchang AT fsautomation.com>]

Navdeep,

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.

DC

----- 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