Among other amazing things around Delphi is a litle object called “TParam” which is derived from TCollectionItem and it's associated collection object “TParams”.
Both are declared in DB.pas unit and their short description in delphi help says:
TParam: "TParam represents a field parameter for a query or stored procedure."
TParams: "TParams manages a list of field parameters."
That's all, the name as per this help specifies it's usage. So what is the amazing about TParam and TParams?
Or it is not that's all...?
Let's forget the association of the name with the usage and the usage itself and do some brainstorming. If you dig a bit in DB.pas unit you will discover that:
- TParams collection is not by any means depended by any object such as a query or a stored procedure
- Further looking at it's constructor we discover that it can be instantiated as an independed object
- There is also a constructor that needs no owner
- TParam can hold runtime data for almost all Delphi data types
- Data in TParam can be accessed by name much like using a TDataset fields. Just take a look at TParams.FindParam, ParamByName, ParamValues and TParam.AsXXX properties …
- And of course TParams can hold a collection of TParam items much like a single dataset record... ;)
Let's consider some possible scenarios that i faced many times in my life as a developer programming in Delphi and long ago in COBOL!
In real world data applications there are many times that you need to keep a single database record field values in a temp storage for later use. Or perform coded calculations in a set of database records, where you need to hold and access the calculated values at any step during the code execution line. Or perform various computations and/or validations on a set of mutually related records simultaneously...
TParam and TParams collection can do a lot for you in such situations without the need of hard coding individual variables or complex arrays and structures. Another possible usage can be for storing and accessing application parameters, which as you will see in next article, it is very interesting simple when comes to persistence...
The possibilities are endless as i have discovered through the years working with this small but amazing piece of Delphi and i would like to share this experience via this series of articles.
A Note: Of course kind of same functionality could be achieved via TClientDataset, but there are differences, and not to mention the Midas lib overhead. After all TClientDataset is another Delphi's success story by a different point of view and usability and i hope to create a new article series in the future about how i utilize it. And believe me there are numerous ways too!
So, let the journey commence...
The journey starts from the simplest alternate way of using TParam and TParams and somewhere at the end of this article series will be addressing object oriented database manipulation...!
First of all let's see how can we use (the simplest form of using) TParams as a placeholder for the field values of a single database record. Field definitions from a fields collection is used to create or update TParam items in a TParams collection:
procedure FieldsToParams(Fields: TFields; Params: TParams; doAdd: Boolean=False);
var i: integer;
begin
if not doAdd then Params.Clear;
for i:=0 to Fields.Count-1 do
if not (Fields[i].DataType in ObjectFieldTypes) then
Params.CreateParam(Fields[i].DataType, Fields[i].FieldName, ptUnknown);
end;
The second function is devoted to COBOL's powerfull statement MOVECORR and as it's name suggest assigns (moves) Param values from a TParams collection to their corresponding by name fields in a given Dataset, updating the current record.
function MoveCorr(Params: TParams; Dataset: TDataset): integer;
var i: integer;
p: TParam;
begin
Result := 0;
with Dataset do
begin
for i := 0 to FieldCount -1 do
if (Fields[i].FieldKind = fkData) and (not Fields[i].ReadOnly) then
begin
p := Params.FindParam(Fields[i].FieldName);
if Assigned(p) then //fieldparam may not exists!
if p.Bound then
{check for equal fields - problem on ado master/detail when a masterfield changes the detail is requeryed!}
if not VarEquals(Fields[i].Value, p.Value) then
begin
if VarIsEmpty(p.Value) or VarIsNull(p.Value) then
Fields[i].Clear
else
Fields[i].Value := p.Value;
Inc(Result);
end;
end;
end;
end;
In order for "MoveCorr" to work you will need the following function "VarEquals" that checks if two variants are equal and can handle variant arrays also. Of course you can modify everything as your needs.
{Bonus – Helper function that checks if two variants are equal}
function VarEquals(const V1, V2: Variant; CheckAsString: Boolean): Boolean;
var k,i1,i2,j1,j2: integer;
begin
Result := False;
if VarIsArray(V1) or VarIsArray(V2) then
begin
if not (VarIsArray(V1) and VarIsArray(V2)) then Exit;
i1:=VarArrayLowBound(V1,1); j1:=VarArrayHighBound(V1,1);
i2:=VarArrayLowBound(V2,1); j2:=VarArrayHighBound(V2,1);
if (j1-i1) <> (j2-i2) then Exit;
k := j1-i1;
while k >=0 do begin
if not VarEquals(V1[i1+k], V2[i2+k]) then Exit;
Dec(k);
end;
Result := True;
end
else
if VarIsNull(v1) then Result := VarIsNull(v2)
else
if VarIsNull(v2) then Result := VarIsNull(v1)
else
if VarIsEmpty(v1) then Result := VarIsEmpty(v2)
else
if VarIsEmpty(v2) then Result := VarIsEmpty(v1)
else
try
if CheckAsString then
Result := ( VarToStr(V1) = VarToStr(V2) )
else
Result := ( V1 = V2 );
except
end;
end;
Now we have two basic procedures to transfer field data from a TDataset record to a TParams collection and vice versa. Just thought how easy it is to implement a simple Ditto function on a dataset by using the above procedure/function…
procedure DittoRecord(aDataset: TDataset);
var tmpParams: TParams;
begin
{Create the temp storage}
tmpParams := TParams.Create;
try
{Copy field data to temp storage}
FieldsToParams(aDataset.Fields,tmpParams);
{Append a new record to the dataset}
aDataset.Append;
{Fill field values from temp storage}
MoveCorr(tmpParams,aDataset);
{…you may need to fixup some field values like keys…}
{Post newly appended record}
aDataset.Post;
finally
FreeAndNil(tmpParams);
End;
end;
The above code fragments are directly from my code libraries and include also some of the comments i have done. Feel free to modify the above code as per your needs and if you make any enhancements please contact me.
