TFDQuery editing fails randomly

[Dmitry Onoshko 0]

I’m trying to make a tiny UI program to manage an SQLite database. The database basically consists of the main table `Items` and a few additional tables to which `Items` has foreign keys. The `Items` table has several thousands of records, gets loaded at program startup and is then displayed on the main form.

 

For various reasons the following decisions were made:

* The dataset displayed on the main form is along the lines of

SELECT `Items`.*, SUM(…)
FROM `Items`
  LEFT JOIN …
ORDER BY `Items`.`ID`

* The dataset is displayed in TAdvStringGrid in virtual mode (OnGetDisplText)

* The dataset is TFDQuery and it is used to handle the OnGetDisplText event by setting dataset’s RecNo and returning Fields[ACol].AsString.

 

Everything goes fine until I try to edit an item. ItemEditor form uses plain controls (due to lack of certain complex features in DB-aware controls). When “Apply” button is clicked, the code executed is like this:

with Dataset do // Dataset.RecNo is set to the currently edited record here
begin
  Edit;
  FieldByName('Field1').AsString := …;
  FieldByName('Field2').AsDate := …;
  Post;
end;

Now I see strange behaviour. Sometimes the data in the dataset gets updated, sometimes not. FDMonitor shows that sometimes an UPDATE query (autogenerated, but valid for the changes made) gets executed, sometimes not.

 

I tried to set CachedUpdates to False explicitly immediately after creating a TFDQuery but it seems to make no difference. If that matters, the FireDAC units are only used in DataModule, form units get it as TDataset (but I guess virtual methods should still do the job, right?).

 

1) Is using TFDQuery as a local copy of the table data even viable?

2) What could cause the strange update behaviour?

 

Any advice on how to implement such a simple CRUD program (with additional requirements not easily achievable with DB-aware controls) is also appreciated.

 

UPD1: after removing all the debugging code I used looks like only the first update on the TFDQuery is actually done, the rest are just ignored. If that helps.

UPD2: well, maybe not the first, but whenever the item being edited is not the one that was edited before (and not the first one for the first edit).

UPD3: sorry, wrong information, the generated UPDATE statement is wrong, OLD_ID is always 30 for some reason instead of the real value.

Edited 22 hours ago by Dmitry Onoshko

[weirdo12 24]

Are you calling the TFDQuery.ApplyUpdates() method somewhere after you call Post?

 

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

Edited 15 hours ago by weirdo12