Board index » delphi » TQuery calculated field

2005-08-11 01:46:40 AM
delphi223
Is there any way to do fill out a calculated field for a TQuery at run-time, not
design time ?
I want to do a query against a table, and add another field whose calculated
value is too complicated to be calculated by normal Sql means ( it depends on
the value of columns in previous records ). Afterwards I pass on the dataset to
another component which can process the fields, including the calculated value,
against a separate table for insertion or update.
While I can have the second component do the necessary calculations for the
extra field as the dataset records are being processed, I'd rather have the
TQuery calculate the value and place it in the calculated field for each record
before paasing it to the second component. This way the second component does
the same processing it always has, and the burden of handling the calculated
field remains with the original TQuery.
 

Add a calculated field to the TQuery and perform the calculation in the
OnCalcFields event handler.
--
Bill Todd (TeamB)
 

Bill Todd writes:
dynamic fields, created from an Sql select statement. From my understanding
calculated fields only occur with persistent fields created at design time.
 

You can create the fields dynamically at runtime as follows.
var
I: Integer;
Field: TField;
begin
{ Can only add fields to inactive dataset. }
Table1.Active := False;
{ Allocates field definitions if dataset has never been made active. }
Table1.FieldDefs.Update;
{ Create all fields from definitions and add to dataset. }
for I := 0 to Table1.FieldDefs.Count - 1 do
begin
{ Here is where we actually tell the dataset to allocate a field. }
{ Field gets assigned but we don't need it - just points to new
field. }
Field := Table1.FieldDefs[I].CreateField(Table1);
end;
{ This is how you can add additional calculated fields }
Field := TStringField.Create(Table1);
Field.FieldName := 'Total';
Field.Calculated := True;
Field.DataSet := Table1;
{ Now we can see our fields. }
Table1.Active := True;
--
Bill Todd (TeamB)
Edward Diener writes:

Bill Todd writes:
when I set Active to true, or do I manually have to fill in any information here
for each field I create ?
when the dataset is open ? Or is it called once when the query is executed and
it is up to my code to cycle through each record in order to update the
calculated field(s)?
 

Edward Diener writes:
field objects created at design time.
example creates a TStringField so the type is string. Create
TIntegerField, TFloatField etc. depending on your needs.
OnCalcFields is triggered when:
A dataset is opened.
A dataset is put into dsEdit state.
A record is retrieved from a database.
When the AutoCalcFields property is true, OnCalcFields is also
triggered when:
Focus moves from one visual control to another, or from one column to
another is a data-aware grid control and modifications were made to the
record.
Note: When the AutoCalcFields property is true, an OnCalcFields event
handler should not modify the dataset (or a linked dataset if it is
part of a master-detail relationship), because such modifications
retrigger the OnCalcField event, leading to infinite recursion.
If an application permits users to change data, OnCalcFields is
frequently triggered. To reduce the frequency with which OnCalcFields
occurs, set AutoCalcFields to false.
Warning: When the dataset is the master table of a master-detail
relationship, OnCalcFields occurs before detail sets have been
synchronized with the master table.
--
Bill Todd (TeamB)
 

Bill Todd writes:
type of field. Then I can create the fields. With purely dynamic fields this is
not necessary, since the fields are created when my dataset is made Active.
However if this is the only way to add a calculated field to my fields at
run-time, creating each TFieldDef and then creating the field from it is what I
have to do.
here also.
happening when OnCalcFields is called, so I am making some educated guesses.
I will assume that OnCalcFields is called for each record in which the
calculated field must be filled as that record becomes the current record, and
that the TDataSet passed to the handler is on that current record. I will also
assume that one fills the calculated field by setting the Value for the
particular field.
 

Edward Diener writes:
calculation you must use a second dataset component. If you move to
another row within OnCalcFields you can get into a recursive loop.
--
Bill Todd (TeamB)
 

Bill Todd writes:
received an exception. After I set my SQL property, the FieldDefs are indeed
generated after I call FieldDefs.Update.
this technique.
After setting Active to false for my TQuery, if my SQL property changes will my
FieldDefs be automatically updated or do I need to call FieldDefs.Update each
time, perhaps to clear out the old ones ? Previous to using calculated fields
with my query, I never had to call FieldDefs.Update for the TQuery to work and I
just want to make sure that I do not need to call it if I do not add a
calculated field to my dataset in the technique you showed me. I am of course
deleting the calculated field when my TQuery is no longer using one, ie. when I
change my SQL statement to a query which does not need to have a calculated
field added to the dataset.
 

Edward Diener writes:
fild objects.
returned you need to delete all of the field objects you created in
code, not just the calculated field.
--
Bill Todd (TeamB)
 

Bill Todd writes:
deleted when the field definitions are refreshed, ie. when a new value for SQL
property is assigned ?
 

Edward Diener writes:
get any error when you change the SQL. If not, you do not need to do
anything.
--
Bill Todd (TeamB)
 

Bill Todd writes:
very much for your help on this problem. Being able to add a calculated field on
the fly made the design of what I am doing much clearer.