{ DISQLite3 example project to demonstrate how to use TDateTime values with
  TDISQLite3UniDirQuery and DB controls. Things to keep in mind:

  * Declare all Delphi TDateTime, TDate, and TTime fields as 'REAL' in the SQL
    CREATE TABLE statement.

  * Add a TDISQLite3UniDirQuery.OnInitFieldDef event to change the corresponging
    columns' datatypes to ftDateTime, ftDate, and ftTime respectively (see
    DISQLite3UniDirQuery_InitFieldDef below).

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

unit DISQLite3_DateTime_Form;

{$I DICompilers.inc}

interface

uses
  DISystemCompat, Classes, Controls, Forms, StdCtrls, ExtCtrls, Mask, Grids,
  DB, Provider, DBClient, DBCtrls, DBGrids,
  DISQLite3Database, DISQLite3DataSet;

type
  TfrmDateTime = class(TForm)
    DataSource: TDataSource;
    DataSetProvider: TDataSetProvider;
    ClientDataSet: TClientDataSet;
    DISQLite3Database: TDISQLite3Database;
    DISQLite3UniDirQuery: TDISQLite3UniDirQuery;
    DBGrid1: TDBGrid;
    edtName: TDBEdit;
    edtDate: TDBEdit;
    lblName: TLabel;
    lblBirthday: TLabel;
    gbxUpdates: TGroupBox;
    btnApply: TButton;
    btnCancel: TButton;
    lblStatus: TLabel;
    DBNavigator: TDBNavigator;
    procedure FormCreate(Sender: TObject);
    procedure ClientDataSet_Modified(DataSet: TDataSet);
    procedure btnApplyClick(Sender: TObject);
    procedure btnCancelClick(Sender: TObject);
    { Important! }
    procedure ClientDataSetNewRecord(
      DataSet: TDataSet);
    { Important! }
    procedure DISQLite3UniDirQuery_InitFieldDef(
      const AColumn: TDISQLite3Column;
      const AFieldDef: TFieldDef);
  private
    FIndex: Integer;
  end;

var
  frmDateTime: TfrmDateTime;

const
  APP_TITLE = 'DISQLite3 DateTime';

implementation

uses
  SysUtils,
  { Compile MidasLib into the application if supported by Delphi version.
    This removes all dependencies against midas.dll and turns projects into
    fully standalone applications. }
  {$IFDEF COMPILER_6_UP}MidasLib, {$ENDIF}
  DISQLite3Api;

{$R *.dfm}

procedure TfrmDateTime.FormCreate(Sender: TObject);
begin
  Caption := APP_TITLE;

  try
    DISQLite3Database.Open;
  except
    on e: ESQLite3 do
      if e.ErrorCode = SQLITE_CANTOPEN then
        begin
          DISQLite3Database.CreateDatabase;

          { Create table ... }
          DISQLite3Database.Execute(
            'CREATE TABLE Birthdays(' +
            ' ID INTEGER PRIMARY KEY,' +
            ' Name TEXT,' +
            ' Birthday REAL' + // This MUST be REAL!
            ');'
            );

          { ... and fill with birthdays of a few early computer pionieers. }
          DISQLite3Database.Execute(
            'INSERT INTO Birthdays (Name, Birthday) VALUES (''Konrad Zuse'', julianday(''1910-06-22''));' +
            'INSERT INTO Birthdays (Name, Birthday) VALUES (''Alan Turing'', julianday(''1912-06-23''));' +
            'INSERT INTO Birthdays (Name, Birthday) VALUES (''John Backus'', julianday(''1924-12-03''));' +
            'INSERT INTO Birthdays (Name, Birthday) VALUES (''Donald Knuth'', julianday(''1938-01-10''));');
        end
      else
        raise
    else
      raise;
  end;

  DISQLite3UniDirQuery.SelectSQL :=
    'select * from birthdays';

  ClientDataSet.Open;
end;

//------------------------------------------------------------------------------

{ IMPORTANT: Initialize the BirthDay field as ftDate. TDISQLite3UniDirQuery
  automatically converts those dates to and from TDataTime to JulianDay. }
procedure TfrmDateTime.DISQLite3UniDirQuery_InitFieldDef(
  const AColumn: TDISQLite3Column;
  const AFieldDef: TFieldDef);
begin
  if AColumn.ColumnName = 'Birthday' then
    AFieldDef.DataType := ftDate;
end;

//------------------------------------------------------------------------------

{ IMPORTANT: Initialize a new TClientDataSet ftAutoInc record with a unique
  negative ID to avoid primary key violations. TDISQLite3UniDirQuery by default
  does not send ftAutoInc values to the database engine so DISQLite3 can fill
  these values with unique positive INTEGER keys when it writes the recrod to
  disk. }
procedure TfrmDateTime.ClientDataSetNewRecord(DataSet: TDataSet);
var
  b: Boolean;
  i: Integer;
  f: TField;
begin
  for i := 0 to DataSet.Fields.Count - 1 do
    begin
      f := DataSet.Fields[i];
      if f.DataType = ftAutoInc then
        begin
          { Set ftAutoInc fields to unique negative values
            to avoid CDS unique key violations. }
          b := f.ReadOnly;
          f.ReadOnly := False;
          Dec(FIndex); // Integer field.
          f.AsInteger := FIndex;
          f.ReadOnly := b;
        end;
    end;
end;

//------------------------------------------------------------------------------

{ Update the status when a TClientDataSet record changes. }
procedure TfrmDateTime.ClientDataSet_Modified(DataSet: TDataSet);
begin
  if ClientDataSet.ChangeCount > 0 then
    lblStatus.Caption := IntToStr(ClientDataSet.ChangeCount) + ' Update(s) pending'
  else
    lblStatus.Caption := ''
end;

//------------------------------------------------------------------------------

{ Apply TClientDataSet changes to the database. }
procedure TfrmDateTime.btnApplyClick(Sender: TObject);
var
  i: Integer;
begin
  i := ClientDataSet.ApplyUpdates(-1);
  if i > 0 then
    lblStatus.Caption := IntToStr(i) + ' Error(s) during update'
  else
    lblStatus.Caption := 'Update(s) OK';
end;

//------------------------------------------------------------------------------

{ Cancel any changes to the TClientDataSet. }
procedure TfrmDateTime.btnCancelClick(Sender: TObject);
begin
  ClientDataSet.CancelUpdates;
  lblStatus.Caption := '';
end;

end.

