mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty

TOrmInvcanceled=class(Torm)
    private
      Ftestid: PtrInt;
      Fxml: rawutf8;
    published
      property testID: PtrInt read Ftestid write Ftestid;
      property xml:rawutf8 read Fxml write Fxml;
  end;

procedure errororm;
var lis:ilist<TOrmInvcanceled>; model:TOrmModel;
begin
model:=tormmodel.Create([TOrmInvcanceled]);
Model.VirtualTableRegister(TOrmInvcanceled,TOrmVirtualTableBinary);
srvmem:=TRestServerDB.Create(model);
srvmem.Model.Owner:=srvmem;
srvmem.CreateMissingTables;
showmessage(TOrmInvcanceled.OrmProps.SqlTableRetrieveAllFields);
srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'*');
end;

The above causes the following error:
Project Project3.exe raised exception class ESqlite3Exception with message 'Error SQLITE_ERROR (1) [SELECT ID,testID,xml FROM Invcanceled WHERE testID=?] using 3.37.0 - no such column: ID, extended_errcode=1'.

if the following is used, no error is returned:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'',[],'*');

Using the following, no error is returned also:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'Row'+TOrmInvcanceled.OrmProps.SqlTableRetrieveAllFields);

Last edited by dcoun (2021-12-18 12:15:12)

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty

Am I missing something from the documentation in the above?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty

It is weird, because it was a problem existing in mORMot 1 already.

Should be fixed with https://github.com/synopse/mORMot2/commit/9cdef61a

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty

Thank you Arnaud,
There is something more. The following does not work

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'ID=?',[1],'*');

nor the following:

srvmem.orm.RetrieveIList(TOrmInvcanceled,lis,'testID=?',[1],'ID,xml');

Should I use RowID? 
Thank you in advance

Last edited by dcoun (2021-12-18 14:15:01)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: RetrieveIList: "no such column: ID" when FormatSqlWhere is not empty

Yes use rowid if you have virtual tables.