Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "David Chang" <dchang AT fsautomation.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] dbnextrow() vs dbskiprow() (skipping rows in FreeTDS)
  • Date: Sun, 27 Jul 2008 18:50:50 -0700

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







Archive powered by MHonArc 2.6.24.

Top of Page