mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 392

Differences in quering database from mormot2

I tried to request the same with different approaches from two different TRestServerDB, one sqlite memory tables (Srv1) and one that maps to a Mariadb database(Srv2).
I noticed the following differences:
a. Using RetrieveListJson and MultiFieldValues, the Srv1 does not recognize the field name 'ID' (raises exception) and only works with field name 'RowID'. In contrast, Srv2 accepts without exceptions both field names 'ID' and 'RowID' but it always returns the field name 'ID' in json.
b. Running a query with ExecuteJson([Tormtable],'select * from table where field=1'), the Srv1 does NOT return the RowID field in json, in contrast the Srv2 returns it as 'ID'
c. Using again ExecuteJson the query 'select RowID from table' works for both Srv1 and Srv2, but Srv1 returns RowID and Srv2 returns ID as row id field name.
d. Using again ExecuteJson the query 'select ID from table' works only for Srv2, and Srv1 raises exception.

The above are helpful with what we expect to be returned.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 56

Re: Differences in quering database from mormot2

This is really very confusing!!!! RowID e ID

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Differences in quering database from mormot2

This is indeed confusing, but because you are making wrong assumptions.
This behavior is documented since the beginning of mORMot, and written explicitly in the IRestOrm.ExecuteJson method:
- you should not have to use this method, but the ORM versions instead
ExecuteJson() is to be used with a pure SQLite3 backend or our in-memory engine.

In short:
1) if you want to run SQL statements, just use the mormot.db direct statements on the database.
2) if you want to use the ORM, don't use SQL statements, unless you are sure that you don't have an external database, or you understand what occurs behind the scene.
3) if you really want to write SQL statements, explicitly add the "AS fieldname" syntax if you want a consistent response about the field names in the JSON response.

The mORMot 1 documentation about external DB is applying to mORMot 2, and a good reference.
The main problem is that you make plain SQL statements, like "select" without understanding what is behind the scene.
If you use an external table, you should use 'RowID' name when the SQL statement is run by SQLite3 virtual tables. Because it is what is required by those SQLilte3 virtual tables mechanism (this is not a mORMot limitation, but a SQLite3 limitation).
For the most simple requests, the SQlite3 virtual engine is by-passed, and may accept or return plain ID or RowID field names.
Then, depending on where the request is executed (in SQLite3 virtual engine, or from direct requests), you may receive unexpected field names, depending on the DB backend.
All this is even complicated by the fact that sometimes the JSON result is rewritten on the fly. This is why the ORM is always using a TOrmTableJson for parsing the result into proper TOrm values.

So just don't go that direction, this is just plain wrong.
I have updated the documentation to emphasize this behavior:
https://github.com/synopse/mORMot2/commit/4f43b296

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 392

Re: Differences in quering database from mormot2

I did not write the above this to state the "confusion". I wrote it to help.
All the time, I am requesting 'RowID" because that works everywhere. All methods like MultiFieldValues, onefieldvalues, etc follow that.
I am using ExecuteJSON only to deal with json fields using json_each and conditions in where and believe me I love it, it is very powerful.
Probably the main reason I am using Mormot is the possibility to use everything you need. I have already used XData & DMVC in the past.
I did not write anything to blame your work

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 56

Re: Differences in quering database from mormot2

Sry guys, my fault!