mORMot Open Source

talexone

Member

Registered: 2013-08-21

Posts: 23

Column names in response of restful query changes with startIndex

Hi,

Can't figure out why the response from RESTful server has different column names (rowid instead of ID and camel cased) when define startIndex other than 0:

Request/Response with startIndex=0:

http://localhost:8080/root/User?select=*&startIndex=0&results=1 

[{"ID":1,"USERNAME":"user1","HASHEDPASSWORD":null,"ROLE":2,"LOGINDATE":null}]

Request/Response with startIndex=1:

http://localhost:8080/root/User?select=*&startIndex=1&results=1 

[{"rowid":2,"Username":"user2","HashedPassword":null,"Role":0,"LoginDate":null}]

Is it possible to get the same format for both requests?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Column names in response of restful query changes with startIndex

Which version of mORMOt do you use?
Which database do you use?
How is the table defined? It is wieird there is "user1" then "user2" as column name.

Please try to debug a little and see what is computed as sql within ExecuteOrmGet.
My guess is that it introduce an OFFSET clause, which is handled not by the direct SQL engine but with a mORMot virtual table.
This is a limitation, which can not be easily overpassed.

My advice would be to avoid such a request, and return several items at once, then make the number filtering on client side.

talexone

Member

Registered: 2013-08-21

Posts: 23

Re: Column names in response of restful query changes with startIndex

Which version of mORMOt do you use? : 1.8

Which database do you use? Firebird

How is the table defined?

 TSQLUser = class(TSQLRecord)
    private
      fUsername: RawUTF8;
      fHashedPassword: RawUTF8;
      fRole: TSQLUserRole;
      FLoginDate: TDateTime;
    published
      property Username: RawUTF8 index 30 read fUsername write fUsername stored AS_UNIQUE;
      property HashedPassword: RawUTF8 index 64 read fHashedPassword write fHashedPassword;
      property Role: TSQLUserRole read fRole write fRole;
      property LoginDate: TDateTime read FLoginDate write FLoginDate;
  end;

It is wieird there is "user1" then "user2" as column name: user1 and user2 are the values of Username column of the rows 1 and 2.

You were right I found the checking for OFFSET in AdaptSQLForEngineList:

 if Stmt.Offset<>0 then begin
      InternalLog('AdaptSQLForEngineList: unsupported OFFSET for [%]',
        [SQL],sllWarning);
      exit;

I use it for pagination (but for example I put only one record by request), the data returned on the first page always works, and then on the second page nothing, but response body seems to keep good values in it.
As my client is a JavaScript and it's case sensitive, the response body of the second page was not accepted by client.
Is it possible to keep column naming structure between direct engine and virtual table?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Column names in response of restful query changes with startIndex

If you set Server.StaticVirtualTableDirect := true then the virtual table will be used for every request so you will get consistent results.

You may loose a little about performance, but not much I guess in respect to Firebird response time: more time will be spent waiting for Firebird than within the SQlite3 virtual engine.
Only check the SQL requests generated to Firebird in the logs, to ensure not too much data is returned.

Perhaps using a dedicated method-based or interface-based service could help in your specific case: you would have direct ability to change the SQL and what and where it is actually executed.
Using the REST / ORM layer on production is not a good idea anyway.

talexone

Member

Registered: 2013-08-21

Posts: 23

Re: Column names in response of restful query changes with startIndex

Thank you for your help @ab, StaticVirtualTableDirect did the trick. I'll check if there is any performance issue with bigger table.
REST layer is very handy for basic CRUD operations with entities, and I use interface-based services for more complex requests.