Friday, October 06, 2023

Belated discovery in Delphi: defining indexes on calculated fields

Many years ago, I showed how to define indexes on the calculated fields of a ClientDataSet in Delphi. I haven't had need to look at that code since, as I included the index creation code as a utility procedure in the management program. Today, however, I was working on a different program and wanted to sort the data in a clientdataset in descending order (using AddIndex). When I used the name of an existing field (i.e. one defined in the sql code), AddIndex worked, but when I replaced the name of the field with a calculated field, I repeatedly received an error.

After looking for information on this topic, I finally found the answer: the calculated field has to be defined as an internal calculated field (fkInternalCalc), not as a regular calculated field. Once this has been done, the index can be defined as follows

qSigHarig.Open; qSigHarig.addindex ('idx1', 'thissig', [ixDescending], '', '', 0); qSigHarig.indexname:= 'idx1'; qSigHarig.first;
In fact, it can't be seen from that code that 'thissig' is an internal calculated field, but it is. The documentation is opaque on the difference between these two types of fields and doesn't mention the index problem.

Internally calculated fields, just like calculated fields, are calculated in an OnCalcFields event handler. However, you can optimize your event handler by checking the State property of your client dataset. When State is dsInternalCalc, you must recompute internally calculated fields. When State is dsCalcFields, you need only recompute regular calculated fields.


On a completely different topic, today is the 50th anniversary of the Yom Kippur War (at least, according to the Gregorian calendar). I wrote about this from my very limited point of view ten years ago.


This day in history:

Blog #Date TitleTags
41206/10/2011Rest in peaceRIP
98006/10/2016Health updateHealth, Theanine, Donating blood
153306/10/2022Weight and bp confirmedHealth

No comments: