Introduction

---

Using cached updates for table and query result sets is accomplished through the BeginCachedUpdates, and ApplyCachedUpdates, and CancelCachedUpdates methods of the TDBISAMTable and TDBISAMQuery components. The properties used by these methods include the CachingUpdates property. Using cached updates permits an application to copy all existing records in a given table or query result set to a temporary table that is then used for any inserts, updates, or deletes. Once all updates are complete, the application may then call the ApplyCachedUpdates method to apply all updates to the source table or query result set, or the CancelCachedUpdates method to cancel all updates and revert the table or query result set to its original state prior to the cached updates. The records that are included in the cached updates can be controlled by setting a range or filter on the source table or query result set prior to calling the BeginCachedUpdates method. Please see the Setting Ranges on Tables and Setting Filters on Tables and Query Result Sets topics for more information.


Beginning Cached Updates

---

To begin cached updates, you must call the BeginCachedUpdates method. When using either a TDBISAMTable or TDBISAMQuery component, the table or query result set must be opened (Active property is set to True) or an exception will be raised.

Applying Cached Updates

---

To apply any cached updates to the source table or query result set, you must call the ApplyCachedUpdates method. This method will apply any updates that were made to the temporary table used for the cached updates to the source table or query result set. Only records that were inserted, updated, or deleted are processed, so the result is the same as calling the CancelCachedUpdates method if no records were inserted, updated, or deleted while cached updates were enabled. You can examine the CachingUpdates property to determine whether cached udpdates are in effect before trying to apply any cached updates.

It is strongly recommend that you always wrap the ApplyCachedUpdates method with a TDBISAMDatabase StartTransaction and Commit and Rollback block of code. This will allow the application of the cached updates to behave as an atomic unit of work and will avoid any possible problems of partial updates due to errors during the application of the updates.

The following example shows how to propery apply cached updates using a transaction around the ApplyCachedUpdates method:

var
   TablesList: TStrings;
begin
   TablesList:=TStringList.Create;
   try
      with MyTable do
         begin
         TablesList.Add(TableName);
         Database.StartTransaction(TablesList);
         try
            ApplyCachedUpdates;        
            Database.Commit;
         except
            Database.Rollback;
            raise;
         end;
   finally
      TablesList.Free;
   end;
end;

Reconciling Errors

---

Cached updates are handled in an optimistic manner, which means that DBISAM does not hold any locks on the records that are held in the cache while the cached updates are in effect. Subsequently, it is possible that another session has changed some or all of the records that were cached and updated or deleted in the cache. When the cached updates are then applied using the ApplyCachedUpdates method, an error message will be raised and it is possible that only a portion of the cached updates will be applied to the source table or query result set. To avoid this, you can assign an event handler to the OnCachedUpdateError event. This will cause DBISAM to instead call this event handler when an error occurs during the application of the cached updates, giving the user an opportunity to correct any errors and retry any update that is causing an error.


The following is an example of an OnCachedUpdateError event handler that retries the current record application if a record lock error is causing the problem:

procedure TMyForm.MyTableCachedUpdateError(Sender: TObject;
  CurrentRecord: TDBISAMRecord; E: Exception;
  UpdateType: TUpdateType; var Action: TUpdateAction);
begin
   Action:=uaFail;
   if (E is EDBISAMEngineError) then
      begin
      if (EDBISAMEngineError(E).ErrorCode=DBISAM_RECLOCKFAILED) then
         Action:=uaRetry;
      end;
end;

Of course, there are many responses that can be made in this event handler depending upon the actual error code and any input that the user may be able to provide. The TDBISAMRecord object passed in contains both the current values and the old values of the record being applied, which allows you to prompt the user for an answer to a possible issue with a key violation, locking issue, or a record being modified by another user since it was last cached. In some cases, like duplicate key violations, it is possible to modify the current values so that the record can still be inserted, updated, or deleted.

Filters, Ranges, and Master-Detail Links

---

Most of the operations that can be performed on a TDBISAMTable or TDBISAMQuery component behave the same regardless of whether cached updates are active or not. This includes the following operations:

Navigating Tables and Query Result Sets
Searching and Sorting Tables and Query Result Sets
Updating Tables and Query Result Sets

However, certain states of the table or query result set are not carried over to the cached updates temporary table. These include:

Filters
Ranges
Master-Detail Links

All of these states are reset for the cached updates temporary table. You may apply new filters, ranges, and/or master-detail links on the cached updates temporary table if you wish, but they will not apply to the base table nor will they affect the base table's state with respect to filters, ranges, or master-detail links. After the cached updates are applied or cancelled, all of these states are set back to what they were prior to the cached updates being active.

Refreshing During Cached Updates

---

If you call the TDBISAMTable or TDBISAMQuery Refresh method while cached updates are active, then the current contents of the cached updates temporary table will be discarded and replaced with the latest data from the base table. Cached updates will remain in effect after the Refresh is complete.