Delphi FireDAC: how to refresh data in cache

user_1697197 0
Viewed 709

i need to refresh data in a TFDQuery which is in cached updates. to simplify my problem, let's suppose my MsACCESS database is composed of 2 tables that i have to join.

LABTEST(id_test, dat_test, id_client, sample_typ)
SAMPLEType(id, SampleName)

in the Delphi application, i am using TFDConnection and 1 TFDQuery (in cached updates) in which i join the 2 tables which script is:

"SELECT T.id_test, T.dat_test, T.id_client, T.sample_typ, S.SampleName
FROM   LABTEST  T
left JOIN    SAMPLEType  S ON  T.sample_typ = S.id"

in my application, i also use a DBGrid to show the result of the query. and a button to edit the field "sample_typ", like this:

qr.Edit;
qr.FieldByName('sample_typ').AsString:=ce2.text;
qr.Post;

the edition of the 'sample_typ' field works fine but the corresponding 'sampleName' field is not changing (in the grid) after an update. in fact it is not refreshed ! the problem is here: if i do refresh of the query, an exception is raised: "cannot refresh dataset. cached updates must be commited or canceled and batch mode terminated before refreshing"

if i commit the updates, data will be sent to database and i don't want that, i need to keep the data in cache till the end of the operation. also if i get out of the cache, data will be refreshed in the grid but will be sent to the database after qr.post and i don't want that.

i need to refresh data in the cache. what is the solution ?

Thanks in advance.

firedac refresh caching delphi
1 Answers

The issue comes down to the fact that you haven't told your UI that there is any dependency on the two fields - it clearly can't know how to do the join itself without resubmitting it so if you don't want to send the updates and reload you will have a problem.

It's not clear exactly what you are trying to do, but these two ideas may help you.

If you are not going to edit the fields in the SAMPLEType tables (S) then load the values from that table into a lookup table. You can load this into a TFDMemTable. You can use an adapter which loads from a query. Your UI controls can then show the value based on the valus looked up in your local TFDMemTable. Dependiong on the UI control this might be a 'LookupField' or some such.

You may also be able to store your main data in a TFDMemTable with an Adapter - you can specify diferent TFDCommands to read the whole recordset, refresh a record, update, insert and delete a record. The TFDCommands can act on multiple tables for joined recordsets like this. That would automatically refresh the individual record for you when you post it.

user_108790610
Related