mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Query selection on a date

I'm trying to create a unit test where selection of a specific record will be done on a TDatetime field.

  // Create 10 records
  CreateSmallModelData;

  // Select a single record by Date
  Rec := TSQLUnitTestSampleRecord.CreateAndFillPrepare(Client,'Datum=?', [], [DateToIso8601Text( EncodeDate(2012, 5, 4))]);
  Rec.FillOne;
  CheckEqualsString(Format(cPPName, [4]), Rec.Name);

When i run this against an oracle database, the SQL statement that is eventually executed in the TSQLRestServerStaticExternal.ExecuteInlined method is

SELECT ID,ModTime,Name,Question,Address_,PostalCode,City,Datum FROM UnitTestSampleRecord WHERE Datum="2012-05-04'';

When i run this against the nexusDB database (for which we are coding a native driver for mORMot) then the executed method is:

SELECT ID,ModTime,Name,Question,Address_,PostalCode,City,Datum FROM UnitTestSampleRecord WHERE Datum=''2012-05-04'';

Exactly the same .. Both oracle and Nexus have their own ISO8601 implementation but i dont see any of those methods used when executing a SQL statement via
the ExecuteInline way ... When Nexus tries to execute the SQL it results in an error, because a date literal should always be preceded with the keyword "DATE"

How should we solve this?

Last edited by Bascy (2012-08-27 10:06:27)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Query selection on a date

I'm working on a solution.

Thanks for the report.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Query selection on a date

See http://synopse.info/fossil/info/eaa12aef16
  *  new DateToSQL() and DateTimeToSQL() functions, returning a string with a JSON_SQLDATE_MAGIC prefix and proper UTF-8 / ISO-8601 encoding to be inlined as ? bound parameter in any SQL query (allow binding of date/time parameters as request by some external database engine which does not accept ISO-8601 text in this case)
  *  ExtractInlineParameters() and SQLParamContent() decode ':("\uFFF12012-05-04"):' inlined parameters (i.e. text starting with JSON_SQLDATE_MAGIC after UTF-8 encoding) as sftDateTime kind of parameter
  *  inlined parameters in any SQL query will bind explicitely TDateTime values if the parameter is transmitted as DateToSQL() or DateTimeToSQL() TEXT
  *  documentation update about parameter binding (and date/time handling)

So in your case, you may have to code:

Rec := TSQLUnitTestSampleRecord.CreateAndFillPrepare(Client,'Datum=?', [], [DateToSQL(EncodeDate(2012, 5, 4))]);

And it will work as expected, also with a Nexus database back-end.