From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00 autolearn=unavailable autolearn_force=no version=3.4.4 X-Received: by 10.237.35.174 with SMTP id j43mr30447236qtc.29.1471467908926; Wed, 17 Aug 2016 14:05:08 -0700 (PDT) X-Received: by 10.157.51.45 with SMTP id f42mr1090997otc.12.1471467908862; Wed, 17 Aug 2016 14:05:08 -0700 (PDT) Path: eternal-september.org!reader01.eternal-september.org!reader02.eternal-september.org!news.eternal-september.org!news.eternal-september.org!feeder.eternal-september.org!news.glorb.com!j37no9065270qta.0!news-out.google.com!d68ni30818ith.0!nntp.google.com!f6no10739653ith.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.lang.ada Date: Wed, 17 Aug 2016 14:05:08 -0700 (PDT) Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=76.218.37.33; posting-account=W2gdXQoAAADxIuhBWhPFjUps3wUp4RhQ NNTP-Posting-Host: 76.218.37.33 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: GNATCOLL SQLite3 vs Ada.Calendar.Time From: Stephen Leake Injection-Date: Wed, 17 Aug 2016 21:05:08 +0000 Content-Type: text/plain; charset=UTF-8 Xref: news.eternal-september.org comp.lang.ada:31453 Date: 2016-08-17T14:05:08-07:00 List-Id: I've run into some very odd behavior using GNATCOLL to interface to an SQLite3 database, and querying on a DATETIME field. Here's a very simple program that shows the problem: -- create_db.sql -- Create a table, fill it with some test data -- SQLite3 syntax CREATE TABLE Title (ID INTEGER PRIMARY KEY, Modified DATETIME, Title TEXT); INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "2001"); INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation"); INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundations Edge"); INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Earth"); INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Empire"); -- end of file -- debug_db_times.adb -- Abstract : -- -- demonstrate a bug in GNATCOLL/sql related to time field handling pragma License (GPL); with Ada.Text_IO; use Ada.Text_IO; with Ada.Calendar; with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec; with GNATCOLL.SQL.Sqlite; with GNATCOLL.Traces; procedure Debug_DB_Times is DB : Database_Connection := Build_Connection (GNATCOLL.SQL.Sqlite.Setup ("debug_db_times.db")); Cursor : Forward_Cursor; Before_Created : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (1999, 1, 1); Jan_1_2000 : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (2000, 1, 1); procedure Put_Current is begin for I in Field_Index'(0) .. 2 loop Put (Cursor.Value (I)); if I /= 2 then Put (", "); end if; end loop; New_Line; end Put_Current; begin GNATCOLL.Traces.Parse_Config_File (Filename => "debug_db_times.trace", Force_Activation => False); Put_Line ("find all:"); Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title ORDER BY ID"); loop exit when not Cursor.Has_Row; Put_Current; Cursor.Next; end loop; Put_Line ("find modified time (long before created):"); Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Before_Created)); loop exit when not Cursor.Has_Row; Put_Current; Cursor.Next; end loop; Put_Line ("find modified time (1 day before created):"); Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Jan_1_2000)); loop exit when not Cursor.Has_Row; Put_Current; Cursor.Next; end loop; Put_Line ("find modified string:"); Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +"2000-01-01 00:00:00")); loop exit when not Cursor.Has_Row; Put_Current; Cursor.Next; end loop; end Debug_DB_Times; -- end of file -- debug_db_times.trace SQL=TRUE SQL.SELECT=TRUE -- end of file Running on Windows 8.1 with GNAT GPL 2016: sqlite3 -echo -init create_db.sql debug_db_times.db ".quit" gprbuild -p -P debug_db_times.gpr debug_db_times.adb ./debug_db_times.exe find all: [SQL] Connecting to sqlite database debug_db_times.db () [SQL] PRAGMA foreign_keys=ON [SQL.SELECT] SELECT ID, Modified, Title FROM Title ORDER BY ID 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 find modified time (long before created): [SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 1999-01-01 06:00:00 1, 2000-01-02 00:00:00, 2001 find modified time (1 day before created): [SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 06:00:00 1, 2000-01-02 00:00:00, 2001 find modified string: [SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 00: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 When I pass an Ada.Calendar.Time as a parameter, I only get one record back. When I pass a string as a parameter, I get all the records back. Note that Ada.Calendar.Time messes with time zones in an undocumented way (I happen to be in GMT+6); that's not a problem, since I figured out how to deal with it in my full program. In my full program, I have similar but different behavior, and always passing a string doesn't fix it. 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. Any ideas what's going on here?