How to correctly update date fields in SQLite?
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2010-03-19 15:29

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 · 2010-03-19 16:18

What component are you using ? Sqldb, Zeos, sqlite3dataset

----------------------------------------------------------------------
JanRoza · 2010-03-19 16:52

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

----------------------------------------------------------------------
LuizAmérico · 2010-03-19 19:09

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 · 2010-03-19 19:25

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 · 2010-03-19 19:40

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 · 2010-03-19 19:51

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 · 2010-03-19 20:17

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

----------------------------------------------------------------------
JanRoza · 2010-03-19 20:51

Just send it to you via private message.

----------------------------------------------------------------------
LuizAmérico · 2010-03-19 23:39

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

----------------------------------------------------------------------
JanRoza · 2010-03-20 01:30

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

----------------------------------------------------------------------
LuizAmérico · 2010-03-20 15:09

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 · 2010-03-20 19:00

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 · 2010-03-20 19:59

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 · 2010-03-20 20:20

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

----------------------------------------------------------------------
LuizAmérico · 2010-03-20 20:31

I've been very busy lately, but i really hope i can complete the
documentation soon to help sqlite3dataset users.

----------------------------------------------------------------------
JanRoza · 2010-03-20 21:25

When you do I will be one of the first to download and read it as I
think SQLite is a great product with lots of potential.
In Delphi I always use Absolute database which too is a wonderful
product, but its owner has no intention to adapt it for FPC. In FPC
SQLite is the preferred database, but I still have to learn a lot
about it.
Thanks again and keep up the good work on SQLite!