mORMot Open Source

Beertjie

Member

From: South Africa

Registered: 2018-09-09

Posts: 14

TSQLRequest.Execute ignoring Expand when result set is empty

I want my REST API to consistently use the "expanded" JSON format in its responses, so I've got NoAJAXJSON set to False on my TSQLRestServerDB. However, in the TSQLRequest.Execute function in SynSQLite3.pas, there is the following code which explicitly defies the value of the Expand argument if the result set is empty:

if (result=0) and W.Expand then begin
  // we want the field names at least, even with no data: we allow RowCount=0
  W.Expand := false; //  {"FieldCount":2,"Values":["col1","col2"]}
  W.CancelAll;
  for i := 0 to FieldCount-1 do
    W.ColNames[i] := sqlite3.column_name(Request,i);
  W.AddColumns;
end;

This requires the consumer of my API to have special handling for empty result sets. It would be cleaner to respond with a standard empty JSON array.

Judging from the comments, this behaviour is clearly intentional; therefore, I am hesitant to submit a pull request to change it. Of course, I could modify this code locally and run the risk of having it overwritten on future updates, but I would prefer to avoid that.

Any recommendations?

igors233

Member

Registered: 2012-09-10

Posts: 234

Re: TSQLRequest.Execute ignoring Expand when result set is empty

I used a custom loader function that accepts both expanded and non expanded format:

var
  json: RawByteString;
  Data: TDocVariantData;
begin
  json := StringFromFile(FileName);
  if not IsNotAjaxJSON(PUtf8Char(json)) then // If customers data is in normal, expanded format use regular InitJSON, otherwise use custom loader
    Data.InitJSON(json, JSON_OPTIONS[True])
  else
    LoadNonExpandedJSON(json, JSON_OPTIONS_FAST, Data);
end;

procedure LoadNonExpandedJSON(const AJSON: RawUTF8; AOptions: TDocVariantOptions; out ADoc: TDocVariantData);
var
  Temp: TSQLTableJSON;
  v: Variant;
begin
  TAutoFree.One(Temp, TSQLTableJSON.Create('', AJSON));
  Temp.ToDocVariant(v, False);
  ADoc.InitCopy(v, AOptions);
end;

Last edited by igors233 (2019-06-13 12:30:01)

Beertjie

Member

From: South Africa

Registered: 2018-09-09

Posts: 14

Re: TSQLRequest.Execute ignoring Expand when result set is empty

Thanks, igors233. That will work for a Delphi client.

I'd prefer to have my API respond in a friendlier way. In a public API, I do not have control over my consumers, and I'd rather not have to ask them to implement a workaround.

Does anyone know of a way to deal with this on the server? If the Execute method were virtual, perhaps I could override it...

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: TSQLRequest.Execute ignoring Expand when result set is empty

I think for an empty result + expanded it should in fact return an empty array. This is how most public api works, in addition to saving network resources.

If you need  full control of the input / output data format, I believe it is best to use methods-based services.

but it will give more work, or not

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: TSQLRequest.Execute ignoring Expand when result set is empty

This is a feature, so that the client knows the expected field names, even if there is no data.
For instance, when you run SynDBExplorer, you will have a grid with the field names, and no row.
It is also expected by other parts of the framework.

Note that the best practice is to not publish REST ORM in production, but encapsulate the ORM call in a service.
Then it is very easy to change the result to an empty array, if the default layout doesn't fit your needs.

Beertjie

Member

From: South Africa

Registered: 2018-09-09

Posts: 14

Re: TSQLRequest.Execute ignoring Expand when result set is empty

Note that the best practice is to not publish REST ORM in production, but encapsulate the ORM call in a service.

I didn't know that. What is the reason it's not recommended practice? I assume it's not just about the layout.