Optional field in TSQLQuery

[Vodnik]

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?

[balazsszekely]

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.

[Vodnik]

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]

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.

[balazsszekely]

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]

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]

Thanks, GetMem, I will try this.

[Vodnik]

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...