delphi159

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.

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

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.

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".

My final aim is this sql-code: select G.Goods, G.Goods_id, coalesce(dt2.incomes,0)as incomes, coalesce(dt2.sales,0) as sales, coalesce(dt2.writeoffs,0) as writeoffs, coalesce(dt2.endqnt,0) as endqnts From Goods G Join ( select Goods_id, coalesce(sum(inc),0) as incomes, coalesce(sum(sale),0) as sales, coalesce(sum(writeoff),0) as writeoffs, sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts from( select i.goods_id, sum(i.qty) as inc, cast(0 as float) as sale, cast(0 as float) as writeoff from income i where cast(i.recdate as date) <= :d group by i.goods_id union all select s.goods_id, cast(0 as float), sum(s.qty) as sale, cast(0 as float) from sales s where cast(s.recdate as date) <= :d group by s.goods_id union all select w.goods_id, cast(0 as float), cast(0 as float), sum(w.Qty) as writeoff, from writeoff w where cast(i.recdate as date) <= :d group by w.goods_id) dt1 group by Goods_id) dt2 on P.Goods_id=dt2.Goods_id order by Goods but this dt2+dt1 derived tables code How to correct this code for Interbase 2020 server?

Dany Marmur, unfortunatelley a new error appeared: 'Expression Evalution Not Supported' on IB 2020.