Optional field in TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2019-01-29 10:06

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 · 2019-01-29 10:15

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
CheckBox1.Checked then   DBGrid1.Datasource :=
DataSource_WithJoinelse  DBGrid1.Datasource := DataSource_WithOutJoin
 
If you don't like the above method just set visibity to false:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.Fields.FieldByName('fasdfa').Visible := False;But the search
won't be faster.

----------------------------------------------------------------------
Vodnik · 2019-01-29 10:51

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 · 2019-01-29 12:30

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 · 2019-01-29 12:58

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
AddField(DBGrid: TDBGrid; const AName: String): Boolean;var  I:
Integer;  DataSet: TDataSet;  FildDef: TFieldDef;  NewField:
TField;begin  Result := False;  Dataset := DBGrid.DataSource.DataSet; 
Dataset.Active := False;  try     for I := 0 to
Dataset.FieldDefs.Count - 1 do     begin        FildDef :=
Dataset.FieldDefs.Items[I];        if Assigned(FildDef) and
(DataSet.FindField(FildDef.Name) = nil) and (UpperCase(FildDef.Name) =
UpperCase(AName)) then        begin          NewField :=
FildDef.CreateField(DataSet.Owner);          NewField.Name := AName; 
       Result := True;          Break;        end;      end; 
 finally     DataSet.Active := True;   end;end; procedure
TfMain.Button1Click(Sender: TObject);begin  if AddField(DBGrid,
'ASSIGNEDAGENTNAME') then    ShowMessage('Ok');end;

----------------------------------------------------------------------
Vodnik · 2019-01-29 13:03

Quote from: rvk on January 29, 2019, 01:30:04 pmDo 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 · 2019-01-29 13:11

Thanks, GetMem, I will try this.

----------------------------------------------------------------------
Vodnik · 2019-01-30 19:34

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i SELECT callid, callstart, ..., , u.userlastname as
assignedagentname from callrecord left join...