Inserting dates using “A Simple Delphi Wrapper for

Question

I am using delphi 2010, and Tim Anderson's SQLite3 wrapper - http://www.itwriting.com/blog/?page_id=659 - but I am having trouble inserting dates

Here is my database creation

DB.ExecSql('CREATE TABLE Tags (No Integer NOT NULL, Title VarChar(25) NOT NULL, Creator VarChar(25) NULL, Born Date NULL, Charter Boolean Default False NULL, Owned Boolean Default False NULL, Image Blob NULL, CONSTRAINT PK_No PRIMARY KEY (No));');

Which builds and works fine. I tested it with SQLite administrator - http://sqliteadmin.orbmu2k.de/ I am even able to manually enter dates using adminstrator

here is my insert

DB.ExecSql('Insert into Tags (No, Title, Creator, Born, Charter, Owned) ' +
             'values (' + quotedStr(frmTag.edtTagNo.Text) + ',' + quotedStr(frmTag.edtTitle.Text) + ',' +
                          quotedStr(frmTag.edtCreator.Text) + ',' + quotedStr(frmTag.edtBorn.Text) + ',' +
                          quotedStr(BoolToStr(frmTag.cbxCharter.Checked)) + ',' + quotedStr(BoolToStr(frmTag.cbxOwned.Checked)) + ');');

The date field is being supplied by the edtBorn control (TRzDateEdit)

I have checked the values of edtBorn.Text amd edtBorn.date prior to the insert and the date is always correct.

I have tried inserting the following ways:

frmTag.edtBorn.Text 
FormatDateTime('mm/dd/yyyy',frmTag.edtBorn.Text)
quotedStr(frmTag.edtBorn.Text)
quotedStr(FormatDateTime('mm/dd/yyyy',frmTag.edtBorn.Text))

I have even tried using a parameters

 DB.AddParamText('@ABorn', frmTag.edtBorn.Text);
 DB.AddParamFloat('@ABorn', frmTag.edtBorn.Date);

Nothing seems to work! I get no exceptions, yet my field never gets a dat value!

Answer 1

Date and Time Datatype

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.

Showing here for example the REAL and INTEGER part.

modified SQLite3 wrapper Testfile: uTestSqlite

sSQL := 'CREATE TABLE testtable ([ID] INTEGER PRIMARY KEY,[OtherID] INTEGER NULL,';
sSQL := sSQL + '[Name] VARCHAR (255),[Number] FLOAT,[Date] INTEGER, [notes] BLOB,
       [picture] BLOB COLLATE NOCASE);';
sldb.execsql(sSQL);
sldb.execsql('CREATE INDEX TestTableName ON [testtable]([Name]);');

//begin a transaction
sldb.BeginTransaction;

sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Date) VALUES ("Some Name", 4,
         julianday("now"), strftime("%s","now"));';
sldb.ExecSQL(sSQL);

sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Date,Notes) VALUES ("Another Name",12,
         julianday("2013-03-01"),strftime("%s","2013-03-01"), "More notes");';
sldb.ExecSQL(sSQL);

//end the transaction
sldb.Commit;

[...]

//query the data
sltb := slDb.GetTable('SELECT * FROM testtable');
if sltb.Count > 0 then
begin
//display first row
updateFields;
end;

Show the values:

procedure TForm1.updateFields;
var
Notes: string;
myDate :TDateTime;

begin
ebName.Text := sltb.FieldAsString(sltb.FieldIndex['Name']);
ebID.Text := inttostr(sltb.FieldAsInteger(sltb.FieldIndex['ID']));

if TryJulianDateToDateTime(sltb.FieldAsDouble(sltb.FieldIndex['Number']),myDate)
   then
   ebNumber.Text := DateTimeToStr(myDate)
   else
   ShowMessage('Not a valid Julian date');

myDate:=UnixToDateTime(sltb.FieldAsInteger(sltb.FieldIndex['Date']));
ebDate.Text := DateTimeToStr(myDate);

[...]
end;

Output:

In your problem with DB.ExecSql('CREATE TABLE Tags (..., Born Date NULL,...);');

replace in DB.ExecSql('Insert into Tags (....) VALUES (...

+ quotedStr(frmTag.edtBorn.Text) 

with

'strftime("%Y-%m-%d","'+frmTag.edtBorn.Text+'")'

The value of frmTag.edtBorn.Text must be like 1975-10-21

You can get it with:

ebDate.Text := sltb.FieldAsString(sltb.FieldIndex['Born']);

Answer 2

I use both Delphi 2010 and Tim Anderson's SQLite3 wrapper.
Here's what I use to create & use a datetime field. Its fairly straight forward and has been working for me. I trust that you can figure out the concept illustrated below with me having to write you a demo program.

SQL to create field:

  sSQL :=  'CREATE TABLE [someTable] (' +
            '  [somefield1] VARCHAR(12),' +
            '  [somefield2] VARCHAR(12),' +
            '  [myDateTime] DATETIME );';

SQL to populate field:

 sSQL := 'INSERT INTO someTable(somefield1, somefield2, myDateTime)' + 
         '  VALUES ( "baloney1", "baloney2","' + FloatToStr(Now) + '");';

Example of retrieving data from field:

var
sDBFilePathString: string;
sl3tbl: TSqliteTable;
fsldb : TSQLiteDatabase;
FromdbDTField : TDateTime;

begin
   ...
   ... 
    fsldb := TSQLiteDatabase.Create(sDBFilePathString); 
    sl3tbl := fsldb.GetTable('SELECT * FROM someTable');
    FromdbDateTime := StrToFloat(sl3tbl.FieldAsString(sl3tbl.FieldIndex['myDateTime']));
    Showmessage('DT: ' + DateTimeToStr(FromdbDTField));
end;

Result:

 **DT: 10/10/2013 1:09:53 AM**

I leave it to you to make things prettier or more elegant.

Answer 3

I've made my own 'really light' SQLite3 wrapper, but use Variants to decide whether which sqlite3 internal type to use: https://github.com/stijnsanders/TSQLite

There I've found the 'loose typing' sqlite3 internally works really well, and I found out that Delphi's dates stored in a Variant turn into a floating point value in SQLite3 (which TDateTime actually is by the way). A possible downside is the date values are a bit clumsy to manipulate from SQL. I regret to see you've already tried AddParamFloat (and FieldAsFloat) and this didn't appear to work.

So I suggest you store the date as a string, using one if the sqlite date formats, for example using FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',d). See more here: http://www.sqlite.org/lang_datefunc.html