mORMot Open Source

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

Hi folks,

I don't really use the ORM but this time I wanted to take advantage of FetchAllAsJSON method, sadly i can't make a query behave, let's say I have a table in the database with a field ( UPDATED_AT Timestamp ) so I can query like this:

select field1, field2 from mytable where mytable.UPDATED_AT >= :FECHA

The previous sentence used in a TZQuery gaves the expected results ( 3 rows in my case )

This also works fine:

select field1, field2 from mytable where mytable.UPDATED_AT >= '2020-07-22 01:00:00'

For the ORM For querying I'm using

select field1, field2 from mytable where mytable.UPDATED_AT >= ?

and executing the query like this

var
  aTimeStamp: TDateTime;
Begin
  aTimeStamp := EncodeDateTime(2020, 7, 22, 1, 0, 0, 0);
  fDbConnection.Execute( 'select field1, field2 from mytable where mytable.UPDATED_AT >= ?', [DateTimeToSQL(aTimeStamp)] );
End

Gaves 0 records for the same param value, and it doesn't matter if I pass the date as a parameter or if I include it as text I will get 0 rows.

I had the code done using old versions of UNIDAC/mORMot with the same results and because of this, I thought it was better to update mORMot to the latest, so I cloned the mORMot git repo, same for  Zeos ( Im using 7.3 ) but it didn't make a difference, so I don't think it was a problem with unidac, nor with zeos.

Do any of you know how to get the same results query with the ORM as with direct ZEOS/Unidac ?

P.S. I am using Firebird 3, and Delphi 2007 for this, I can see zeos has a property PROTOCOL which can be firebird3up or firebird, couldn't find how to use firebird3up.
P.S.2  The database is legacy and in fact I'm not using the ORM just the SynDB stuff.

Last edited by moctes (2020-07-29 15:23:40)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

What is the UPDATED_AT column type in the DB?

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

The function DateTimeToSQL() internally uses DateTimeToIso8601(DT,true,'T',WithMS). So the result will always have a "T" between date and time. In your case you have to call DateTimeToIso8601() directly with the correct parameters.

With best regards
Thomas

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

DateTimeToSQL() value is expected to be bound as a date/time DB parameter just prior to SynDB execution, and not as string.
Try to debug why it is not the case.

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

What is the UPDATED_AT column type in the DB?

The type is TIMESTAMP

you have to call DateTimeToIso8601() directly with the correct parameters

ok, so now I did this :

fDbConnection.Execute( sqlText, [DateTimeToIso8601(fechaConsulta,true,' ',False)] )

Now the relevants lines of the log ( using a simplified query from the actual database ) :

20200729 14234861  # DB        SynDBZeos.TSQLDBZEOSStatement(024efb60) Prepare 28us SELECT PRODUCTOS.CODIGO,PRODUCTOS.DESCRIPCION, PRESENTACIONES.neto10 FROM PRODUCTOS INNER JOIN PRESENTACIONES ON (PRODUCTOS.CODIGO = PRESENTACIONES.CODIGO) where   presentaciones.updated_at >= ?

20200729 14235025  # SQL       SynDBZeos.TSQLDBZEOSStatement(024efb60) ExecutePrepared 786.08ms SELECT PRODUCTOS.CODIGO,PRODUCTOS.DESCRIPCION, PRESENTACIONES.neto10 FROM PRODUCTOS INNER JOIN PRESENTACIONES ON (PRODUCTOS.CODIGO = PRESENTACIONES.CODIGO) where   presentaciones.updated_at >= '2020-07-22 01:00:00'

So using DateTimeToIso8601 results on the same sql sentence I previously tested ( without parameters ) and is still returning 0 rows instead of 3.

Edit: The SQL from the log (ExecutePrepared)  I can use exactly that with TZQuery and the result is 3 rows

Last edited by moctes (2020-07-29 14:46:32)

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

I want to apologize, there was a wrong config setting from my side (somewhere along the testing I did this ), so the queries made with TZQuery were executed against a test database and (ouch!) the SynDB where being executed against a dev database, so the advice from @tbo about DateTimeToIso8601 was the definitive answer for getting this running with parameters sorry if i wasted your time, it is already working as it should.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

