Read-only TClientDataSet Refresh fails with key violation

Question

I get a key violation on the Refresh in the below code.

EmployeeContracts is a TClientDataSet coupled through a TDataSetProvider to a TFDQuery with SQL:

select ec.*
from tt_emp e, tt_emp_contract ec
where (coalesce(e.tt_nonactive,0)=0)
and e.tt_emp_id = ec.tt_emp_id

Code fragment:

with EmployeeContracts do
begin
  // Retrieve contracts of all active employees
  if (not Active) then
  begin
     Open;
  end;

  // Is record already correctly positioned?
  if (FieldByName(SEmpID).Asinteger=AEmpID) and
     (FieldByName(SFromDate).AsDateTime<=APeilDatum) and
     (FieldByName(SToDate).AsDateTime>=APeilDatum) then
  begin
     Result := True;
     Exit; 
  end;

  if not FindKey([AEmpID]) then  // Make sure the data are up to date. Refresh from the server.
  begin
     Refresh;  // ERROR HERE
  end;

  if FindKey([AEmpID]) then
  begin
     while (FieldByName(SempID).Asinteger=AEmpID) and (not EOF) do
     begin
        if (FieldByName(SFromDate).AsDateTime<=APeilDatum) and
           (FieldByName(SToDate).AsDateTime>=APeilDatum) then
        begin
           Result := True;
           Exit; 
        end;

        Next;
     end;
  end;
end;

• IndexFieldNames is tt_emp_id;tt_fromdate
• We have gone through the routine earlier, the clientdataset is open; no errors as long as the FindKey returns true
• FetchOnDemand=true, but toggling it makes no difference
• Delphi Tokyo Win32, FireBird 2.5.3, Dialect 3 database (a GDB file actually)
  ADDED 30-11-2017: I now also get this on a MSSQL database in the same app.
• If I trace the Delphi code, the error happens in the TCustomClientDataSet.InternalRefresh when calling FDSBase.AppendData at the end.

This code worked when we used SQLDirect as database access layer, but no longer with FireBird.

What can be the reason?

---

ADDED 1-12-2017 It has to do with the UpdateOptions.RequestLive property for the TFDConnection. If I switch its default true value to false, everything works OK.

This is all very strange. Why a default true for RequestLive?
(And why is its value not actually reflected in the DFM, but are the EnableDelete, EnableInsert, EnableUpdate toggled)?.

---

For someone who wants to reproduce, this is the full .pas source:
(It actually has a TDataSource and TDBGrid but those were only to show the data)

unit uClientDatasetRefresh;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf,
  FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys, FireDAC.Phys.FB,
  FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, Data.DB, Vcl.StdCtrls, Vcl.Grids,
  Vcl.DBGrids, Vcl.ExtCtrls, FireDAC.Comp.Client, FireDAC.Comp.DataSet,
  Datasnap.Provider, Datasnap.DBClient;

type
  TFrmClientDatasetRefresh = class(TForm)
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    FDQuery1: TFDQuery;
    FDConnection1: TFDConnection;
    Panel1: TPanel;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    function PositionOnEmployeeContractRecord(AEmpID: integer; ADate: TDateTime = 0): Boolean;
  public
  end;

var
  FrmClientDatasetRefresh: TFrmClientDatasetRefresh;

implementation

{$R *.dfm}

procedure TFrmClientDatasetRefresh.Button1Click(Sender: TObject);
begin
   PositionOnEmployeeContractRecord(20652);   // Has records in tt_emp_contract
   PositionOnEmployeeContractRecord(1024);    // Has no records in tt_emp_contract
end;

const
   SEmpID    = 'tt_emp_id';
   SFromDate = 'tt_fromdate';
   SToDate   = 'tt_todate';

function TFrmClientDatasetRefresh.PositionOnEmployeeContractRecord(AEmpID: integer; ADate: TDateTime = 0): Boolean;
begin
   Result := False;

   if (AEmpID=0) then Exit;
   if ADate=0 then ADate := Date;

   with ClientDataSet1 do
   begin
      if (not Active) then
      begin
         Open;
      end;

      if (FieldByName(SEmpID).Asinteger=AEmpID) and
         (FieldByName(SFromDate).AsDateTime<=ADate) and
         (FieldByName(SToDate).AsDateTime>=ADate) then
      begin
         Result := True;
         Exit;
      end;

      if not FindKey([AEmpID]) then
      begin
         Refresh;
      end;

      if FindKey([AEmpID]) then
      begin
         while (FieldByName(SempID).Asinteger=AEmpID) and (not EOF) do
         begin
            if (FieldByName(SFromDate).AsDateTime<=ADate) and
               (FieldByName(SToDate).AsDateTime>=ADate) then
            begin
               Result := True;
               Exit;
            end;

            Next;
         end;
      end;
   end;
