Migrating a query form to D12CE

I reckon that yesterday¹'s serendipitous discovery about the data controls saved about ten hours' work and who knows how much frustration. With not having to worry about data controls, I was able to dive straight in to the migration process. To use a metaphor, migrating an application is like hill climbing; one has to put in a great deal of work to cover maybe 80% of what needs to be done, but once that 80% is done, the rest is relatively easy.

After having obtained what I thought was a stable framework with nothing extra defined, I wanted to migrate a simple query form first: this form displays the number of times each unit has been accessed by which user during a given time period. This form uses the regular (for me) paradigm² of a page control with two tabs, one in which the user chooses values for parameters and one in which the relevant data is displayed. This form also allows the query parameters to be saved for recall at a later date.

As such, even though I'm theoretically migrating one unit (Manage38, to be precise), I also have to migrate a unit that handles the saved parameters (Manage137) and add the various necessary queries to the data module. Adding the necessary queries is mechanical work, copying the name, sql statement and parameters from the old dbExpress based queries to the new FireDAC queries. There is one very important caveat with this: if the query is saving a Hebrew string (like the name of a person or an activity or whatever), the parameter datatype must be defined as ftWideString; this ensures that the string will be saved in Unicode Hebrew.

I don't think that Manage137 presented any challenges, but Manage38 certainly did. Apart from replacing queries, I also had to reconnect an OnCalcFields handler to one of the queries. The major problem with this unit was the handling of indexes for displaying the data, should the user wish to change by which field the data is sorted, and whether it is sorted in reverse. I had devoted some time to this issue previously³, but there the solution was very specific, using real field names, whereas I wanted something much more generic - the index handling routine will probably be used by 50-100 units! 

As I wrote then,[w]ith a ClientDataSet I could define indexes on the query when a form opens and these indexes would always be available; with FDMemTable, predefined indexes get deleted every time data is copied to the table (a real bug that has been fixed), so I have to use a more dynamic method. Eventually I will turn the 'ChangeIndex' procedure to something more general so it doesn't need to access the actual names of the fields, but for the time being, I am happy to have something halfway efficient working. So instead of defining the indexes at the beginning each unit's code and simply changing them when the user clicks on the grid's header, I needed to create an index after that header click.

This actually required two separate library calls, although one is a subset of the other. The subset call is required when the form is initially displayed so that it can be sorted by the same field as it was the last time the form was opened. The more interesting library call is in the OnTitleClick handler - 'n' is the column number, and 'rev' adds an extra definition should the data be required to be sorted in reverse order.

Procedure ChangeIndex (rev: boolean; column: integer; aquery: TFDQuery);
var
 s, aname: string;
 idx: TFDIndex;

begin
 s:= aquery.fields[column].FieldName;
 aname:= 'idx_' + s;
 idx:= aquery.Indexes.FindIndex (aname);
 if idx <> nil then aquery.indexes.delete (idx.Index);
 idx:= aquery.Indexes.Add;
 idx.name:= aname;
 idx.fields:= s;
 if rev
  then idx.descfields:= s
  else idx.descfields:= '';
 idx.active:= true;
 aquery.IndexName:= aname;
 aquery.First;
end;

Now that I have this unit defined, I'll start migrating other query forms. It's good that I read my old blogs, because yesterday I had forgotten the corollary to that statement predefined indexes get deleted every time data is copied to the table, namely [this is] a real bug that has been fixed. I'm using a newer version of Delphi and FireDAC, and CoPilot assures me that my original procedure ('BuildIndexes') will work now as it worked then. So I'll restore that code into the current unit and see whether it does indeed work. Assuming that it does, that will save another mechanical translation to watch out for.

What is going to be time consuming is that some 'father' forms have only one 'son' form but one in particular has 20-30 son forms! I'll leave that one for later.

Internal links
[1] 2078
[2] 1400
[3] 2066

---

This day in blog history:

Blog #	Date	Title	Tags
25	21/02/2006	Virus ate my bagels	Programming, Computer virus
813	21/02/2015	Musicology and harmony	Music theory