Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

["G.B." <bauhaus@futureapps.invalid>]

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(?) ... "

With Ubuntu's standard GNAT, this change results in:

find modified time (1 day before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= 
datetime(?) ORDER BY ID, 1999-12-31 23:00:00
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire

Also, I think that +"some string" has only recently been added
as an overloaded "+" in GNATCOLL.SQL.Exec, in case it's resolved
to be from there and portability to older installation matters.

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. (I have had cases where using double
quotes for VARCHAR literals had made the DBMS take "foo"
for column FOO, and that's formally correct.)