Description: ------------ I have a block of SQL code that I am using to emulate MySQLs limit feature in MSSQL by using a cursor. The example below will retrieve rows 10 to 30. now quite exactly how easy it would be to integrate this logic into the mssql driver (and sybase driver as well, probably others). but it works rather quickly and better than my previous hack at doing sublimit queries. SELECT TOP 5 Name, uname FROM users WHERE uname NOT IN (SELECT TOP 10 uname FROM users ORDER BY uname ASC) ORDER BY uname ASC This would retrieve records 11 to 15, but requires knowing what the primary key(s) of the table are to work.. Reproduce code: --------------- $db =& DB::connect('mssql://user:poass@server/database"); $sql = <<< EOSQL DECLARE Search CURSOR LOCAL SCROLL READ_ONLY FOR SELECT * FROM MyTable DECLARE @limit INT, @offset INT SET @limit = 20 SET @offset = 10+1 OPEN Search FETCH ABSOLUTE @offset FROM Search WHILE @@FETCH_STATUS =0 AND @limit > 1 BEGIN FETCH NEXT FROM Search SET @limit = @limit - 1 END CLOSE Search DEALLOCATE Search EOSQL; $res =& $db->query($sql); $row =& $res->fetchRow(); do { echo $row[0]."\n"; $row =& $res->fetchRow(); if (is_null($row)) { if ($res->nextResult()) { $row =& $res->fetchRow(); } } } while ($row);

DB is in maintaince mode, any changes should go into MDB2.

Since there is no way for us to know the primary key of a table, we cannot use this.

This does not require knowledge of the Primary key at all. It goes soly based on the record index. so it works EXACLTY like LIMIT 10,30 in MySQL.

Ah then I misunderstood your comment. So in your example you are just wrapping the following query into the cursor with a limit of 20 and an offset of 10 (effectively getting rows 11 to 30): SELECT * FROM MyTable Is that correct?

Could you cook up a patch for MDB2 (and/or DB)?

Exactly it would just return rows 11 through 30. The only *oddity* and thing that would have to be worked around is the fact that the cursor would return back 20 individual result sets of 1 record. AND when pulling back with an associative array an additional field of 'rowstat' with a value of 1 is returned. (no clue how to tell mssql to not send it, or if it's a php-mssql extension issue).

I have dealt with additional columns due to limit emulation in the oracle driver. adding additional result sets might be a bit painful though. there is however already a method in the drivber to move to the next result set that we could use. however are you sure that the overhead of this is worth it? i guess it is with large offsets. so maybe we can make it an option from what offset on this alternative algorithm should be used?

I'm using this heavily in a webbased ticketing system and havn't noticed any "performance" problems or overhead. It seems to be less overhead as the server is doing limit emulation instead of having php pull down a few hundred records only to do something with the last 20. This cursor block would have the server do that and only return what I really need. Though due to the way this works with MSSQL (I haven't found a way to get MSSQL to return one result set) it does require the special PHP code block to pull results back, which I have cooerced into a single while () expression. while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC || ($res->nextResult() && $row =& $res->fetchRow(DB_FETCHMODE_ASSOC ))) { This is still PEAR::DB and not PEAR::MDB2, but should be the same except the constant. I have not yet converted my code to MDB2 (which I like the transaction support MUCH better in MDB2)

I am using a similar method. Instead of using setLimit() on the query before it's executed, I'm re-writing the query as follows and passing it off to MDB2's appropriate function. For UPDATE/DELETE queries: $sql = preg_replace('/^UPDATE /i',"UPDATE TOP $count}",$sql); $sql = preg_replace('/^DELETE /i',"DELETE TOP $count}",$sql); Which is then passed off to exec($sql). For SELECT queries the statement is being re-written as follows: SELECT * FROM ( SELECT TOP $rows * FROM ( SELECT TOP $last $sql_after_select ORDER BY $order_clause ) AS foo ORDER BY $order_clause_rev ) AS bar ORDER BY $order_clause $rows = The count normally passed to setLimit(). $last = $rows plus the offset normally passed to setLimit(). $sql_after_select = preg_replace('/^SELECT /i','',$sql); $order_clause = A valid ORDER BY clause. Here when one isn't defined I order by the first column I see in the table DESC (ie. ORDER BY id DESC). $order_clause_rev = A reversed ORDER BY clause. For the example above I would use ORDER BY id ASC. This works great and consumes less time and server resources than any other method I've tried. A good discussion of this can be found here: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/