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.InternalRefreshwhen callingFDSBase.AppendDataat 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