Clientdataset's delta becomes empty

[delphi159 0]

I have "briefcase model application". Clientdataset reads data from xml-file. After closing modified data are saved in the xml-file. When click on "applyUpdate" button, data are saved in firebird db.

If I don't exit program then ApplyUpdate works properly and commits delta-packet in db but sometimes, after closing-opening program 3- 4-5 times, for no clear reason delta becomes empty and after that if I click on "apply" button ApplyUpdates(-1)  operator causes exception "access violation".

Delphi 10.4, update 2. Firebird3.

Project and db files on google drive:  https://drive.google.com/drive/folders/1UvtGmaWLdmmSYQITWpEvp-BwRNmw9VEL?usp=sharing

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, 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,
  Datasnap.DBClient, FireDAC.Comp.DataSet, FireDAC.Comp.Client,
  Datasnap.Provider, Vcl.StdCtrls, Vcl.Buttons, Vcl.ComCtrls, Vcl.Grids,
  Vcl.DBGrids, FireDAC.UI.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool,
  FireDAC.Phys, FireDAC.Phys.FB, FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, MidasLib;

type
  TForm1 = class(TForm)
    LabelStatus: TLabel;
    LabelChangeCount: TLabel;
    DBGrid: TDBGrid;
    bApply: TBitBtn;
    DBGrid2: TDBGrid;
    bClose: TButton;
    bOpen: TButton;
    bMerge: TBitBtn;
    DataSource1: TDataSource;
    DataSetProvider1: TDataSetProvider;
    qGoods: TFDQuery;
    cdsGoods: TClientDataSet;
    DataSource2: TDataSource;
    FDConnection1: TFDConnection;
    FDTransaction1: TFDTransaction;
    qGoodsGOODS_ID: TIntegerField;
    qGoodsGOOD: TWideStringField;
    qGoodsQNT: TWideStringField;
    procedure bApplyClick(Sender: TObject);
    procedure bCloseClick(Sender: TObject);
    procedure bOpenClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cdsGoodsAfterPost(DataSet: TDataSet);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  FN: TFileName;
  MySavePoint: integer;
  Modified: boolean;


implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
 qGoods.Open;

 FN:= ExtractFilePath(ParamStr(0)) + 'cds.xml';
 cdsGoods.filename:= FN;
 cdsGoods.Close;
 cdsGoods.open;
 MySavePoint:= cdsGoods.SavePoint;
end;


procedure TForm1.bApplyClick(Sender: TObject);
begin
 cdsGoods.ApplyUpdates(-1);
 qGoods.Refresh;
 MySavePoint:=cdsGoods.SavePoint;
 Modified:= false;
end;


procedure TForm1.bCloseClick(Sender: TObject);
begin
 cdsGoods.Close;
end;

procedure TForm1.bOpenClick(Sender: TObject);
begin
 cdsGoods.Open;
end;

procedure TForm1.cdsGoodsAfterPost(DataSet: TDataSet);
begin
 Modified:= true;
end;

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
 case cdsGoods.UpdateStatus of
     usUnmodified: labelStatus.Caption:='record didnt change';
     usModified:   labelStatus.Caption:='record was modified';
     usInserted:   labelStatus.Caption:='record was inserted';
     usDeleted:    labelStatus.Caption:='record was deleted';
    end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 qGoods.close;
 cdsGoods.Close;
end;

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
 if Modified then  case application.MessageBox(PwideChar('Data were changed. Save changes?'), '', MB_YESNOCANCEL+MB_ICONQUESTION) of
                     IDCancel: CanClose:=false;
                     IDNo:  cdsGoods.SavePoint:=MySavePoint;
                     // IDYES- no needed. Cds is saved auomatically after close
// cdsGoods.saveToFile(FN); 
                   end;

end;

   
end.

Edited July 14, 2022 by delphi159

[delphi159 0]

Update: 
 After closing-opening program 2-3 times, for no clear reason when I open program delta becomes unassigned (nil) as breakpoint shows. Therefore, after that calling of applyupdates() or mentioning of clientdataset.delta causes exception "access violation". 

Edited July 16, 2022 by delphi159

[Brian Evans 83]

I would suggest switching to using cached updates on the TFDQuery instead of using ClientDataSet.

https://docwiki.embarcadero.com/RADStudio/Sydney/en/Caching_Updates_(FireDAC)

 

 

[delphi159 0]

 

Brian Evans, thanks for you reply.  Unfortunately, you are right. 

Despite many attempts, it still doesn't work. It's very sad. What may be the reason if in the case of a remote server (internet hosting) in datasnap project it works perfectly and sends data packets containing thousands of records? 

I have already changed it with fdQuery+fdTableAdapter+fdCommand and it works fine. 

 

Edited July 17, 2022 by delphi159

[Brian Evans 83]

I don't use Firebird so was not able to debug your project and reproduce the problem. Some guesses like qGoods being OnDemand and 50 record fetch size vs loading all but without being able to debug they are shots in the dark. 

 

 

[delphi159 0]

Update2:
Strange case: If I create clientdataset using fielddefs then it works without error and applies changes in DB but when I assign local dataset then after 3-4 close-opening of program delta-packet becomes unassigned(nil).  
Instead  of firebird  db I created MSSQL db but  nothing changed, the same error.
After that I make these changes:
fdQuery's fetch options: mode  fmOnDemand change with fmAll,  RowSetSize 50 with 500,
clientdataset's option  fetchOnDemand true with false
DataSetProvider's option AllowMultiSelectUpdate false with true but nothing has changed. 

Sql for firebird dataset is very simple:

CREATE TABLE GOODS (
    GOODS_ID  INTEGER NOT NULL,
    GOOD      VARCHAR(50),
    QNT       VARCHAR(1) DEFAULT '0' NOT NULL
); 

Sql for MS Sql server: 
 

CREATE TABLE [dbo].[Goods](
    [Goods_id] [int] IDENTITY(1,1) NOT NULL,
    [Good] [varchar](50) NOT NULL,
    [Qnt] [smallint] NOT NULL,
 CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED 
(
    [Goods_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

Edited July 17, 2022 by delphi159