Recent

Author Topic: Optional field in TSQLQuery  (Read 1355 times)

Vodnik

  • Full Member
  • ***
  • Posts: 110
Optional field in TSQLQuery
« on: January 29, 2019, 11:06:00 am »
I use TSQLQuery to process SELECT request. Result is presented in DBGrid.
SELECT contain field assignedagentname, which is not in the requested callrecord table, but obtained with JOIN. JOIN slowdowns the SQL operation; so I would like to make this field optional.
I have used OI to create TSQLQuery instance DBquery1 (see screenshot). In FieldDefs I defined all fields contained in table callrecord. Then I added some fields (e.g. DBquery1assignedagentname) that I would like to be shown in DBGrid.
Everything works fine.
But I would like to make field DBquery1assignedagentname optional: if user sets check that he would like to see it, it is used, otherwise not (for faster search). I can't find whether fields can be added and removed during run-time. Is it possible?

GetMem

  • Hero Member
  • *****
  • Posts: 3539
Re: Optional field in TSQLQuery
« Reply #1 on: January 29, 2019, 11:15:07 am »
You can add/remove fields during runtime, but your query won't speed up since you already did a join. In my opinion the best approach is to use two separate queries + datasources, then link the grid to the appropriate query according to the checkbox checked state:
Code: Pascal  [Select]
  1. if CheckBox1.Checked then
  2.   DBGrid1.Datasource := DataSource_WithJoin
  3. else
  4.   DBGrid1.Datasource := DataSource_WithOutJoin

 
If you don't like the above method just set visibity to false:
Code: Pascal  [Select]
  1. SQLQuery1.Fields.FieldByName('fasdfa').Visible := False;
But the search won't be faster.
« Last Edit: January 29, 2019, 11:25:25 am by GetMem »

Vodnik

  • Full Member
  • ***
  • Posts: 110
Re: Optional field in TSQLQuery
« Reply #2 on: January 29, 2019, 11:51:15 am »
Visibility, of course, will not improve the speed.
I thought about several Datasources, but what if I have to make optional more fields?
If it is possible to add/remove fields during run-time, why not to:
- remove optional field from TSQLQuery (how?),
- modify TSQLQuery.SQL.text to exclude JOIN related parts,
- call TSQLQuery.Open?

rvk

  • Hero Member
  • *****
  • Posts: 3862
Re: Optional field in TSQLQuery
« Reply #3 on: January 29, 2019, 01:30:04 pm »
Do you really need to access the fields via the Object Inspector?
Do you really need the variable DBquery1assignedagentname etc.?

If not, you best remove ALL the fields from DBquery1.
After that, fields are automatically generated for any DBGrid and you can access them by DBquery1.FieldByName() in code.

GetMem

  • Hero Member
  • *****
  • Posts: 3539
Re: Optional field in TSQLQuery
« Reply #4 on: January 29, 2019, 01:58:40 pm »
Assuming that you queried the field "AssignedAgentName" via SQL, but is not added to the available fileds in SQLQuery(not visible in the grid):
Code: Pascal  [Select]
  1. function AddField(DBGrid: TDBGrid; const AName: String): Boolean;
  2. var
  3.   I: Integer;
  4.   DataSet: TDataSet;
  5.   FildDef: TFieldDef;
  6.   NewField: TField;
  7. begin
  8.   Result := False;
  9.   Dataset := DBGrid.DataSource.DataSet;
  10.   Dataset.Active := False;
  11.   try
  12.      for I := 0 to Dataset.FieldDefs.Count - 1 do
  13.      begin
  14.         FildDef := Dataset.FieldDefs.Items[I];
  15.         if Assigned(FildDef) and (DataSet.FindField(FildDef.Name) = nil) and (UpperCase(FildDef.Name) = UpperCase(AName)) then
  16.         begin
  17.           NewField := FildDef.CreateField(DataSet.Owner);
  18.           NewField.Name := AName;
  19.           Result := True;
  20.           Break;
  21.         end;
  22.       end;
  23.    finally
  24.      DataSet.Active := True;
  25.    end;
  26. end;
  27.  
  28. procedure TfMain.Button1Click(Sender: TObject);
  29. begin
  30.   if AddField(DBGrid, 'ASSIGNEDAGENTNAME') then
  31.     ShowMessage('Ok');
  32. end;

Vodnik

  • Full Member
  • ***
  • Posts: 110
Re: Optional field in TSQLQuery
« Reply #5 on: January 29, 2019, 02:03:32 pm »
Do you really need to access the fields via the Object Inspector?
Do you really need the variable DBquery1assignedagentname etc.?

If not, you best remove ALL the fields from DBquery1.
After that, fields are automatically generated for any DBGrid and you can access them by DBquery1.FieldByName() in code.

Do I really need to access the fields via the Object Inspector?
- mmm... Maybe No, this is my first experience

Do I really need the variable DBquery1assignedagentname etc.?
- Yes. Some fields use OnGetText event handler.

I started the project without fields, but later added them to make use of OnGetText handlers. Exactly DBquery1assignedagentname field does not use handlers.


Vodnik

  • Full Member
  • ***
  • Posts: 110
Re: Optional field in TSQLQuery
« Reply #6 on: January 29, 2019, 02:11:35 pm »
Thanks, GetMem, I will try this.

Vodnik

  • Full Member
  • ***
  • Posts: 110
Re: Optional field in TSQLQuery
« Reply #7 on: January 30, 2019, 08:34:58 pm »
Well, I have found a very simple workaround for this.
When JOIN should not be used, I create a "fake" field assignedagentname in SQL query, that is always empty:
Code: MySQL  [Select]
  1. SELECT callid, callstart, ..., "" as assignedagentname from callrecord

When JOIN is to be used, I use real field assignedagentname, derived with JOIN,  in SQL query:
Code: MySQL  [Select]
  1. SELECT callid, callstart, ..., , u.userlastname as assignedagentname from callrecord left join...