Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake <stephen_leake@stephe-leake.org>]

On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
> On 17.08.16 23:05, Stephen Leake wrote:
> > One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
> 
> It seems advisable to use SQLite's Date And Time Functions
> when using SQLite for SQL. Thus,
> 
>     " ... WHERE Modified >= datetime(?) ... "
> 

Yes, that works. Although I'm not clear why; 
https://www.sqlite.org/lang_datefunc.html defines that function to return a string, and the examples are about output formatting. Apparently it has a different meaning when processing bound parameters.

> Testing SQL scripts using standard SQL syntax and several
> SQL DBMSs has proven to be useful in detecting caveats.
> SQLite's .dump command seems pretty good at producing
> standard SQL syntax. 

That gives:

INSERT INTO "Title" VALUES(2,'2000-01-02 00:00:00','Foundation');

no "datetime" function call.

But we are talking about bound parameters, which could easily be different.

I need to remember SQL is not Ada; it can silently do the wrong thing any time it wants :(.

-- Stephe