mORMot Open Source

theodor_gl

Member

Registered: 2014-09-01

Posts: 3

Paging with External Database

I try to change sample 28 to a SQL Server database and I encounter some errors when I use paging.

My datamodel is

  TMFFisa = class(TSQLRecord)
  private
    fDenumire: RawUTF8;
  published
    property Denumire: RawUTF8 index 50 read fDenumire write fDenumire;
  end;



using the URIPagingParameters

  aRestServer := TSQLRestServerDB.Create(aModel,':memory:',false); // authentication=false

  aRestServer.URIPagingParameters.StartIndex := 'PAGE=';
  aRestServer.URIPagingParameters.Results := 'LIMIT=';
  aRestServer.URIPagingParameters.SendTotalRowsCountFmt := ',"total":%';
  aRestServer.URIPagingParameters.Sort := 'SIDX=';
  aRestServer.URIPagingParameters.Dir := 'SORD=';

The result for a call
http://localhost:8888/root/MFFisa/?Select=*&page=3&limit=30&SIDX=Denumire&SORD=desc

is ok, but in log I see the query: SELECT Count(*) FROM MFFisa ORDER BY Denumire DESC 

For
http://localhost:8888/root/MFFisa/?Select=*&page=3&limit=30&SIDX=Denumire&SORD=asc

I get an error Column \"dbo.MFFisa.Denumire\" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Is it ok to have ORDER BY in the select count(*) query ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,244

Website

Re: Paging with External Database

Indeed, it sounds not right.

I've ensured that the "SELECT count()" statement generated for TSQLRestServer.URIPagingParameters.SendTotalRowsCountFmt property won't contain any ORDER BY clause.
See http://synopse.info/fossil/info/9e7b448397