[SOLVED]TField alias in TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
larsal007 · 2013-05-23 00:52

Hello everybody,
This is my first post. I use a TSQLQuery to get data from postgresql
server. I use alias field in SQL string and can get alias fieldname to
generate components on the form and show the data but I am not able to
get original DB field to do SQL updates.

Code: [Select]var
  MainForm: TMainForm;
  FCon  : TPQConnection;
  FTrs  : TSQLTransaction;
  Libelles: array [0..100] of TLabel;
  Champs  : array [0..100] of TWinControl;
  sqlSRPList: string = 'select srpnum as "ID", srpnam as "Designation"
from salesrep order by "ID";';
  sqlSRPForm: String ='select srpnum as "&Identifiant", srpnam as
"&Nom",'+
                     'datein as "Date entrée", dateout as "Date de
sortie",'+
                     'isactive as "Actif" from salesrep;';

procedure TMainForm.FormCreate(Sender: TObject);
begin
  FCon := TPQConnection.Create(self);
  FCon.DatabaseName:= '*******';
  FCon.UserName    := 'postgres';
  FCon.HostName    := '*********';
  FCon.Password    := '*********';

  FTrs := TSQLTransaction.Create(MainForm);
  FTrs.DataBase := FCon;
end;

function TMainForm.GetQuery : TSQLQuery;
  var AQuery : TSQLQuery;
begin
  AQuery := TSQLQuery.Create(MainForm);
  AQuery.Database    := FCon;
  AQuery.Transaction := FTrs;
  Result             := AQuery;
end;

procedure TMainForm.BuildForm(panel: TGroupBox; sql: string);
var
  i, ColCount, LabelWidth : integer;
  dataset                 : TSQLQuery;
begin
  LabelWidth:=0;
  if panel.ComponentCount>0 then exit;;
  //Initialiser la requete
  dataset     := GetQuery;
  dataset.SQL.Text := sql;
  dataset.Open;

  colcount :=dataset.Fields.Count;     //Le nombre de colonnes

  //Loop 1:
  //Calculer la largeur des libélles
  For i:=0 to colcount-1 do begin
    if LabelWidth < Canvas.TextWidth(dataset.Fields[i].FieldName) then
       LabelWidth:= Canvas.TextWidth(dataset.Fields[i].FieldName)+10;
  end;

  //Loop 2:
  for i:=0 to colcount-1 do begin
    //Création des champs
    case dataset.FieldByName(dataset.Fields[i].FieldName).DataType of
      ftString:begin
          Champs[i] := TmzEdit.Create(panel);            //Crée champ
          Champs[i].width := dataset.Fields[i].Size*11;  //Fixe
largeur
          TmzEdit(Champs[i]).MaxLength:=dataset.Fields[i].Size;//Fixe
taille saisie
        end;
      ftBoolean     :Champs[i] := TCheckBox.Create(panel);//Crée champ
à cocher
      ftdate        :Champs[i] := TmzDateTime.Create(panel);//Crée
champ date
    end;

    with Champs[i] do
    begin
      Parent := panel;
      Top    := 5+(20*i);
      Left   := LabelWidth;
    end;

    //Création des libéllés
    Libelles[i] := TLabel.Create(panel);//Créer les libellés
    with Libelles[i] do
    begin
      Parent := panel;
      Top    := 5+(20*i);
      Left   := 5;
      Width  := LabelWidth;
      Caption:=dataset.Fields[i].FieldName;
      FocusControl:=Champs[i];      //Affecte raccourci clavier au
champ adequat
    end;
  end;
  panel.Height:=((panel.ComponentCount div 2)+1)*20;

  dataset.Close;
  dataset.Free;
end;

I need an instruction like dataset.Fields.FieldOriginalName wich
replies srpnam or srpnum (fields names in database and not alias
names).
Any help will be welcome.

----------------------------------------------------------------------
exdatis · 2013-05-23 05:24

I think it's more useful to use the original names of the fields in
the table and the property DisplayLabel for aliases.

----------------------------------------------------------------------
larsal007 · 2013-05-23 14:59

After checking, it looks like the origin property is filled when the
TSQLQuery is created in design time.

Unfortunately, it not my case. Anyway, I have to use a bad trick but
it is ok for me : I parse the displaylabel property from the SQL query
strings which includes both origin and displaylabel like this :
Code: [Select]select srpnum as "srpnum:ID", srpnam as
"srpnam:Designation" from salesrep order by "srpnum:ID"
I know this not the best but it works.

Thanks for your reply.