Title: Use your own SQL to update table in MIDAS
Question: How to use my own SQL to update table in MIDAS?
Answer:
In two-tier application, I used TUpdateSQL to update table, when I want use my own SQL to update table. It's very easy to finish it.
In three-tier application, Delphi 4 and 5 has no component such as TProviderUpdateSQL to accomplish this function. How to use my own SQL to update table in MIDAS?
To do this, I write a procedure called SetParams to bind params from ClientDataSet to TUpdateSQL, this mean you can still use your TUpdateSQL.
The step is:
1 Drop down a TUpdateSQL in your remote data module, set the Query's UpdateObject property to it. Double click the TUpdateSQL component, the component editor will help you to generate SQL.
2 After set the property SQL, hook the BeforeUpdateRecord event of the TDataSetProvider(or TProvider in Delphi 4) which associate with your Query. Write this code:
SetParams(UpdateSQL1, DeltaDS, UpdateKind);
UpdateSQL1. ExecSQL(UpdateKind);
Applied := True;
That is OK. You can use SQL Monitor to watch it work finely. In 'Delphi 5 Developer's Guide', Dan Miser have written a component that do similar work of mine. And his SetParams procedure and me do same work.
Good luck.
Here is the source code of SetParams:
procedure SetParams(AUpdateSQL: TUpdateSQL; ADeltaDS:
TClientDataSet;AUpdateKind: TUpdateKind);
var
I: Integer;
Old: Boolean;
Param: TParam;
PName: string;
Field: TField;
Value: Variant;
begin
if not Assigned(AUpdateSQL.DataSet) then
Exit;
with AUpdateSQL.Query[AUpdateKind] do
for I := 0 to Params.Count - 1 do begin
Param := Params[I];
PName := Param.Name;
Old := CompareText(Copy(PName, 1, 4), 'OLD_') = 0;
if Old then
System.Delete(PName, 1, 4);
Field := ADeltaDS.FindField(PName);
if not Assigned(Field) then
Continue;
if Old then
Param.AssignFieldValue(Field, Field.OldValue)
else begin
Value := Field.NewValue;
if VarIsEmpty(Value) then
Value := Field.OldValue;
Param.AssignFieldValue(Field, Value);
end
end
end;