mORMot Open Source

BostjanZ

Member

Registered: 2016-11-05

Posts: 7

JsonGet and ID field problem on MariaDB(Firedac)

Hello,

I'm trying to build a concept in which I could switch between SQLite and MySQL storage just with parameter and for standard fields everything works ok, but I have field Data which contains JSON object with some properties, which I would like to read separatly by internal parameters.

Model is defined like this:

TSQLTestTable = class(TSQLRecord)
    private
      FName:RawUTF8;
      FData:variant;
    published
      property Name:RawUTF8 read FName write FName;
      property Data:variant read FData write FData;
  end;

One record for test contains this content in Data field:

{"Param":{"Serial":"123abc"}}

I list records with:

var Table:TSQLTable;
     TmpFields:RawUTF8;
begin
  TmpFields:='ID,JsonGet(Data,'+QuotedStr('Param.Serial')+') as '+QuotedStr('Param.Serial');
  Table:=Database.MultiFieldValues(TSQLTestTable,TmpFields,'');
end;

When I use this code on SQLite database it works ok, but when I run the same code connected to MariaDB I get error:

exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) [SELECT ID, JsonGet(Data,'Param.Serial') as 'Param.Serial' FROM TestTable] using 3.33.0 - no such column: ID, extended_errcode=1

Problem is only with ID field. If I replace 'ID' with 'Name' which is also in model everything works ok.

I need ID field in result for record references, but I don't know if this is some kind of bug or limitation ?

Thank you for any info.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,256

Website

Re: JsonGet and ID field problem on MariaDB(Firedac)

Use RowID with external tables.

BostjanZ

Member

Registered: 2016-11-05

Posts: 7

Re: JsonGet and ID field problem on MariaDB(Firedac)

Thank you, with RowID it works as expected on both databases.

I should know its not a bug but only lack of my knowledge .