mORMot Open Source

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

TSQLTableJSON - Compare data on where

I was trying to use FillPrepare to select my fields, with Date compare, but i got Out of Memory, cus i have 700mb of Logs on my Database.
So i tried to use TSQLTableJSON like this:

oSQLTableJSON := BaseDeDados.List([TCentralCompraLog], ' ID, Email ', sWhere);

its worked, but when i try to use something like this on where:

Date(DataHora)         = ''2014-10-22''

i got an AccessViolation, but the same command works on FillPrepare.

so i wanna know if is there a way to do it:

Select ID, Email From MyTable where Date(DataHora) = ''2014-10-22'' 

with TSQLTableJSON  or TSQLRecord.FillPrepare, whatever.

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: TSQLTableJSON - Compare data on where

Use a ? parameter with DateToSQL(() as explained by the documentation.
Please ensure you did read the ORM part of the SAD 1.18 pdf.

Something like that:

oSQLTableJSON := BaseDeDados.List([TCentralCompraLog], ' ID, Email ', 'DataHora=?', [DateToSQL(aDate)]);

And do not forget to create an index on DataHora field - by overriding TCentralCompraLog.InitializeTable() and calling Server.CreateSQLIndex() - if you want good performance.

Victor Zanella

Member

From: Santa Catarina

Registered: 2014-10-10

Posts: 9

Re: TSQLTableJSON - Compare data on where

I tried to use something like this:

    oCentralCompraLog.FillPrepare(BaseDeDados,
                                 'CodigoEmpresa=? and CodigoTipoInformacao=? and TipoIntegracao=? and Mensagem=? and CodigoTipoMensagem=? and  Date(DataHora)=?  limit 1',
                                 [AParametros.CodigoEmpresa, AParametros.CodigoTipoInformacao, AParametros.TipoIntegracao, AParametros.Mensagem, AParametros.CodigoTipoMensagem, DateToSQL(AParametros.DataHora)]);

and it works on small Database.

But when i try to use it at a client database, i got out of memory, and it happens cus i have a Text JSON field on database, so, this select will bring 665 rows, with bigs JSONs .
So if i dont want to bring JSON, how can i do that ?

I dont know how to do it with TSQLRecord object, only with TSQLTabelJSON, but with TSQLTabelJSON, i cant include Date on my where.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: TSQLTableJSON - Compare data on where

Either:

1. Run your query on the server side, encapsulating your process within a SOA design;
2. Specify the fields to retrieve.

But I can't get how 665 rows of data would trigger an out of memory error.