I need a help with FillPrepareMany functionality

Roberto Schneiders · 2013-10-29 17:06:11

I am trying to use the FillPrepareMany but isen't working. Someone can help me?

These are my entities classes.

  TFornecedor = class(TSQLRecord)
  private
    FCodigo: integer;
    FNome: RawUTF8;
  published
    property Codigo: integer read FCodigo write FCodigo;
    property Nome: RawUTF8 read FNome write FNome;
  end;

  TProdutoXFornecedor= class(TSQLRecordMany)
  private
    fSource: TProduto;
    fDest: TFornecedor;
  published
    property Source: TProduto    read fSource;
    property Dest  : TFornecedor read fDest;
  end;

  TProduto = class(TSQLRecord)
  private
    FDescricao: RawUTF8;
    FCodigo: integer;
    FDepartamento: TDepartamento;
    FProdutoXFornecedor: TProdutoXFornecedor;
    FDataCadastro: TDateTime;
  published
    property Codigo            : integer             read FCodigo              write FCodigo;        stored AS_UNIQUE;
    property Descricao         : RawUTF8   index 40  read FDescricao           write FDescricao;
    property Departamento      : TDepartamento       read FDepartamento        write FDepartamento;
    property DestList          : TProdutoXFornecedor read FProdutoXFornecedor;
    property DataCadastro      : TDateTime           read FDataCadastro        write FDataCadastro;
  end;

I am trying to use in this way:

  produto.FillPrepareMany(DatabaseServer, 'DestList.Dest.Codigo=?', [], ['1000']);

I receive an exception with this message:
ESQLite3Exception with message 'no such column: DestList'

What i am doing wrong?

ab · 2013-10-29 22:25:59

I do not have any Delphi IDE now, but you may try

produto.DestList.FillPrepareMany(DatabaseServer, 'Dest.Codigo=?', [], ['1000']);

Roberto Schneiders · 2013-10-30 10:12:11

still have the same problem:

  produto.FillPrepareMany(DatabaseServer, 'Dest.Codigo=?', [], ['1000']);

Error: 'no such column: Dest.A.Codigo'

ab · 2013-10-30 12:12:46

produto.FillPrepareMany(DatabaseServer, 'DestList.Dest.Codigo=?', [], ['1000']);

Sounds just fine.

What is the exact SQL statement generated by TSQLRecord.FillPrepareMany()?

Do you use external databases?

Roberto Schneiders · 2013-10-30 12:20:13

I am using a external SQLite3 database.

I realized that i have not tested exactly what you passed in the other post, so, i did other test with this code:

produto.DestList.FillPrepareMany(DatabaseServer, 'Dest.Codigo=?', [], [1000]);

this error occurs: 'near "and": syntax error';

ab · 2013-10-30 12:21:37

Using

produto.FillPrepareMany(DatabaseServer, 'DestList.Dest.Codigo=?', [], ['1000']);

What is the exact SQL statement generated by TSQLRecord.FillPrepareMany()?
And the SQL statement at external DB statement.

Did you change the external table name from its default?

dorival · 2013-10-30 13:35:20

Hi,

Using:

produto.FillPrepareMany(DatabaseServer, 'DestList.Dest.Codigo=?', [], [1000]);

in the line below (of TSQLRecord.FillPrepareMany - mormot.pas) ...

// execute SQL statement and retrieve data
T := aClient.ExecuteList(ObjectsClass,aSQL);

aSQL contains:

'select A.RowID AID,A.Codigo A00,A.Descricao A01,A.Departamento A02,
A.DataCadastro A03,B.RowID BID,C.RowID CID,C.Codigo C00,C.Nome C01
from Produto A,ProdutoXFornecedor B,Fornecedor C
where B.Source=A.RowID and B.Dest=C.RowID and (C.Codigo=:(1000):)'

if I run this select in the program 'SQlite Expert Personal', for exemple, its works ok.

but in my app returns de error: 'no such column DestList'

Obs: in the line below (of TSQLVirtualTableCursorExternal.Search - mORMotDB.pas) ...

fStatement := fProperties.NewThreadSafeStatementPrepared(SQL,true);

'SQL' contains:

'select Codigo,Descricao,Departamento,DestList,DataCadastro,ID from Produto'

...and the table "Produto" don't have the field "DestList" (TSQLRecordMany).

Obs 2:
if I run the procedure "SQLite3ConsoleTests" (of mORMotSelfTests.pas) its works ok.

ab · 2013-10-30 14:49:17

There was indeed an issue when retrieving a TSQLRecord containing TSQLRecordMany properties with external tables...
Please see http://synopse.info/fossil/info/e1c3c95b95

Note that such JOINed query via external tables are not optimized with the current ORM core: the JOIN is processed within the SQLite3 virtual table engines, not directly at SQL level.
This is a limit of the current implementation...
It could be a good idea to migrate from a RDBMS view into a no-sharding kind of storage, e.g. using dynamic arrays instead of TSQLRecordMany.

