Re: MDB MSSQL and LIMIT

Hello, On 07/20/2004 05:20 PM, Markus Wolff wrote:

This uses the PEAR::Pager package and performs a LIMIT (from, count) on the query so as to get only the data that should be displayed on every page. This works very well under Oracle 8 and 9, but we have troubble with MSSQL 8 : The number of data displayed on every page increases on each next page. If we set a perPage value of 5, the first page will display item 0 to 5, the second page will display item 0-10, the third page item 0-15. So it seems the 'from' of the LIMIT instruction is not used. This is strange as we did not change anything on that class, that uses MDB and the behaviour is very good under Oracle. Is there comething weird with MDB, or with the MSSQL driver??

It's neither MDB nor the driver. It's MSSQL. It doesn't support LIMIT at all and its equivalent, "SELECT TOP", does not provide the possibility to define an offset value. It is in fact possible to emulate LIMIT functionality with MSSQL, but this would mean either using stored procedures or using a complicated WHERE statement that has to be changed for every query depending on how the tables are organized. Both solutions are clearly beyond the scope what a generic package can offer.

That is not accurate. The original Metabase MSSQL driver used server side cursors to fetch only the range of rows that you ask, not more, not less. However, that part of the Metabase driver was not ported to MDB which just uses SELECT TOP which is a feature that is not supported by old MS SQL servers, unlike server side cursors. -- Regards, Manuel Lemos PHP Classes - Free ready to use OOP components written in PHP http://www.phpclasses.org/ PHP Reviews - Reviews of PHP books and other products http://www.phpclasses.org/reviews/ Metastorage - Data object relational mapping layer generator http://www.meta-language.net/metastorage.html