Master-Detail on Date Field

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Master-Detail on Date Field

Post by LHSoft » Fri 27 Jan 2017 00:59

Hello,
How to do a Master-Detail on a Date-Field ([date1]: Date) so it works?
Masterfields := date1 and DetailFields := date1 result in 0 records of Detail-Table.
Doing this in a query, the WHERE statement needs not only the date-part itself but additional time 0:00:00 to work, for example date1='2017-01-27 0:00:00'.
best regards
hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Tue 31 Jan 2017 09:47

We investigated LiteDAC 2.7.27 behavior when using a Master-Detail relationship with the Date type fields. Unfortunately we could not reproduce the described problem. For check, we used a small sample that you can implement yourself. Create a new project and place on the form the following components :

Code: Select all

  
...
    DataSourceDept: TLiteDataSource;
    DataSourceEmp: TLiteDataSource;
    LiteConnection: TLiteConnection;
    QueryEmp: TLiteQuery;
    QueryDept: TLiteQuery;
...


Then execute the following code :

Code: Select all

  LiteConnection.Options.Direct := True;
  LiteConnection.Database := ':memory:';
  LiteConnection.Connect;

  DataSourceDept.DataSet := QueryDept;
  DataSourceEmp.DataSet := QueryEmp;

  LiteConnection.ExecSQL('Create Table Dept (DName Varchar(16), DDate Date)');
  LiteConnection.ExecSQL('Insert Into Dept Values (''ACCOUNTING'', strftime(''%Y-%m-%d'', ''2017-01-10''))');
  LiteConnection.ExecSQL('Insert Into Dept Values (''RESEARCH'', strftime(''%Y-%m-%d'', ''2017-01-15''))');
  LiteConnection.ExecSQL('Insert Into Dept Values (''SALES'', strftime(''%Y-%m-%d'', ''2017-01-20''))');
  LiteConnection.ExecSQL('Create Table Emp (EName Varchar(16), DeptDate Date)');
  LiteConnection.ExecSQL('Insert Into Emp Values (''MILLER'', strftime(''%Y-%m-%d'', ''2017-01-10''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''KING'', strftime(''%Y-%m-%d'', ''2017-01-10''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''SMITH'', strftime(''%Y-%m-%d'', ''2017-01-15''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''JONES'', strftime(''%Y-%m-%d'', ''2017-01-15''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''ADAMS'', strftime(''%Y-%m-%d'', ''2017-01-15''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''ALLEN'', strftime(''%Y-%m-%d'', ''2017-01-20''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''MARTIN'', strftime(''%Y-%m-%d'', ''2017-01-20''))');
  LiteConnection.ExecSQL('Insert Into Emp Values (''JAMES'', strftime(''%Y-%m-%d'', ''2017-01-20''))');

  QueryDept.SQL.Text := 'Select * From Dept';
  QueryDept.Open;

  QueryEmp.SQL.Text := 'Select * From Emp';
  QueryEmp.MasterFields := 'DDate';
  QueryEmp.DetailFields := 'DeptDate';
  QueryEmp.MasterSource := DataSourceDept;
  QueryEmp.Open;
Make sure that this sample also works correctly when using the DateTime field.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Wed 01 Feb 2017 05:22

Hello,
sometimes it can drive one crazy.
Your example works well, but in my project this Master-Detail does not work.
After investigation I found out the way how to add data to your test-tables is important for this error.
The source is any other database, in my case a SQL-Database from SDAC, but I think, this is not important.
I used a TMSQuery with 3 fields (string, date and Integer) I queried from an existing SQL-Table.
I tested 2 ways to get the data into the Lite-Table: standard method with TLiteTable.Insert and other with TLiteSQL using params.
So, the way with TLiteSQL brings the Master-Detail-Error.
Here is my code, the marked out lines do it right, the SQL-method results in error:

Code: Select all

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  LiteConnection.Close;
  DeleteFile(LiteConnection.Database);
