mORMot Open Source

ulrichd

Member

Registered: 2018-07-30

Posts: 16

RetrieveDocVariantArray returning inconsistent results

I just finished converting a project from mORMot 1 to mORMot 2.  Everything is working great so far, except the result of a RetrieveDocVariantArray call, which is returning null for Data for one row of the table.  It should be returning a string value (this particular entry is holding an email address).

The relevant code:

var
  cfg: Variant;
begin
    ...
      cfg := RetrieveDocVariantArray(TConfigHistory, '', 'JOIN Config AS c ON (MasterId=c.ID) WHERE EndTime=0', [], 'Key,Data,StartTime,CreatedBy');
    ...
end;

TConfig and TConfigHistory are defined as follows:

  TConfig = class(TSQLRecord)
  private
    fKey: RawUTF8; // alphanumeric string acting as a unique key
  published
    property Key: RawUTF8 index 32 read fKey write fKey stored AS_UNIQUE;
  end;

  TConfigHistory = class(TSQLRecord)
  private
    fMasterId: TConfigToBeDeletedID; // reference to the master TConfig record (w/ ON DELETE CASCADE)
    fStartTime: TUnixMSTime; // time stamp when record was first active
    fEndTime: TUnixMSTime; // time stamp when record was last active; will be 0 for currently active records
    fCreatedBy: TUserToBeDeletedID; // table ID of user who created the record (w/ ON DELETE CASCADE)
    fData: Variant; // data
  published
    property MasterId: TConfigToBeDeletedID read fMasterId write fMasterId;
    property StartTime: TUnixMSTime read fStartTime write fStartTime;
    property EndTime: TUnixMSTime read fEndTime write fEndTime;
    property CreatedBy: TUserToBeDeletedID read fCreatedBy write fCreatedBy;
    property Data: Variant read fData write fData;
  end;

The strange thing is, if I overwrite the value with an integer (5 for example), it will return 5 as the field value.  Any string results in null.

If I use this function to pull that particular entry, the correct value is returned from the table:

function GetConfigVar(const Key: RawUTF8; var Value: Variant): Boolean;
var
  aConfig: TConfig;
  aConfigHistory: TConfigHistory;
begin
  aConfig := TConfig.CreateAndFillPrepare(aRestServer.Orm, 'Key=?', [Key]);
  try
    Result := aConfig.FillOne;
    if Result then begin
      aConfigHistory := TConfigHistory.CreateAndFillPrepare(aRestServer.Orm, 'MasterId=? AND EndTime=0',
       [aConfig.ID]); // get the currently active history record
      try
        Result := aConfigHistory.FillOne;
        if Result then
          Value := aConfigHistory.Data;
      finally
        aConfigHistory.Free;
      end;
    end;
  finally
    aConfig.Free;
  end;
end;

Anyone have any suggestions as to what the issue might be?

Last edited by ulrichd (2024-10-25 20:32:01)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,656

Website

Re: RetrieveDocVariantArray returning inconsistent results

My guess is that the problem is at database client layer, not in the ORM.
Which DB are you using? Via which mormot.db.* unit do you connect?

1) Enable the logs and look at the executed SQL and returned JSON
2) OR debug and see which JSON is returned by the database client layer
3) Don't put code in the forum thread, but use a git.

ulrichd

Member

Registered: 2018-07-30

Posts: 16

Re: RetrieveDocVariantArray returning inconsistent results

Right: totally forgot about mentioning that.  I'm using SQLite3 via mormot.db.raw.sqlite3

1) This is the debug output I'm getting for this call:

20241028 13402909  0 SQL   	ols_rest.TOlsRestServer(02b5f690) 246us returned 8 rows as 598 B SELECT Key,Data,StartTime,CreatedBy FROM ConfigHistory JOIN Config AS c ON (MasterId=c.ID) WHERE EndTime=0
20241028 13402909  0 res   	mormot.db.raw.sqlite3.TSqlDatabase(02c3aa28) [{"Key":"NotDnsSrv","Data":"1.1.1.1","StartTime":1697574402827,"CreatedBy":0},{"Key":"NotExpWrn","Data":"7","StartTime":1697574402842,"CreatedBy":0},{"Key":"MaintTime","Data":"256","StartTime":1697574402842,"CreatedBy":0},{"Key":"MaintLogD","Data":"365","StartTime":1697574402858,"CreatedBy":0},{"Key":"MaintBakF","Data":"14","StartTime":1697574402858,"CreatedBy":0},{"Key":"AccSesLen","Data":"1","StartTime":1729798793575,"CreatedBy":1},{"Key":"NotViaEml","Data":"1","StartTime":1729798819683,"CreatedBy":1},{"K... (truncated) length=598

Is there some way to configure the logging so it does not truncate the output?  The relevant value is just beyond the portion that is cut off...

2) That's next, but I will have to move my code to a console app first as it is currently running as a Windows service app.

3) Sorry about that, I will keep that in mind.

