Using Update Objects to Update a Dataset

When the BDE-enabled dataset represents a stored procedure or a query that is not "live", it is not possible to apply updates directly from the dataset. Such datasets may also cause a problem when you use a client dataset to cache updates. Whether you are using the BDE or a client dataset to cache updates, you can handle these problem datasets by using an update object.

To update a dataset

If you are using a client dataset, use an external provider component with TClientDataSet rather than TBDEClientDataSet. This is so you can set the UpdateObject property of the BDE-enabled source dataset (step 3).
Add a TUpdateSQL component to the same data module as the BDE-enabled dataset.
Set the BDE-enabled dataset component's UpdateObject property to the TUpdateSQL component in the data module.
Specify the SQL statements needed to perform updates using the update object's ModifySQL, InsertSQL, and DeleteSQL properties. You can use the Update SQL editor to help you compose these statements.
Close the dataset.
Set the dataset component's CachedUpdates property to True or link the dataset to the client dataset using a dataset provider.
Reopen the dataset.

Note: Sometimes, you need to use multiple update objects. For example, when updating a multi-table join or a stored procedure that represents data from multiple datasets, you must provide one TUpdateSQL object for each table you want to update. When using multiple update objects, you can't simply associate the update object with the dataset by setting the UpdateObject property. Instead, you must manually call the update object from an OnUpdateRecord event handler (when using the BDE to cache updates) or a BeforeUpdateRecord event handler (when using a client dataset).

The update object actually encapsulates three TQuery components. Each of these query components perform a single update task. One query component provides an SQL UPDATE statement for modifying existing records; a second query component provides an INSERT statement to add new records to a table; and a third component provides a DELETE statement to remove records from a table.

When you place an update component in a data module, you do not see the query components it encapsulates. They are created by the update component at runtime based on three update properties for which you supply SQL statements:

ModifySQL specifies the UPDATE statement.
InsertSQL specifies the INSERT statement.
DeleteSQL specifies the DELETE statement.

At runtime, when the update component is used to apply updates, it:

Selects an SQL statement to execute based on whether the current record is modified, inserted, or deleted.
Provides parameter values to the SQL statement.
Prepares and executes the SQL statement to perform the specified update.