Continuing the migration, supplemental

Today I will write about something that I meant to include in the previous post but forgot: when does one need a TFDMemTable (hereinafter, TMT)? After all, one can define a TFDQuery, include an SQL query to retrieve data from the database, connect the query to a datasource and thence to a grid: the data will be displayed. Also, in a simple 'edit' form, there's no need for a TMT.

I have identified two cases when a TMT is required:

1. If the grid that displays the data has an OnTitleClick event - this allows the user to sort the data.
2. If the form allows the addition or deletion of data.

According to CoPilot, a TFDQuery doesn't have indexes whereas a TMT does. So when I migrate units with a grid, I first check to see whether the grid has an OnTitleClick event; if so, I know that I need to a TMT. Incidentally, there is a new flag that has to be set in the grid's options - OnTitleClick. This flag didn't exist in Delphi 7, and I came across it in one of the first units that I migrated. I was clicking on the title bar but the event did not fire ... because this new flag was not set.

If the grid does not have an OnTitleClick event, I check whether the form has a qGetOne query - this means that data has to be added - or a qDelete query. In the case of qGetOne, previously I used to iterate over the fields of the query and add them one by one to the clientdataset. A TMT has a new method, AppendRecord, that allows for the data to be added in one statement (never mind that beneath the hood, the data is still being added field by field). I have used this everywhere, apart from one or two special cases where AppendRecord seemed not to be appropriate.

The TMT comes 'naked' without fields. Here is my methodology for adding them (this may not be optimal). First I check that the query can be opened - it frequently happens that fields are defined automatically as ftString when they should be defined as ftWideString. So I fix all these before I even start with the TMT. When the query is defined properly, I open the 'fielddefs' property of the TMT and begin to add fields. The size of the fields is very important when the field in the query is a ftWideString; the default size is 20 but this should be changed to match the size of the query's field. When all the fields have been defined in the 'fielddefs' property, I then open the TMT's fields editor. This will be empty so I press on 'add all fields' and the fields that I added in the 'fielddefs' property now become persistent fields. I then iterate over the fields in the query, copying their displaynames to the TMT's fields, and finally I delete all the persistent fields of the query. I do this in order to highlight any use in the code of these persistent fields - it has happened that I received bizarre results when debugging and I discovered that these came from usage of the query's persistent fields. As the grid displays data coming from the TMT and not the query, progressing through the TMT gives different data in the persistent fields, whereas the query's persistent fields stay the same.

I wrote about changing column widths in a grid and saving them. I came across a very interesting problem with this: every form has a minimum width that is hard coded. I changed the column widths of one grid so much that the new width was less than the minimum width; as a result, there was a huge space on the left hand side of the grid before data appeared. How could I fix this? I made two additions: I converted the procedure that saves the column widths into a function that returns the total width with an extra 72 pixels for the scroll bar. I then wrote a procedure in the ancestor form that saves this new width. When the form is opened, this new width is compared to the minimum width, and if it is less, then the minimum width becomes this new width. Slightly messy but it works. Part of the problem is that the code for saving and restoring column widths is in a different unit from the ancestor form. Maybe I should move this code to the ancestor form - but not every form descends from this ancestor and maybe I would want to save column widths on such a form (unlikely).

---

This day in blog history:

Blog #	Date	Title	Tags
119	16/03/2008	Back to blogging	Office automation, Meta-blogging
120	16/03/2008	Chava Alberstein	Chava Alberstein
464	16/03/2012	Rubber duck debugging	Programming
1480	16/03/2022	My first year as a Londoner, part 1 - being a student	Personal, 1975, 1974
1593	16/03/2023	I contain multitudes	Food science, Non-fiction books

Continuing the migration

Over the weekend I worked for 15 hours on the continuing migration¹ of the OP's management program. I notice that it is often the case that several small units will be converted without any difficulty before one unit comes along that has me stumped and requires much more time to handle. Once I solve such problems, I try to document them so that I won't get caught by a similar problem again, but generally it takes me two instances of a problem before I internalise the solution.