ulrichd

Member

Registered: 2018-07-30

Posts: 16

Re: RetrieveDocVariantArray returning inconsistent results

@ab, I stepped through the code with the debugger, and it looks like the issue is with converting the JSON string to a variant.

The

T := MultiFieldValues(Table, FieldsCsv, FormatSqlWhere, BoundsSqlWhere);

call in TRestOrm.RetrieveDocVariantArray correctly pulls the data from the table into a JSON string:

'[{"Key":"NotDnsSrv","Data":"xyz","StartTime":1730216625567,"CreatedBy":0},{"Key":"NotExpWrn", ... 

note how the Data property is identified as a string here.

The subsequent conversion to variant however gets the value of this property without quotes, so does not recognize it as a string and hence flags it as unknown (i.e. returns null).  This happens in the JsonToAnyVariant call in mormot.core.variants: it gets all the way to the end to

   begin
      Info.Json := J;
      goto parse;
    end;

but then the jump to parse calls Info.GetJsonField which cannot identify it as a string (lacks the quotes) and returns nil instead.

Let me know if this helps in identifying the issue, or if I need to put together some demo code.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,656

Website

Re: RetrieveDocVariantArray returning inconsistent results

I don't understand: what "The subsequent conversion to variant however gets the value of this property without quotes" means.
You may be confused that the string is parsed in-place, and un-quoted in place, during the parsing.

My guess is that there is something wrong elsewhere.

ulrichd

Member

Registered: 2018-07-30

Posts: 16

Re: RetrieveDocVariantArray returning inconsistent results

Sorry, that was just a wild guess.

Here's a gist to demonstrate the issue: https://gist.github.com/oti-ud/61151cff … a1c5b5edf2

If you look at the log file generated, the dump of the cfg variable shows the second row Data property to be null; it should be a string instead (i.e. the sample email address as populated in the db).

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,656

Website

Re: RetrieveDocVariantArray returning inconsistent results

I can't reproduce it.

In the ValueVarToVariant() for this field:
- what is the Value and ValueLen content?
- what is the fieldType value?

ulrichd

Member

Registered: 2018-07-30

Posts: 16

Re: RetrieveDocVariantArray returning inconsistent results

Value is 'test@test.com'
ValueLen is 13
fieldType oftVariant

here's the call stack up until and into JsonToAnyVariant:

mormot.core.variants.JsonToAnyVariant(Unassigned,('test@test.com', 'test@test.com', 7, True, ' '),$119F930,False)
mormot.orm.base.Complex
mormot.orm.base.ValueVarToVariant('test@test.com',13,oftVariant,(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, nil {#0}, nil, 0, False, nil, 0, 0, 0, 0, 0, 0, 0, nil, nil, nil, nil, nil, (nil, nil), (0, 0, 0), (0, 0, 0, 0, 0, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), (0, 0, 0, 0)),True,nil,[dvoReturnNullForUnknownProperty,dvoValueCopiedByReference])
mormot.orm.base.TOrmTableAbstract.GetAsVariant(9,1,Unassigned,False,False,False,[dvoReturnNullForUnknownProperty,dvoValueCopiedByReference])
mormot.orm.base.TOrmTableAbstract.ToDocVariant(2,Unassigned,[dvoReturnNullForUnknownProperty,dvoValueCopiedByReference],False,False,False)
mormot.orm.base.TOrmTableAbstract.ToDocVariant((???, Unassigned),False)
mormot.orm.base.TOrmTableAbstract.ToDocVariant(Unassigned,False)
mormot.orm.rest.TRestOrm.RetrieveDocVariantArray(TConfigHistory,'','JOIN Config AS c ON (MasterId=c.ID) WHERE EndTime=0',(...),'Key,Data,StartTime,CreatedBy',nil,nil)
mormot.rest.core.TRest.RetrieveDocVariantArray(TConfigHistory,'','JOIN Config AS c ON (MasterId=c.ID) WHERE EndTime=0',(...),'Key,Data,StartTime,CreatedBy',nil,nil)
mormot2_rvda_issue.mormot2_rvda_issue

it then goes through GetJsonField which sets Value to nil.

I assume GetJsonField would only return it as string if the input value was wrapped in quotes?

Please see rev3 of the gist for an even more simplified version of the code.

Last edited by ulrichd (2024-11-01 19:29:50)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,656

Website

Re: RetrieveDocVariantArray returning inconsistent results

I think now I understand the problem.

Please try with
https://github.com/synopse/mORMot2/commit/4860f1d6

ulrichd

Member

Registered: 2018-07-30

Posts: 16

Re: RetrieveDocVariantArray returning inconsistent results

Hi ab,

confirmed working!  That fixes the issue, thanks!