Page 1 of 1
UnifiedSQL DATETIME-Macro for SQLite
Posted: Tue 14 Jun 2011 12:33
by Elias Zurschmiede
Hi
As you can read in your help file UniDAC.chm (UniDac 3.70.0.17 28-Apr-2011) chapter "Unified SQL" => "Macros Reference" the DATETIME Macro for SQLite should be "TIMESTAMP" - so your documentation is correct, but in source (SQLiteUniProvider.pas) it's not.
This leads to problems accessing the DateTime fields as .AsDateTime because they threaded as Memo-Fields and not as Date-Fields in Delphi.
My workaround/fix in SQLiteUniProvider.pas:
Change:
Code: Select all
LiteFunctions.AddObject('TODATE', TObject(PChar('CAST(%s AS TEXT)')));
LiteMacros.AddObject('DATETIME', TObject(PChar('TEXT')))
to
Code: Select all
LiteFunctions.AddObject('TODATE', TObject(PChar('CAST(%s AS TIMESTAMP)')));
LiteMacros.AddObject('DATETIME', TObject(PChar('TIMESTAMP')))
Hope to see this fix in the next release. Thanks and best regards
Elias
Posted: Wed 15 Jun 2011 06:44
by AlexP
Hello,
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
That's why we use this code when using TODATE and DATETIME
Posted: Wed 15 Jun 2011 07:32
by Elias Zurschmiede
Hi Alex
Thanks for your reply. I know that with the storage classes of SQLite.
Anyhow creating a column of type "TEXT" is not the same as creating it of type "TIMESTAMP" or "DATETIME" even in SQLite:
Code: Select all
CREATE TABLE "test1" ("descr" VARCHAR(250) , "id" INTEGER PRIMARY KEY AUTOINCREMENT , "testdate" TIMESTAMP)
-UniMetaData (Kind Columns) returns TIMESTAMP so we know the column contains Date/Time values
-TUniTable/TUniQuery creates a field of type TDateTimeField so we can access the Date/Time values the Delphi-way by Field.AsDateTime
-SQLite knows
Code: Select all
CREATE TABLE "test2" ("descr" VARCHAR(250) , "id" INTEGER PRIMARY KEY AUTOINCREMENT , "testdate" TEXT)
-UniMetaData (Kind Columns) returns TEXT so we DONT know the column contains Date/Time values
-TUniTable/TUniQuery creates a TMemoField. We can't access date/time values trough Field.AsDateTime
We use TUniMetaData to build CREATE TABLE Statements for other databases (to easily switch our applications from SQLite to MySQL for example). Having DateTime-Fields as TEXT this is not possible. So why not use TIMESTAMP or DATETIME as i suggested - SQLite supports it and you already have it in your documentation. And even more important: IMHO with UniDAC u try to give us a unified way to access different databases the "same" unified way. Using DATETIME or TIMESTAMP is one step more into this direction so your users don't have to care about the underlaying database.
greeting
Elias
Posted: Wed 15 Jun 2011 11:03
by AlexP
Hello,
When using types conversion (CAST(...), DATE(..), etc), the sqlite3_column_type function of SQLite that defines the column type, returns only “base types”, such as Float, Text, Blob, and Null. If the code you suggested is used, the result will have the Integer type and values will be displayed incorrectly. That's why we are using converting to VARCHAR.
If you get a type of the column that exists in the table, SQLite returns correct data type (DATE, TIMESTAMP, etc).