Assign a collection's fields to a ZUpdateSQL parameters
lazarus / 16-Databases

----------------------------------------------------------------------
JD · 2012-05-22 15:38

Hi there everyone,

I have some code where I assign values to a Zeos TZUpdateSQL component
from a collection as shown below:

Code: [Select]ZQuery.UpdateObject := ZUpdateSQL;
//
with ZUpdateSQL do
begin
    // set the values of the parameters
    Params.ParamByName('NOM').Value := LRecord[0].Nom;
    Params.ParamByName('ADRESSE').Value := LRecord[0].Adresse;
    Params.ParamByName('TELEPHONE').Value := LRecord[0].Telephone;
    Params.ParamByName('CONTACT').Value := LRecord[0].Contact;
end;

This is fine for collections with a small number of fields but I have
other collections with up to 40 fields so in order to save on typing.
I wanted to assign the components fields directly to the parameter
list without explicitly stating the parameters.

This is what I tried below:

Code: [Select]ZQuery.UpdateObject := ZUpdateSQL;
// set the parameter values
ZUpdateSQL.Params.Assign(LRecord[0]);

However, I got the following error:

Error: Cannot assign a TRecord to a TParams (where TRecord is the
collection class).

My question is this, how can I assign a TPersistent such as a
TCollection to ZUpdateSQL's parameters?

Thanks for your assistance.

JD

----------------------------------------------------------------------
ludob · 2012-05-22 17:12

QuoteMy question is this, how can I assign a TPersistent such as a
TCollection to ZUpdateSQL's parameters?AFAIK you can only assign
collections if the collection items are assignment compatible. By the
looks of it TRecords is a collection of items that are not compatible
with TParam.

----------------------------------------------------------------------
JD · 2012-05-22 21:46

This is a sample of the kind of collection I'm using

Code: [Select]type
  { TFoyer }
  TFoyer = class(TCollectionItem)
  private
    { private declarations }
    FID: integer;
    FNom: string;
    FAdresse: string;
    FVille: string;
    FCodePostale: string;
    FPaysID: integer;
    FTelephone: string;
    FContact: string;
  public
    { public declarations }
  published
    { published declarations }
    property ID: integer read FID write FID;
    property Nom: string read FNom write FNom;
    property Adresse: string read FAdresse write FAdresse;
    property Ville: string read FVille write FVille;
    property CodePostale: string read FCodePostale write FCodePostale;
    property PaysID: integer read FPaysID write FPaysID;
    property Telephone: string read FTelephone write FTelephone;
    property Contact: string read FContact write FContact;
  end;

  { TFoyers }
  TFoyers = class(TCollection)
  private
    { private declarations }
    function GetItem(AIndex: integer): TFoyer;
  public
    { public declarations }
    constructor Create;
    function Add: TFoyer;
    property Items[AIndex: integer]: TFoyer read GetItem; default;
  end;

Is it possible to assign such a collection (or its modified form) to
TParam such that the parameter values will directly coincide with the
collections field's values?

----------------------------------------------------------------------
ttomas · 2012-05-23 07:59

You can create function FieldByName(Field: String): Variant; in class
TFoyer.
Then in your code you can use:

Code: [Select]for i:=0 to Params.Count-1 do
begin
    Params[i].Value := LRecord[0].FieldByName(Params[i].Name);
end;

----------------------------------------------------------------------
ludob · 2012-05-23 08:58

This is a nice application for rtti.

Code: [Select]var
  ppl:PPropList;
  i,cnt:integer;
  s:string;
  obj:TObject;
begin
  obj:=LRecord[0];
  cnt:=GetPropList(obj.ClassInfo,tkProperties,nil);
  GetMem(ppl, cnt*SizeOf(Pointer));
  try
  GetPropList(obj.ClassInfo,tkProperties,ppl);
  for i:=0 to cnt-1 do
    begin
    s:=ppl^[i]^.Name;
    if ppl^[i]^.PropType^.Kind=tkInteger then
      Params.ParamByName(s).AsInteger:=GetOrdProp(obj,s)
    else if ppl^[i]^.PropType^.Kind=tkAString then
      Params.ParamByName(s).AsString:=GetStrProp(obj,s)
    end;
  finally
    freemem(ppl);
  end;
end;

EDIT: A much shorter version when you start with the query parameters
instead of the properties:

Code: [Select]var
  i:integer;
begin
  for i:=0 to SQLQuery1.Params.Count-1 do
    SQLQuery1.Params[i].Value:=GetPropValue(LRecord,SQLQuery1.Params[i
].Name);
end;
In both cases you probably need to catch the case where a param name
doesn't exists as a property or vice versa.

The GetPropValue can also be used in the first code sample
Code: [Select]  for i:=0 to cnt-1 do
    begin
    s:=ppl^[i]^.Name;
    Params.ParamByName(s).Value:=GetPropValue (obj,s)
    end;