About cache and RetrieveDocVariantArray

mrbar2000 · 2024-11-20 20:16:59

I have the following scenario below and I don't understand why the ORM is not taking into account the parameterized query I am making, creating another completely different query.

  TAmostra = class(TOrm)
  private
    FNumero: RawUTF8;
    FSolicitacaoID: TID;
    FExames: RawUTF8;
    FDataEtiqueta: TDateTime;
    FRackID: TNullableInteger;
    FPostoAtual: TID;
    FColetado: Boolean;
    FCancelada: Boolean;
    FPostoAtualID: TID;
    FEmpresaID: TID;
  published
    property EmpresaID: TID read FEmpresaID write FEmpresaID;
    property Numero: RawUTF8 index 40 read FNumero write FNumero;
    property SolicitacaoID: TID read FSolicitacaoID write FSolicitacaoID;
    property Exames: RawUTF8 index 500 read FExames write FExames;
    property DataEtiqueta: TDateTime read FDataEtiqueta write FDataEtiqueta;
    property RackID: TNullableInteger read FRackID write FRackID;
    property Coletado: Boolean read FColetado write FColetado;
    property PostoAtualID: TID read FPostoAtualID write FPostoAtualID;
  end;

When I do:

FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', 
  ' inner join Solicitacao s on (s.RowID = Amostra.solicitacaoID)  where (amostra.empresaID = ?) and (s.numero = ?) order by s.numero, Amostra.numero '
  [1, "4"],
  'Amostra.RowID, Amostra.*, s.numero numerosolicitacao, s.paciente, s.datasolicitacao');

Why does this prepare a sql like this:

select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,PostoAtualID,ID 
from public.Amostra where EmpresaID=?

From what I debugged, it executes the above sql throwing all CompanyID objects = 1 into a cache. I need all TAmostra where EmpresaID=1 and Number="4".
In a previous post AB informed me that RetrieveDocVariantArray works anyway, so why the "FormatSqlWhere" and "BoundsSqlWhere" parameters if this method doesn't respect it?
I looked in the documentation and on the forum how to disable the cache but I didn't find it or it didn't work.

function TRepository.ConnectionClass(pDBConnection: TSqlDBConnectionPropertiesClass): IRepository;
begin
  Result := Self;
  FConfigDB := pDBConnection.Create(FHost, FDatabase, FUser, FPassword);
  FConfigDB.ConnectionTimeOutMinutes := 2;
  VirtualTableExternalRegisterAll(FOrmModel, FConfigDB);
  FRestOrm := TRestServerDB.Create(FOrmModel, SQLITE_MEMORY_DATABASE_NAME);
  FRestOrm.AcquireExecutionMode[execOrmGet] := amBackgroundOrmSharedThread;
  FRestOrm.AcquireExecutionMode[execOrmWrite] := amBackgroundOrmSharedThread;
  FRestOrm.DB.UseCache := False;   <<<<<< dont disable the cache
  FRestOrm.DB.Synchronous := smOff;
  FRestOrm.DB.LockingMode := lmExclusive;
  FRestOrm.Server.CreateMissingTables;
  CreateIndexes;
  FRestOrm.Server.TrackChanges([TEmpresa, TUsuario, TAmostra,
    TRack, TEvento], TMyHistory, 100, 10, 65536);
end;

Last edited by mrbar2000 (2024-11-20 20:19:58)

ab · 2024-11-20 20:47:31

The ORM does not support any such complex "Where" clause with an inner join.
Your confusion comes also from the fact that your request is executed via SQLite3 virtual tables.
So the SQLite3 virtual table engine makes several queries, some with "where EmpresaID=?" condition.

As documented, you should use a direct SQL statement for this in the external DB (i.e. FConfigDB), without the ORM nor the main SQLite3 database involved.

mrbar2000 · 2024-11-21 03:04:50

"The ORM does not support any such complex "Where" clause with an inner join"
But it bring the records with fields passed.

I whould like understand why It take "EmpresaID=?". I have others properties TID on TAmostra, but orm put just EmpresaID.

I can use FConfigDB to make my complex queries and use ORM to insert, update, delete? what impact about this? I´m using postgress (TSqlDBPostgresConnectionProperties). All samples that I see, use or somefile.db or SQLITE_MEMORY_DATABASE_NAME.

I´m using frontend made in nocode plataform (testing yet) then all this code is on server application.

Last edited by mrbar2000 (2024-11-21 03:25:38)

ab · 2024-11-21 07:23:42

I can use FConfigDB to make my complex queries and use ORM to insert, update, delete

: this is the way to go.

ab · 2024-11-21 16:17:35

I have added a new ISqlDBStatement.FetchAllToDocVariantArray method, which you could use directly on the FConfigDB results.

mrbar2000 · 2024-11-21 21:00:00

thanks man!

mORMot Open Source

#1 2024-11-20 20:16:59

About cache and RetrieveDocVariantArray

#2 2024-11-20 20:47:31

Re: About cache and RetrieveDocVariantArray

#3 2024-11-21 03:04:50

Re: About cache and RetrieveDocVariantArray

#4 2024-11-21 07:23:42

Re: About cache and RetrieveDocVariantArray

#5 2024-11-21 16:17:35

Re: About cache and RetrieveDocVariantArray

#6 2024-11-21 21:00:00

Re: About cache and RetrieveDocVariantArray

Board footer