version 1 - December 97
The UpdateMulti component is similar to Delphi's UpdateSQL component, but it is able to update multiple tables.
The UpdateMulti component was created by Cogisoft, it is used since early 1996 to build applications internally as well as for clients.
If you know the UpdateSQL component, you will grasp the
UpdateMulti component easily.
The operation is the same, UpdateMulti only brings new fonctionnality.
TUpdateSQL, part of Delphi since version 2, let you provide queries to be executed when an update happens against a Query component. The three TUpdateSQL's main properties are DeleteSQL, InsertSQL et ModifySQL. Each properties contain the SQL query to be executed respectively when a deletion, an insertion and a modification happens.
One limitation of the UpdateSQL component becomes apparent
when one wants to update multiple tables: each property can contain only one SQL Query
(DELETE, INSERT or UPDATE).
This problem happens for example when the SELECT query of the Query component forms a join
between multiple tables.
The UpdateMulti component addresses this problem by accepting multiple queries in each SQL property. That way, during an insertion, it is now possible to insert in two tables.
TUpdateMulti can be used instead of the UpdateSQL component, therefore it is used with Cached Updates.
The UpdateMulti component adds two fonctions:
In the handler of these events you can define values for fields that are not filled by the user, typically values of record IDs in each table or values for control fields (eg. creation date, modification date, creator name, ...). You can also include validation rules in theses events, they could test the values of already-filled fields.
To modify or read field values, you can use the DeleteParams, InsertParams or ModifyParams properties.
Example: during an insertion on a query with join, you need to fill the Cust_No field of two tables with the same value. In the OnInsert event handler, you would assign a value to InsertParams.ParamByName('Cust_No').AsInteger, the Cust_No parameter being present in the two INSERT queries ( :Cust_No ).
The UpdateMulti component is used as the UpdateSQL component.
Here are the steps:
You need to modify the UpdateObject property of the DataSet to make it point to the UpdateMulti component.
This step consists of the initialization of the DeleteSQL, InsertSQL and ModifySQL properties with the ad hoc SQL queries.
One property can contain multiple disctint SQL orders in charge to update data in each concerned table. Orders are separated by a line containing only one character called "separator" ('!' by default). The separator is defined by the Separator property.
Once you have provided the SQL queries, the parameters appear in the DeleteParams, InsertParams and ModifyParams properties. You can specify the type of the parameters through these properties.
You can eventually put code in handlers of OnDelete, OnInsert and OnModify events.
Example:
InsertParams.ParamByName('Cust_No').AsInteger := NewID;
(NewID can come from a call to a stored procedure that generate a new ID.)
Most of the parameters use the values provided by the user or the values from the SELECT query.
All fields don't need to be filled programatically in the event handlers.
The UpdateMulti component gives you control before the modify queries are executed by using the OnDelete, OnInsert and OnModify events. There is no way to have control before execution of EACH query, nor after all queries are executed. The limitation is easy to remove by adding new events to the component.
Please if you find an improvement to make let us know so we can improve the component for everybody.
Supported versions of Delphi: 2 and 3.
Instal the file UMReg.pas in the DCL (Component | Install a component).
Install the component with the package dclUpdateMulti.dpk or with a different package.
In case you want to install the component in a different package, you need to add UMReg.pas and UpdateMulti.pas, the package requires Delphi 3\Lib\dclDB30.dcp
01 53 01 92 60
Copyright © 1998 Cogisoft - Site réalisé par Cogisoft
Dernière modification : 30 janvier 1998