end;

procedure TForm1.FormShow(Sender: TObject);
var
  i: Integer;
  SL: TLiteSQL;
begin
  LiteConnection.Options.Direct := True;
  LiteConnection.Database := 'test.db3';
  if not FileExists(LiteConnection.Database) then LiteConnection.Options.ForceCreateDatabase := True;
  LiteConnection.Connect;
  if LiteConnection.Options.ForceCreateDatabase then
    LiteConnection.ExecSQL('Create Table Emp ([Nr] INTEGER PRIMARY KEY AUTOINCREMENT, [EName] nvarchar(20), [DDate] Date, [qty] INTEGER)');
  LiteTable1.TableName := 'Emp';
  LiteTable1.Open;
  if LiteTable1.RecordCount = 0 then
  begin
    SL := TLiteSQL.Create(nil);
    SL.Connection := LiteConnection;
    SL.SQL.Add('INSERT INTO Emp (EName,DDate,qty) VALUES (:EName,:DDate,:qty)');
    MSQuery.SQL.Clear;
    MSQuery.SQL.Add('select ArtikelNr,Lieferdatum,BN from MoStmArt WHERE (BN > 20000) and not (Lieferdatum is NULL)');
    MSQuery.Open; MSQuery.First;
    while not MSQuery.Eof do
    begin
      //LiteTable1.Insert;
      //for i := 0 to MSQuery.Fields.Count - 1 do
      //  LiteTable1.Fields[i+1].Value := MSQuery.Fields[i].Value;
      for i := 0 to SL.Params.Count - 1 do
        SL.Params[i].Value := MSQuery.Fields[i].Value;
      //LiteTable1.Post;
      SL.Execute;
      MSQuery.Next;
    end;
    LiteTable1.Refresh;
    MSQuery.Close;
    MSConnection.Close;
  end;
  LiteTable2.TableName := 'Emp';
  LiteTable2.MasterFields := 'DDate';
  LiteTable2.DetailFields := 'DDate';
  LiteTable2.MasterSource := LiteDataSource1;
  LiteTable2.Open;
end;
best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Fri 03 Feb 2017 09:24

