FireDAC - TFDQuery - How to default calculated fields in the dataset to ReadOnly=False

[JonathanW 0]

I'm using the FireDAC TFDQuery component to run a SQL SELECT statement against a SQL Server database. The SQL statement contains a few 'calculated' column expressions using SQL functions such as CONVERT and CAST.  For example : CONVERT(DECIMAL(9, 6), 0) AS TotalHours   and   CONVERT(TINYINT, 0) AS WorkflowState.

 

In the resulting dataset, these calculated TField objects are always set to ReadOnly=True. I want to populate these calculated fields in code after the dataset has been returned.  I know I can do this by changing the ReadOnly property of each field :

Dataset.FieldByName('MyCalcField').ReadOnly := False;

But is there a setting/option within the query components that I can use to force the fields created to always default to ReadOnly=False?

Edited August 5, 2022 by JonathanW

[Roger Cigol 103]

I would be wary here. The field defaults to read only because it is a calculated field. Why would you want to change the value (unless you are trying something very dodgy?)  of a calculated field. This strikes me as the sort of thing that should ring an "alarm bell" with the question: "am I designing my code the best way ?"

[mvanrijnen 123]

You'd better add calculated fields to the dataset, and use the OnCalcFields event.

That way you can locally do then calculations,  

Use a slight different fieldname for the locally calculated, or same and don't fetch the calculated fields in the query.

 

[Uwe Raabe 2057]

1 minute ago, mvanrijnen said:

You'd better add calculated fields to the dataset, and use the OnCalcFields event.

Exactly! The approach used in the query is just the wrong one.

[tgbs 14]

1 hour ago, mvanrijnen said:

You'd better add calculated fields to the dataset, and use the OnCalcFields event.

That way you can locally do then calculations,  

Use a slight different fieldname for the locally calculated, or same and don't fetch the calculated fields in the query.

 

This depends on the type of calculation.
If there is programmer logic then oncalc event is ok. If it can be considered from the database then better to use syntax from the relevant sql database. No need to run another request/s to read data and calculate. A stored procedure can be used in the main query if it will be used frequently and has more complex logic.

[Uwe Raabe 2057]

1 minute ago, tgbs said:

If it can be considered from the database then better to use syntax from the relevant sql database.

In that case changing that field value inside the program seems a pretty rare case.

 

The shown queries give the impression that some dummy fields are returned with no relation to the data in the database. As there are better ways to create such fields in the program it makes the query the wrong place for it.

[mvanrijnen 123]

6 hours ago, tgbs said:

This depends on the type of calculation.
If there is programmer logic then oncalc event is ok. If it can be considered from the database then better to use syntax from the relevant sql database. No need to run another request/s to read data and calculate. A stored procedure can be used in the main query if it will be used frequently and has more complex logic.

Is not what the TP indicates with glhis post, what you post is not the question. 

You suggest first let the DB do it's work, retrieve that result, and on client side, overwrite that result with a clientside calculation?

 

[tgbs 14]

Quote

You suggest first let the DB do it's work, retrieve that result, and on client side, overwrite that result with a clientside calculation?

No. One or another only and it depends