The UpdateSQL Component TUpdateSQL

We discussed cached updates earlier in the context of the CachedUpdates property published by the dataset components. When cached updates are enabled, data is retrieved from a local or remote table and stored in local memory All operations that occur against the data work against this local dataset. When the modifications are written to the remote dataset, they are written as a large block rather than individually. This strategy serves to reduce transaction times and minimize network traffic through the proximity of the local data.

There are also caveats involved in the use of cached data that the developer needs to be aware of when making the design decision to implement this strategy First, it must be remembered that since the user is working on a dataset that is local and viewed only by them, changes that they are making to their dataset are not seen by other concurrent users until they are committed. This issue also appears in reverse; the local dataset is not refreshed when other users update the remote source dataset, thereby rendering the local dataset obsolete. Additional consideration must be given to the committing of modifications in parent-child relationships due to referential integrity constraints.

Cached updates are enabled through the aforementioned property being set to True. When cached updates are enabled, a read-only query gathers enough data as needed to display and places these records into local memory. The local dataset is updated by periodic "fetches" from the remote dataset as needed by the user scrolling through the data. All updates are posted to the cached data until complete and then the entire cache of modifications is written back to the remote dataset in a single transaction.

There are some conditions in which the local data is not a live dataset that can be modified. A join of two or more tables, for example, results in a read-only dataset that can be modified on a local level but cannot be easily written back to the original relations. To perform this operation you must use an update object, the UpdateSQL component. As you can see from reviewing the property list in Figure 5.18, this is a simple component that belies its power.

Property Default Value Purpose

The SQL DELETE statement to use when applying a cached record deletion.

Property Default Value Purpose

figure 5.18 The TUpdateSQL component properties

UpdateSQL uses SQL statements to update the remote datasets. Its usage requires one instance of the control for each table involved in the original transaction.

The UpdateSQL component encapsulates three Query controls, one each for the purposes of insert, update, and delete operations. Each associated property contains the SQL statement used to process the appropriate query against the remote dataset. The SQL statements allowed through the UpdateSQL control support extended parameter binding and syntax. These properties are string lists and can be modified at design time or run time. To determine which of the three properties to use in the update process, the control relies on the UpdateKind parameter that is automatically generated for each record's update.

UpdateSQL and the process of updating cached data are very particular. Only the type of SQL statement intimated by the property name should be entered into that property, i.e., only a DELETE statement should be entered for the DeleteSQL property. Also, all of the statements in the properties for an instance of UpdateSQL must reference the same table, as the control is associated on a one-to-one basis with a DataSet object.

Building the statements for each of the properties is simplified through the tools that Delphi offers. As an example of this process, start a new application in Delphi and add a Query and an UpdateSQL component the form. Select the Query component and set the DatabaseName to

UpdateSQ!. Editor

SELECT * FROM EMPLOYEE

Formi .UpdateSQLI (Forml Queiyl I

Table Name:

Geljâble Fieids

Scbct Priraafji Keys ---

Generate.SQL' .

F Quote Field Names lütuimm

S"K

UpdateSQ!. Editor

Chapter S-Data Access with Delphi ■ 1531

IBLocall (be sure that the LIBS is running) and the Cached Updates] value to True. Set the SQL property to:

SELECT * FROM EMPLOYEE

¿llrf eiie (JpdateObjectj property to UpdateSQL 1| This last property links the datasetj object to the specific update object.

Right-click on the UpdateSQL control to invoke the UpdateSQL Editor. This tool builds the SQL statements for the properties encapsulated by the control. The editor, as shown in Figure 5.19, will have filled in the default values as determined by the associations made earlier.

Formi .UpdateSQLI (Forml Queiyl I

Table Name:

Geljâble Fieids

Scbct Priraafji Keys ---

Generate.SQL' .

ífiwhélfeíí-

Key Fields:

Update FteWs:

emp no

emp mo

first name

first name

last name

last name

phone ext

phone da

hire date

hire date

dept no

dept" no

job code

job code

job grade

job'grade

job country

job'countby

salãhy

salary

full name

full name

F Quote Field Names lütuimm

S"K

Carteei

Help

The options default on the side of including all fields and indexes invoked but you can pare them down to the desired selections. Click on the Generate SQL button to automate the creation of the SQL that will be used for the update process. This statement may not be complete but provides a good framework to start from. Clicking on OK saves the SQL statement to the property rhat was selected, the default statement! going to the ModifySQL. property This process is repeated for each of the types of operations that will be handled. The ensuing code is executed during the OnRecordUpdate| event.

+1 0

Post a comment