dorival · 2013-10-30 16:53:50

Hi,
now return the error:

'SQL logic error or missing database'

in the line below (of TSQLRecord.FillPrepareMany - mormot.pas) ...

// execute SQL statement and retrieve data
T := aClient.ExecuteList(ObjectsClass,aSQL);

aSQL contains:

'select A.RowID AID,A.Codigo A00,A.Descricao A01,A.Departamento A02,A.DataCadastro A03,
B.RowID BID,C.RowID CID,C.Codigo C00,C.Nome C01
from Produto A,ProdutoXFornecedor B,Fornecedor C
where B.Source=A.RowID and B.Dest=C.RowID and (C.Codigo=:(1000):)'

The exception is created by function "sqlite3_check" (of SynSQLite3.pas - see below).

function sqlite3_check(DB: TSQLite3DB; aResult: integer): integer;
begin
  if (DB=0) or (aResult in [SQLITE_ERROR..SQLITE_ROW-1]) then // possible error codes
    raise ESQLite3Exception.Create(DB,aResult);
  result := aResult;
end;

when the exception is created, aResult contains '1' and DB is <> '0'

The function "sqlite3_check" was called by "function TSQLRequest.Step: integer;".
and funtion Step whas called by "function Execute(aDB: TSQLite3DB; const aSQL: RawUTF8; JSON: TStream;Expand: boolean=false): PtrInt; overload;"...

Last edited by dorival (2013-10-30 16:54:08)

ab · 2013-10-30 17:14:36

The error is at mORMotDB.pas unit level.

The SQL statement used by the virtual table on one table is just not working as expected...

I will try to reproduce the issue in the regression tests, and fix it.

dorival · 2013-10-30 17:18:53

Ok ab, thanks,

I can send my "project" if you need.

Last edited by dorival (2013-10-30 17:19:23)

ab · 2013-10-30 21:28:52

I will just enhance regression tests to use fillcreateprepare with external tables, and I think it will be enough to reproduce the issue...

ab · 2013-11-02 10:02:42

Thanks to newly introduced regression tests, we have fixed errors when executing JOINed queries (e.g. via FillPrepareMany) using external tables.
See http://synopse.info/fossil/info/f4f9bdf93a
and http://synopse.info/fossil/info/a758113d27
and http://synopse.info/fossil/info/c47ea6159e
and http://synopse.info/fossil/info/ecf649f8bb

Should work as expected now.
Only note the potential performance penalty with FillPrepareMany() in respect to direct execution of the SQL (due to marshalling via individual queries within the SQLite3 virtual tables).

Thanks for the report.

dorival · 2013-11-04 11:17:02

Hello Ab, thanks but I think there are more things (for me at least).

I made de update and did run "SQLite3ConsoleTests".

Returned this error:

...
2. mORMot

2.1. File based:

! Exception ESynException raised with messsage:
! sqlite3_key() expects PRAGMA mmap_size=0

Software version tested: 1.0.0.0
Synopse framework used: 1.18
SQlite3 engine used: 3.7.16.2
Generated with: Delphi XE4 compiler

Time elapsed for all tests: 21.73s
Tests performed at 04/11/2013 09:07:17

Total assertions failed for all test suits: 0 / 5,400,743
! Some tests FAILED: please correct the code.

Done - Press ENTER to Exit

ab · 2013-11-04 17:59:37

Please use the latest .obj of the SQlite3 engine as supplied from http://synopse.info/fossil/wiki?name=Get+the+source
That is, http://synopse.info/files/sqlite3obj.7z

You are several versions (years!) behind!

mORMot Open Source

#1 2013-10-29 17:06:11

I need a help with FillPrepareMany functionality

#2 2013-10-29 22:25:59

Re: I need a help with FillPrepareMany functionality

#3 2013-10-30 10:12:11

Re: I need a help with FillPrepareMany functionality

#4 2013-10-30 12:12:46

Re: I need a help with FillPrepareMany functionality

#5 2013-10-30 12:20:13

Re: I need a help with FillPrepareMany functionality

#6 2013-10-30 12:21:37

Re: I need a help with FillPrepareMany functionality

#7 2013-10-30 13:35:20

Re: I need a help with FillPrepareMany functionality

#8 2013-10-30 14:49:17

Re: I need a help with FillPrepareMany functionality

#9 2013-10-30 16:53:50

Re: I need a help with FillPrepareMany functionality

#10 2013-10-30 17:14:36

Re: I need a help with FillPrepareMany functionality

#11 2013-10-30 17:18:53

Re: I need a help with FillPrepareMany functionality

#12 2013-10-30 21:28:52

Re: I need a help with FillPrepareMany functionality

#13 2013-11-02 10:02:42

Re: I need a help with FillPrepareMany functionality

#14 2013-11-04 11:17:02

Re: I need a help with FillPrepareMany functionality

#15 2013-11-04 17:59:37

Re: I need a help with FillPrepareMany functionality

Board footer