#1 2021-12-16 20:52:16

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 184

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)

Offline

#2 2021-12-18 13:39:30

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 184

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

Am I missing something from the documentation in the above?

Offline

#3 2021-12-18 14:02:46

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,187
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

Offline

#4 2021-12-18 14:14:42

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 184

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)

Offline

#5 2021-12-18 18:46:10

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,187
Website

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

Yes use rowid if you have virtual tables.

Offline

Board footer

Powered by FluxBB