We have carefully studied the sent code snippet. Unfortunately we could not reproduce the described problem. When using our test data your code does not lead to an error when implementing a master-detail relationship. For further investigation, please send us the full source code of your test project via the e-support form ( https://www.devart.com menu "Support"\"Request Support" ). In order to avoid loading data from MS SQL Server, please also send SQLite DB file with already loaded test data.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Fri 03 Feb 2017 09:51

I have just sent you a support request with attached zip file.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Fri 10 Feb 2017 12:31

Hello,
didn't you get my file?
Havn't heard anything yet.

best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Fri 10 Feb 2017 13:50

In the sent database the DDate field has the DATE type, however it contains values that include hours, minutes, seconds. In this case to implement a master-detail relation, you need to use the DATETIME field instead of DATE. In addition, there is another solution — to use the LocalMasterDetail property : https://www.devart.com/litedac/docs/?de ... detail.htm

Code: Select all

  LiteTable1.Options.LocalMasterDetail := True;
  LiteTable2.Options.LocalMasterDetail := True;

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Fri 10 Feb 2017 14:11

Hello,
maybe local Masterdetail would solve the surface of the problem but problem itself exists further on.
The error must be somewhere within your LiteDAC or, perhaps, SQLite itself:
If adding rows with insert and post with TLiteTable or TLiteQuery the master-detail-error does not occur.
If adding the same data from the same source to the same table using TLiteSQL and Params, which should lead to same results of course, the error occurs because there is added a time-value to a pure date-field.
So, if I have understood SQLITE and their datatype-handling correctly, the error should be within your SDACLite writing a time to a date field although saying

Code: Select all

TLiteSQL.param[i].ASDATE := Date
.
So maybe its on yours to have a detailed look or explanation.

best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Thu 02 Mar 2017 15:10

We checked LiteDAC 2.7.27 when using the following construction

Code: Select all

TLiteSQL.param[i].ASDATE := Date
, in case if the "Date" variable contains the date value with minutes and seconds. Unfortunately we could not reproduce the problem. Please compose and send us via the e-support form ( https://www.devart.com menu "Support"\"Request Support" ) a small sample in which the problem occurs.

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Mon 06 Mar 2017 21:44

I did send you a Demo on 3rd February and asked you on 10th if you did not recieve it. (look at this subject above).
So I send you again now in a few minutes.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Tue 07 Mar 2017 14:36

Thank you for the sample. We received it, but did not find the AsDate method implementation in it. Please change you application so that it uses this method the way you mentioned above.
When changing you code in the following way:

Code: Select all

   SL.ParamByName('DDate').AsDate := MSQuery.FieldByName('Lieferdatum').Value;
we get a correct result

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Tue 07 Mar 2017 15:29

But this is not the question.
If running through tables one field is a integer, another a date and so on.
So the way is to use Variant.
The method with insert and post works on LiteDAC and on any other database I use:

Code: Select all

LiteTable1.Insert;
for i := 0 to MSQuery.Fields.Count - 1 do
  LiteTable1.Fields[i].Value := MSQuery.Fields[i].Value;
LiteTable1.Post;
the corresponding method with params DOES NOT work in LiteDAC but DOES work in all other Databases I use:

Code: Select all

for i := 0 to SL.Params.Count - 1 do
  SL.Params[i].Value := MSQuery.Fields[i].Value;
SL.Execute;
So, if MSQuery.Fields is DATE the MSQuery.Fields.Value returns DATE-Format in BOTH because
if assigning this correct DATE-Format to a Field.Value in SDAC the DATE is passed correct.

Doing so with a Params, the PARAMs.value gets an additional Timestamp: 0:00:00.
This must be done somewhere within the LiteDAC Source when params Variants are transmitted to fields values and is a serious problem.

Params.Value must be able to read Variants and work with them as correctly and with the same result as Fields.Value do.

best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Fri 10 Mar 2017 12:12

The difference in behavior when you insert data through dataset fields or TLiteSQL parameters is due to the fact that SQLite does not have the built-in data type DATE. In SQLite, any data can be stored in a DATE type column, as you can easily see:

Code: Select all

create table test_table (field_date DATE);
insert into test_table (field_date) values ('abcd');
Therefore, processing DATE type data is carried out by LiteDAC itself on the basis of the information provided by SQLite.

When opening a dataset, LiteDAC can define a field type as DATE based on the table metadata (by the field type name) that are received from SQLite. Then, the data assigned to the field are converted to the DATE type, and, in this case, they are stored in the table as you need.

When working through parameters, LiteDAC cannot determine the parameter type based on the information provided by SQLite (as mentioned above, there is no DATE type in SQLite, unlike other DBMSs). Therefore, in your case (when assigning as LiteQuery.Params.Value:=...), the parameter type is set based on the data assigned to it. If the data is of the DATETIME type, then the parameter will also be of the DATETIME type, respectively, and the data will be saved to the table in this form.

Therefore, to achieve the result you need when assigning a value to a parameter, you should use one of the following methods:

Code: Select all

LiteQuery.Params.AsDate: = ...

LiteQuery.Params.DataType: = ftDate;
LiteQuery.Params.Value: = ...

LHSoft
Posts: 130
Joined: Sat 18 Aug 2012 08:33

Re: Master-Detail on Date Field

Post by LHSoft » Fri 10 Mar 2017 12:18

Ah, Thank you for the tip, I'll try at next occasion.
best regards
Hans

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Master-Detail on Date Field

Post by MaximG » Mon 13 Mar 2017 09:26

We are sure that you will be able to implement the required functionality in your project when working with the DATE type parameters. In turn, we will answer any questions related to the use of our products.

Post Reply