mORMot Open Source

uian2000

Member

Registered: 2014-05-06

Posts: 69

Query by TSQLRecord returns nothing.

Hi, I have two TSQLRecord descendant classes, one refs another.

  // first class - to be referenced.
  TSQLRecordMaster=class(TSQLRecord)
  private
    fDummyName: RawUTF8;
  published
    property DummyName: RawUTF8 read fDummyName write fDummyName;
  end;

  // second class - references first one.
  TSQLRecordDetail=class(TSQLRecord)
  private
    fMaster: TSQLRecordMaster;
    fDummyName: RawUTF8;
  published
    property Master: TSQLRecordMaster read fMaster write fMaster;
    property DummyName: RawUTF8 read fDummyName write fDummyName;
  end;

both classes' insertion works fine, but the second class query always return nothing.

function TDummyService.DetailGet(aMaster: TMaster; out aDetails: TDetails):
    Integer;
var
  qMaster: TSQLRecordMaster;
  qDetail: TSQLRecordDetail;
begin
  Result := 0;
  with TSQLRecordMaster.AutoFree(qMaster,fRest,'DummyName=?',
    [aMaster.MasterName]) do
    if qMaster.FillOne then
      with TSQLRecordDetail.AutoFree(qDetail,fRest,'Master=?',
        [qMaster]) do
        while qDetail.FillOne do           // always jump to the end
        begin
          SetLength(aDetails, Result+1);
          aDetails[Result].Master := aMaster;
          aDetails[Result].DetailName := qDetail.DummyName;
          Inc(Result);
        end;
end;

I can find records of second class in the json file, so I'm sure that data exists.

[{"Master":[
{"RowID":1,"DummyName":"This is Master"}]
},{"Detail":[
{"RowID":1,"Master":35972812,"DummyName":"This is detail No.1"}]
}]

But why?
I've test this case On D7 & DXE, same result.

Here(pastbin) comes the full source, so everyone could reproduce this issue.

I'm sorry for my ignorance of forum rules.

Last edited by uian2000 (2020-02-02 11:56:28)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Query by TSQLRecord returns nothing.

Please follow the forum rules and don't post huge pieces of code in the message. Use gist or pastebin links.

uian2000

Member

Registered: 2014-05-06

Posts: 69

Re: Query by TSQLRecord returns nothing.

I insert detail record like this. Is this a wrong way using TSQLRecord as reference?

var
  qMaster: TSQLRecordMaster;
  qDetail: TSQLRecordDetail;
begin
  ...
  // create qMaster and qDetail
  qDetail.Master:=qMaster;
  ...
  fRest.Add(qDetail);
  ...
end;

And the query of TSQLRecordDetail looks like this. Is it right?

var
  qMaster: TSQLRecordMaster;
  qDetail: TSQLRecordDetail;
begin
  ...
  // check value of qMaster
  with TSQLRecordDetail.AutoFree(qDetail, fRest, 'Master=?', [qMaster]) do
  while qDetail.FillOne do
  begin
     // do something with qDetail
  end;
end;

uian2000

Member

Registered: 2014-05-06

Posts: 69

Re: Query by TSQLRecord returns nothing.

when I changes the property from TSQLRecord decedent class to TID, ALL TEST GOES FINE!
I think the reason is that TSQLRecord field stores the pointer of that instance.
It is mentioned here: TID Fields.

TSQLRecord published properties do match a class instance pointer, so are 32-bit (at least for Win32/Linux32 executables).

Question is: If these TSQLRecord fields just save dynamic values, then how could we use them?

This is the modified TestProject, all testcases passed.
And more, TSQLRestServerFullMemory server failed the last check, for it supports query with only one field, or it will return nothing.

function TSQLRestStorageInMemory.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
// - GetJSONValues/FindWhereEqual will handle basic REST commands (not all SQL)
// only valid SQL command is "SELECT Field1,Field2 FROM Table WHERE ID=120;",
// i.e one Table SELECT with one optional "WHERE fieldname = value" statement
// - handle also basic "SELECT Count(*) FROM TableName;" SQL statement
// Note: this is sufficient for OneFieldValue() and MultiFieldValue() to work
var MS: TRawByteStringStream;
  ...
begin
  ...
      Stmt := TSynTableStatement.Create(SQL,
        fStoredClassRecordProps.Fields.IndexByName,
        fStoredClassRecordProps.SimpleFieldsBits[soSelect]);
      try
        if (Stmt.SQLStatement='') or  // parsing failed
           (length(Stmt.Where)>1) or // only a SINGLE expression is allowed yet  <--here
           not IdemPropNameU(Stmt.TableName,fStoredClassRecordProps.SQLTableName) then
          // invalid request -> return ''
          exit;
        if Stmt.SelectFunctionCount=0 then begin

So I replace TSQLRestServerFullMemory with TSQLRestServerDB. It dose make sense.