MSSQLServer and RQL with Offset/Fetch throws exceptions

[Voscha]

The following RQL from the sample "activerecord_showcases" throws an exception with MS SQLServer:

cRQL2 = 'and(eq(City,"Rome"),or(contains(CompanyName,"GAS"),contains(CompanyName,"Motors")))';
...
lCustList := TMVCActiveRecord.SelectRQL<TCustomer>(cRQL2, 20);

because the resulting SQL has no ORDER BY clause:

'SELECT id, code,description,city,rating,note FROM customers WHERE ((city = ''Rome'') and ((LOWER(description) LIKE ''%gas%'') or (LOWER(description) LIKE ''%motors%''))) /*limit*/ OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY'

Offset/Fetch have to be used with the order by clause, I think this must be done in the SQL generator of MS SQLServer.

[danieleteti]

This is the approach with the less level of "surprise". The only thing we could do is to raise a specific exception before returning the (wrong) statement. What's you POV about the "solution" for this issue? In other words, what should happend using an RQL exception like gt("id", 1);limit(10)?

[Voscha]

Perhaps add a default sort on the primary key in the sql generator if there is no specific sort in the RQL? so we could avoid to raise an exception.

[danieleteti]

I decided to switch this issue in the 3.2.2-nitrogen. Please, check if it works on your side.