So what have I learnt so far? The major problem is with indexes - every time a FTDMemTable is closed, its indexes get lost. So don't close the table! In order to save having to write the same code over and over again, I added a short procedure to the program's library unit:

Procedure LoadData (aq: TFDQuery; mt: TFDMemTable; idx: integer);
begin
 aq.open;
 if mt.active then mt.close;
 mt.copydataset (aq, [coRestart, coAppend]);
 buildindices (mt);
 mt.indexname:= 'idx' + inttostr (idx * 2);
end;

Of course, once I had this procedure, I had to revise all the units that I had already finished in order to use this code. 

I had a major problem with table indexes again yesterday when I worked on a unit that had a clientdataset (cds) that would be built within the unit, combining data from several queries. Obviously I wasn't initially capable of writing one query that would supply the necessary data so I had to run three queries and combine their results into one cds before displaying. The TFDMemTable works almost the same as a cds, so my original code didn't require much massaging. I had forgotten that I had already come across this problem; solved it and even documented² it. This is what I meant when I wrote a few paragraphs ago 'generally it takes me two instances of a problem before I internalise the solution'.

The order of writing should be as follows (see manage135):

1. Define fields, remembering to use ftWideString when necessary.
2. Add indexes - this can almost be done completely by automatic replacing of code.
3. Finalisation: createdataset, indexdefs.update then open.

Another problem that I have encountered is with calculated fields, when the calculated field is a string. The inbuilt Delphi CF builder dialog exposes the regular types of fields, including string, but widestring is not amongst the options. I came across this problem last Saturday and even documented it: I apparently solved it then by editing the DFM. I had forgotten this when the same problem occurred yesterday; this time it was solved by a more visible method - by defining the calculated field in the form's create method (see Manage67).

 f:= TWideStringField.create (mtReceiptsList);
 f.fieldname:= 'CashType';
 f.DisplayLabel:= 'סוג תשלום';
 f.fieldkind:= fkCalculated;
 f.size:= 20;
 f.dataset:= mtReceiptsList;

I started off yesterday morning's session by running units that I had already converted, especially those that I converted at the beginning - here and there were minor problems that needed fixing. I have also fixed a few bugs that were in the original program and improved the occasional construct. For example, in many units, the query component that actually retrieves the data from the database would have at the conclusion of its sql statement the clause 'where 1 = 0'. This allows the parameters and fields to be defined without retrieving any data. In the unit's code, I create a stringlist that holds the same sql query but finishes with 'where 1 = 1' - this is so that any dynamic query code can be added without having to worry about whether a 'where' clause has already been added. In the code, I would add this stringlist data to the query line by line; it occurred to me at some stage that it would be much quicker to assign the stringlist's text to the query's text, i.e. aquery.sql.text:= stringlist.text. 

Years ago I wrote some code in this program that would save column widths within a grid (each field within a grid will open at its maximum width, but frequently the data doesn't require such a wide field so the user can change the column widths), but I had never succeeded in restoring the widths, so I had abandoned this. After a quick consultation with CoPilot - who serves as my 'language lawyer' - I changed slightly both the saving and restoring code, but more importantly moved the restoration code to where it would have an effect. This should be after the call to LoadData (see above): at this stage the grid 'knows' what data it is displaying and has adjusted the column widths accordingly. Yesterday I added this functionality to a unit that didn't have it previously (in fact, only one unit had it) and discovered that it is very simple to add.

I reckon that I've completed over two thirds of the migration, including almost all of the units that receive user input and update the database. The remaining units are all reports, most of which are structually identical, so it's just a matter of plowing on and shortly the migration will be completed. Then will come the testing....

Internal links
[1] 2084
[2] 2082

---

This day in blog history:

Blog #	Date	Title	Tags
463	15/03/2012	Sequencing "Lost" / 2	Van der Graaf Generator, Peter Hammill, Home recording
688	15/03/2014	Boy, was I wrong - programming naivety	Programming, Delphi, ClientDataSet
1381	15/03/2021	A year of Covid-19	Covid-19
1592	15/03/2023	Goodbye, Dilbert (at least for the time being)	Personal
1731	15/03/2024	Jasmine Myra (2)	Jasmine Myra