How to correctly update date fields in SQLite?

[JanRoza]

I'm having a weird problem with SQLite. I've done several projects with SQLite so far and all without problems, but then that were projects without date and time fields in the database.
Now I have a project that shows a data entry screen for a table which contains date and time fields. The problem is that whenever I update the date and time fields the database becomes NULL the minute I exit the corresponding data entry field in my program.
See screenshots below (date example):

http://213.10.59.241/flight1.jpg
Record read, no changes made yet.

http://213.10.59.241/flight2.jpg
Date changed, see the date disappear in the DBGrid (and SQLite)?

I tried two ways but both give me the same problem: first solution was to have a DBEdit field connected to the date field in SQLite and my second try was to use an Edit field with an On Exit routine. In this On Exit I updated the date field in SQLite using SQL, and as an alternative I also tried the tables edit and post method.

SQLite table structure as shown in SQLite Developer:

Code: [Select]

Begin Transaction;
Create  TABLE MAIN.[Temp_56581899](
[ID] integer PRIMARY KEY AUTOINCREMENT NOT NULL
,[Vluchtnummer] integer
,[IndicatieDbo] boolean
,[Datum] date(10)
,[Registratie] varchar(10)
,[Type] varchar(25)
,[StartMethode] varchar(25)
,[StartTerrein] varchar(25)
,[StartTijd] time(5)
,[VliegTijd] time(5)
,[Opmerkingen] text
);

Insert Into MAIN.[Temp_56581899] ([ID],[Vluchtnummer],[IndicatieDbo],[Datum],[Registratie],[Type],[StartMethode],[StartTerrein],[StartTijd],[VliegTijd],[Opmerkingen])
 Select [ID],[Vluchtnummer],[IndicatieDbo],[Datum],[Registratie],[Type],[StartMethode],[StartTerrein],[StartTijd],[VliegTijd],[Opmerkingen] From MAIN.[Vluchten];
Drop Table MAIN.[Vluchten];
Alter Table MAIN.[Temp_56581899] Rename To [Vluchten];

Commit Transaction;
What am I missing?
Is it my program or could I have defined my SQLite table wrongly?
How to correctly maintain date fields in SQLite?

 
Jan

[LuizAmérico]

What component are you using ? Sqldb, Zeos, sqlite3dataset

[JanRoza]

Sorry, forgot to mention that detail.
I'm using sqlite3dataset.
 

[LuizAmérico]

Take a look in http://sqlite4fpc.yolasite.com/faq.php. It should help you.

It seems you are using the TZVDatetimePicker right?


Can you try with an standard DBEdit Control?

[JanRoza]

You're correct, I used the TDBZVDateTimePicker component.
I just did a test replacing the DBZVDateTimePicker with a standard DBedit but the result is the same: the moment I exit the DBedit I already see from the DBGrid that the date field in the table is empty again.
It's such a strange behavior that I have no idea where to look.
Sure hope you can help and solve this annoying riddle.

[LuizAmérico]

Are you able to reproduce in a smaller test?

I added, edited and saved with dbgrid and dbedit without problems.

What fpc version are you using?

[JanRoza]

It doesn't get much smaller than this program is at the moment. It's only a skeleton which will be extended only after I get this date thing working correctly.
I use Lazarus 0.9.28.3 and FPC 2.4.0.

[LuizAmérico]

If you can send a minimal example showing the problem, i will take a look.

[JanRoza]

Just send it to you via private message.

[LuizAmérico]

Currently i'm not in my develop machine. I'll take a look tomorrow.

[JanRoza]

No hurry, I'm away all day tomorrow so take your time.

[LuizAmérico]

Ive found some problems:

1) You are updating date manually (Using sql queries) using the date returned by DateToStr. See Q3 of the FAQ (i posted link earlier)
  The correct is :
  strStartDatum := FloatToStr(tblVluchten.FieldByName('Datum').AsDateTime);
  and in the query is not necessary to put quotes

  TSqliteDataset can do this in behinds. You can try:

  sqlite.Append;
  sqlite.FieldByName('Datum') := Date; // as TDateTime
  [..]
  sqlite.Post;
  sqlite.ApplyUpdates;


- You are using ExecSql to execute Select queries in DB_RegistratieExit. Use Open instead. See Q1 of the FAQ

[JanRoza]

Thanks for the comments Luiz, I changed things as you suggested.
Unfortunately things still do not work and the date field in the table still becomes NULL after exiting the date field on screen.

I tried to solve things with a non-DB TZVDateTimePicker and in its exit routine I do the following:

Code: [Select]

Procedure TfrmMain.edt_StartDatumExit(Sender: TObject);
Begin
  tblVluchten.Edit;
  tblVluchten.FieldByName('Datum').AsDateTime := edt_StartDatum.Date;
  tblVluchten.Post;
  tblVluchten.ApplyUpdates;
end;

But still the result is NULL in the database.

But in the original program I used a TDBZVDateTimePicker (DB coupled DateTimePicker) and isn't it normal that the corresponding date field in the database is automatically updated when I select a date via this DateTimePicker (instead of NULL)?

[LuizAmérico]

I think that now i've found the problem. The date/time fields where declared as date(x)/time(x) and sqlitedataset was recognizing them as string fields.

Change the definition to date/time. I think that will work.

BTW: the better way to create tables is to use the provided functions. This will ensure the use of correct types

if not tblVluchten.TableExists then
begin
    tblVluchten.FieldDefs.Add('Datum', ftDate);
    [..]
    tblVluchten.CreateTable;
end;

[JanRoza]

BINGO !!!! 

That was the golden tip I that I was waiting for!
I had a gut feeling it had something to do with the table definitions but couldn't lay my finger on it ..... but you did.
I changed the date and time fields in the table to type datetime (without a specified length) and now all screen fileds work as they should and I do see the results in the table.
No more NULLs, so you really solved it.
Great! 
Many thanks Luiz, you're the best!

 
Jan