Maybe Arnaud changes the function DateTimeToSQL() a little bit like this:

function DateTimeToSQL(DT: TDateTime; WithMS: boolean=false; HasTimeChar: Boolean=true): RawUTF8;
const
  TIME_CHAR: array[Boolean] of AnsiChar = (' ', 'T');
...

And replace 'T' with TIME_CHAR[HasTimeChar]

With best regards
Thomas

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

I want to apologize, there was a wrong config setting from my side (somewhere along the testing I did this ), so the queries made with TZQuery were executed against a test database and (ouch!) the SynDB where being executed against a dev database, so the advice from @tbo about DateTimeToIso8601 was the definitive answer for getting this running with parameters sorry if i wasted your time, it is already working as it should.

Lorbs, looking what you're doing ... why the hell.. You convert a valid TDateTime value to a Raw-String?
Isn't

fDbConnection.Execute( 'select field1, field2 from mytable where mytable.UPDATED_AT >= ?', [aTimeStamp] ); 

exactly what you want? Or does it not work?
OTH the 'T' delimiter is desturbing most sql db's. MSSQL prefers the packed format without any delimiters, oracle as well. Firebird, MySQL, PostgreSQL, ASE, ASA simply don't understand this format it's just for SQLite and MongoDB, and the JSON's usefull, thought.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

DateTimeToSQL() should not be 'T' sensitive.
As I wrote:

DateTimeToSQL() value is expected to be bound as a date/time DB parameter just prior to SynDB execution, and not as string.
Try to debug why it is not the case.

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

Lorbs, looking what you're doing ... why the hell.. You convert a valid TDateTime value to a Raw-String?
Isn't

fDbConnection.Execute( 'select field1, field2 from mytable where mytable.UPDATED_AT >= ?', [aTimeStamp] ); 

exactly what you want? Or does it not work?
OTH the 'T' delimiter is desturbing most sql db's. MSSQL prefers the packed format without any delimiters, oracle as well. Firebird, MySQL, PostgreSQL, ASE, ASA simply don't understand this format it's just for SQLite and MongoDB, and the JSON's usefull, thought.

Well I'm here to learn in fact your snippet is better I mainly use mORMot as a kind of proxy usually the data is coming from another api, I'll follow your advice and give it a try

Do you know if PROTOCOL can be somehow changed to firebird3up on TSQLDBZEOSConnectionProperties ?  I suppose it would be more appropriate

Thanks

Last edited by moctes (2020-07-29 19:43:41)

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

Well I'm here to learn smile in fact your snippet is better I mainly use mORMot as a kind of proxy usually the data is coming from another api, I'll follow your advice and give it a try

Do you know if PROTOCOL can be somehow changed to firebird3up on TSQLDBZEOSConnectionProperties ?  I suppose it would be more appropriate

The "firebird3up" protocol is the legacy api replacement of fbclient.dll using the object interfaces. See https://firebirdsql.org/file/documentat … s-api.html,  i added some month's ago. The reason for the extra protocol name is: some in the Zeos team would like to test/use firebird 3 with the new interface api and with the old legacy api of course.
We'll leave the protocol by now... Feel free to test. It's possible we'll remove the protocol name again by implementing a preloading mechanism of the library and decide wich ZDBC objects are used for the connection. I just wrote the driver, the new name wasn't on >my< whishlist

The new API is supposed ( by the FB devels )to be faster. I didn't make performance tests yet, due the fact i need to patch SynDBZeos again to get multiple protocols running using the same SQLDefinition enum SynDB requires for..

Update
I've removed the protocol name now! It depends on the client lib which API is used. Using firebird3+ with the legacy API can be improved by choosing the "interbase" protocol name. Thus the name was superfluous. It will be mentioned in the rease notes.

Last edited by EgonHugeist (2020-07-30 07:45:19)

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

Re: ORM, SynDBZEOS, Firebird Datetime params [SOLVED]

Update
I've removed the protocol name now! It depends on the client lib which API is used. Using firebird3+ with the legacy API can be improved by choosing the "interbase" protocol name. Thus the name was superfluous. It will be mentioned in the rease notes.

Great, thank you !