end;

end.

This is the full .dfm source:

object FrmClientDatasetRefresh: TFrmClientDatasetRefresh
  Left = 0
  Top = 0
  Caption = 'ClientDataset Refresh'
  ClientHeight = 276
  ClientWidth = 560
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  Position = poScreenCenter
  PixelsPerInch = 96
  TextHeight = 13
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 560
    Height = 41
    Align = alTop
    BevelOuter = bvNone
    TabOrder = 0
    ExplicitLeft = 16
    ExplicitTop = 8
    ExplicitWidth = 185
    object Button1: TButton
      Left = 32
      Top = 8
      Width = 75
      Height = 25
      Caption = 'Test'
      TabOrder = 0
      OnClick = Button1Click
    end
  end
  object DBGrid1: TDBGrid
    Left = 0
    Top = 41
    Width = 560
    Height = 235
    Align = alClient
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    IndexFieldNames = 'tt_emp_id;tt_fromdate'
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 288
    Top = 8
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = FDQuery1
    Left = 376
    Top = 8
  end
  object FDQuery1: TFDQuery
    Connection = FDConnection1
    SQL.Strings = (
      'select ec.*'
      'from tt_emp e, tt_emp_contract ec'
      'where (coalesce(e.tt_nonactive,0)=0)'
      'and e.tt_emp_id = ec.tt_emp_id')
    Left = 448
    Top = 8
  end
  object FDConnection1: TFDConnection
    Params.Strings = (
      'DriverID=FB'
      'Database=*****.GDB'
      'Password=masterkey'
      'User_Name=SYSDBA')
    LoginPrompt = False
    Left = 528
    Top = 8
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 216
    Top = 8
  end
end

The table structure for tt_emp is easy, just two records with an integer tt_emp_id with values 20652, 1024
tt_emp_contract has some records for different tt_emp_id values, including 20652, excluding 1024. Structure:

TT_EMP_ID    Integer                  
TT_FROMDATE  DateTime                 
TT_TODATE    DateTime                 
TT_HOURS     Float      
... more

Index TT_I0_EMP_CONTRACT on TT_EMP_ID, TT_FROMDATE   Primary, Unique

Answer 1

This is what is happening:

1. Opening the TClientDataSet populates it using the TDataSetProvider.
2. The provider, in turn, opens the TFDQuery.
3. The TFDQuery has UpdateOptions.RequestLive set to true, which causes it to fetch its metadata, in particular the ProviderFlags of each TField.
4. FireDAC retrieves the unique identifying columns for the main (first) table in the select...from... statement, so fails to set tt_fromdate as part of the "identifying" key.
5. The client dataset then propagates this metadata (the "identifying" key) to its internal Midas back-end storage.
6. Later, when calling Refresh, the back-end storage rechecks the uniqueness of its stored records using this wrong key and raises a key violation exception.

Quote from online help:

TFDQuery, TFDTable, TFDMemTable, and TFDCommand automatically retrieve the unique identifying columns (mkPrimaryKeyFields) for the main (first) table in the SELECT ... FROM ... statements, when fiMeta is included in FetchOptions.Items.
...
The application may need to explicitly specify unique identifying columns, when FireDAC fails to determine them correctly.

---

Possible solutions:

• Set RequestLive to false in the TFDQuery component. The main purpose of setting it to true seems to be to enable FireDAC to automatically generate updating SQL commands, so if this is a read-only dataset, you can disable it (note that is also needed if you plan to call RefreshRecord).
• Change table order in from clause so tt_emp_contract is the first table, so its primary key is used.
• Create persistent fields for TFDQuery and set pfInKey in the ProviderFlags of the TField corresponding to tt_fromdate.
• Set TFDQuery UpdateOptions.KeyFields to tt_emp_id;tt_fromdate.

Any of them must do the job.