AnyDAC supports deferred updates posting, so called Cached Updates.
The Cached Updates mode allows to defer posting updates to a database to a later time, than Post / Delete methods are called. That allows to post multiple updates in a single batch, optionally enclosed into a transaction.
To set a dataset to the Cached Updates mode set CachedUpdates property to True. Then the dataset will track all changes made after last setting to True or CancelUpdates / CommitUpdates calls. Those changes made up the change journal, where all changes are ordered by the time when a change was made. AnyDAC does not track multiple versions of the same record. The last change overrides previous one and moves the record to the end of the change order.
AnyDAC supports Decentralized and Centralized Cached Updates modes:
When application needs to log and apply multiple dataset changes in a chronological order, then Centralized Cached Updates may be used. For that a single TADSchemaAdapter instance must be assigned to few datasets SchemaAdapter property. There TADSchemaAdapter serves as a central storage for the rows and their changes for few datasets.
At first, Centralized Cached Updates is useful in master-detail relationships, where a master dataset propagates changes to the cascading detail datasets, including auto-incrementing field values. To enable propagation for a detail dataset it FetchOptions.DetailCascade must be set to True. This allows:
Propagation works when the following conditions are met:
In the same time this enables an in-memory referential constraint for a detail dataset. This is similar to a SQL command:
ALTER TABLE <detail> ADD CONSTRAINT FOREIGN KEY (<detail fields>) REFERENCES <master> (<master fields>) ON DELETE CASCADE ON UPDATE CASCADE
To apply updates the application should use TADSchemaAdapter.ApplyUpdates method instead of dataset ApplyUpdates. To reconcile errors use TADSchemaAdapter.Reconcile method instead of dataset Reconcile.
See AnyDAC\Samples\Comp Layer\TADQuery\CachedUpdates\Centralized demo application for more details.
When your application is working in cached updates mode, you can track the changes and optionally revert the changes for each dataset. To track changes, use the properties:
To revert existing changes, use the properties and methods:
For example, to implement simple Undo functionality, you may create an actUndo action and attach the following event handlers:
procedure TForm1.actUndoUpdate(Sender: TObject); begin actUndo.Enabled := ADQuery1.UpdatesPending; end; procedure TForm1.actUndoExecute(Sender: TObject); begin ADQuery1.UndoLastChange(True); end;
For other example, to implement in-memory transaction, with ability to undo group of changes, you may do:
ADQuery1.CachedUpdates := True; iSavePoint := ADQuery1.SavePoint; try ADQuery1.Append; ... ADQuery1.Post; ADQuery1.Append; ... ADQuery1.Post; ADQuery1.Append; ... ADQuery1.Post; except ADQuery.SavePoint := iSavePoint; end;
Note, in cached updates mode the following methods and properties are working with updates journal:
Note, in cached updates mode some methods or property settings will raise an exception, if there are changes in updates journal. They must be saved then commited or canceled. Those are:
To apply updates to a database use the ApplyUpdates method. If some record applying raised an exception, then it will be associated with the record. Note, that ApplyUpdates method:
After applying updates the changed records still remains in the changes log. To remove them from the changes log and mark as unmodified call the CommitUpdates method. For example:
ADQuery1.CachedUpdates := True; ADQuery1.Append; ... ADQuery1.Post; ADQuery1.Append; ... ADQuery1.Post; ADQuery1.Append; ... ADQuery1.Post; ADConnection1.StartTransaction; iErrors := ADQuery1.ApplyUpdates; if iErrors = 0 then begin ADQuery1.CommitUpdates; ADConnection1.Commit; end else ADConnection1.Rollback;
If an error happens inside of ApplyUpdates call, then ApplyUpdates records the error into the internal data record structure. And continues to process updates until the errors number will be equal or greater than AMaxErrors. ApplyUpdates does not raise exceptions. To process all erroneous records after ApplyUpdates call, use either reconciling process, either filter erroneous records.
To reconcile records assign OnReconcileError event handler and call the Reconcile method. The OnReconcileError event handler allows to analyze error, read / change the current record field values. On exit it should assign action, which AnyDAC code should take on the current record with the error. After the Reconcile method call the ApplyUpdates may be called again to try to post again erroneous record changes.
To filter erroneous records include rtHasErrors into FilterChanges. Then navigate through dataset and read RowError property to get an exception object associated with the current record. For example:
var oErr: EADException; ... if ADQuery1.ApplyUpdate > 0 then begin ADQuery1.FilterChanges := [rtModified, rtInserted, rtDeleted, rtHasErrors]; try ADQuery1.First; while not ADQuery1.Eof do begin oErr := ADQuery1.RowError; if oErr <> nil then begin // process exception object ... end; ADQuery1.Next; end; finally ADQuery1.FilterChanges := [rtUnmodified, rtModified, rtInserted]; end; end;
See the demos for more details:
- AnyDAC\Samples\Comp Layer\TADMemTable\CachedUpdates
- AnyDAC\Samples\Comp Layer\TADQuery\CachedUpdates\OnUpdateRecord
- AnyDAC\Samples\Comp Layer\TADQuery\CachedUpdates\Centralized
What do you think about this topic? Send feedback!