mORMot Open Source

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Problem querying with DateTimeToSQL

Hello ab,

I hit by this issue several times, so I thought I should post it here

The issue is that,  following documentation https://synopse.info/files/html/Synopse … l#TITL_36), when querying with a TDateTime parameter wrapped by DateTimeToSQL, like following:

var
  svrTime: TDateTime;
  whereClause: string;
begin
  ...
  whereClause := '((StartTime <= ?) AND (EndTime >= ?)) ORDER BY Priority ';
  arbiPromoHdr := TSQLArbitraryPromo.CreateAndFillPrepare(aDb, whereClause, [DateTimeToSQL(svrTime), DateTimeToSQL(svrTime)]);
 ...
end;

It'll return no result. It seems that the problem is coming from the SQL generated by the engine, check this screenshot below, I got the SQL from the engine's log file:

I removed the :) prefix/postfix, the query still return no result.

The query will return the correct result, if I removed the mystery character before the datetime value, which I believe is JSON_SQLDATE_MAGIC

Not sure if I'm doing anything wrong, I followed the docs. I'll be appreciated if you can give me any hints.

Last edited by edwinsn (2017-08-30 07:20:32)

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Problem querying with DateTimeToSQL

If I remember correctly, DateTimeToSQL() will work only for inlined parameters (i.e. with ?), over external databases.
If you are using a SQlite3 database only, the DateTimeToSQL() is not necessary: since date/time will be stored as ISO-8601 text in the SQLite3 fields, text search should work as expected.

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Re: Problem querying with DateTimeToSQL

@ab,

Tried, it doesn't seem to work - without DateTimeToSQL, the TDateTime values in the resulting SQL look like this:

WHERE ((IsDisabled=:(0):) AND (StartTime <= :(42977.7362847222):) AND (EndTime >= :(42977.7362847222):))

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Re: Problem querying with DateTimeToSQL

@ab,

Well, after further testing, looks like the issue is not that DateTimeToSQL is not working, it DOES work.
But the SQL logged by the engine is not expected to be run by sample 12 - SynDB Explorer...

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.