mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 56

Can u see if there some bug mine or of mormot here?

I have this instruction:

var
  vAmostras: Variant;
begin
  vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
    'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)',
    [],  <MYFIELDS>);
...
end;

I make many tests replacing <MYFIELDS>

<MYFIELDS> = 'RowId'
Log: EXC       ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowId FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowId",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c

<MYFIELDS> = 'Id'
Log: EXC            ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT Id FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: Id",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c

<MYFIELDS> = 'amostra.RowId, s.numero, amostra.numero'
Log: works and bring Amostra ID rightly

<MYFIELDS> = 'amostra.Rowid, amostra.*'
Log: works and return amostra.Id, others fields...

<MYFIELDS> = 'amostra.*, s.numero, amostra.numero'
Log: works but dont return amostra.Id neither amostra.RowId

<MYFIELDS> = 'amostra.RowId'
Log: don't execute sql nothing at logger

<MYFIELDS> = 'amostra.Id'   
Log: don't execute sql nothing at logger

<MYFIELDS> = 'amostra.*'
Log: don't execute sql nothing at logger

<MYFIELDS> = 'amostra.Id, s.numero, amostra.numero'
Log: EXC       ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT amostra.Id, s.numero, amostra.numero FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: amostra.Id",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c

<MYFIELDS> = '*'
Log:  EXC       ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowID,DataCriacao,UltimaAlteracao,Numero,Solicitacao,Exames,DataEtiqueta,Rack,Linha,Coluna FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowID",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c

<MYFIELDS> = ''
Log: EXC       ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [SELECT RowID,DataCriacao,UltimaAlteracao,Numero,Solicitacao,Exames,DataEtiqueta,Rack,Linha,Coluna FROM Amostra left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)] using 3.44.2 - no such column: RowID",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 8f207c

I forgot my configuration:

  FConfigDB := TSqlDBPostgresConnectionProperties.Create(
    '191.101.78.31:5001',
    'DB_SOROTRACK',
    'microprocess',
    'mpPost19dokcer!');
  FOrmModel := CreateModel;
  VirtualTableExternalRegisterAll(FOrmModel, FConfigDB);
  FOrmModel.Props[TAmostra].ExternalDB.MapField('Solicitacao', 'IdSolicitacao');
  FRestOrm := TRestClientDB.Create(FOrmModel, nil, SQLITE_MEMORY_DATABASE_NAME, TRestServerDB, false, '');
  FRestOrm.Server.Server.CreateMissingTables;

I want use ORM but can put my owner sqls too.
I read and reread documentation and dont understand when use RowID or ID (my field) too.

Last edited by mrbar2000 (2024-03-06 21:03:22)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Can u see if there some bug mine or of mormot here?

Such complex SQL are not supported by the IRestOrm interface.

Directly access the FConfigDB external database.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 56

Re: Can u see if there some bug mine or of mormot here?

Arnold

First of all I want to congratulate you on the mormot. this is a great framework. Despite the vast documentation, many things are evident in it for those who are starting to use the framework.

I would like to understand the reason for this limitation, I am not talking about complex queries here, just talking about the fields to be returned by the ORM query.

This is not a complex query, I simply do the join to filter the records in the where clause.

From what I understand when using VirtualTableExternalRegisterAll with SQLITE_MEMORY_DATABASE_NAME my ID fields have to be treated as RowId when mentioning them in my source code. mormot will automatically convert RowID to ID to run against the database. Am I right?

What I don't understand is why something as basic as returning fields cannot be done in a simple way and consistent with what programmers are used to doing. Again this is not a criticism but an attempt to improve mormot so that we can make mormot closer to what programmers are used to.

So here samples that shouls be works IMHO:

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [],  'RowID');
Should be bring all Amostra records, just field ID, but mormot raise error "no such column: RowId" and not is because join

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [],  '');
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [],  '*');
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [],  'Amostra.*');
All this should be bring the samething, all  amostra records.
by your documentation, the last parameter as '' should be bring all fields except RowId/Id and blob fields

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [],  'Amostra.*, s.*');
Should be bring all Amostra records and all fields, and all fields of solicitacao (as extra properties on variant).

What I mean is that it doesn't matter that the joins I put on where, should return only TORM´s fields passed on first parameter.
Unless I put join fields in the fields, but in this case I will be bringing the information in json without any problems

Last edited by mrbar2000 (2024-03-08 16:07:38)