Batch inserts

Yesterday I wrote how it took 3 hours and 13 minutes to migrate a table with slightly over 2M records. I then tried my hand at the 'times' table; I saw that this table contained slightly more than 14 million records and so the migration should take around 21 hours - which it did. At least the program didn't crash. Obviously I had to find a better method - and I did: batch inserts. 

Basically, an array of 2048 records is prepared and then is inserted at one go. This saves multiple commits thus saving time. A 'commit'  permanently saves all changes made during a transaction, making them visible to other users and ensuring data integrity. Naturally this takes a certain amount of time - even if it's a few milliseconds, that adds up when it has to be done 14M times. My batch code needed to do only about 6,900 commits that should save a great deal of time.

I wasn't prepared for how fast migrating a table of 14M would be: four minutes!! That's 315 times faster than the naive method of copying row by row.

The batch inserts were possible in this database because the three large tables are composed solely of integers and there's no need to faff around with codepage conversions.

Here's the code

const
 batchsize = 2048;

procedure Tq400IntMigrate.Button1Click(Sender: TObject);
var
 i, j, k, fieldcount, position: integer;
 tablename, s: string;

begin
 edit1.Text:= timetostr (time);
 button1.enabled:= false;
 for i:= 0 to componentcount - 1 do
  if components[i] is TCheckBox then
   if tCheckBox (components[i]).checked then
    begin
     tablename:= uppercase (tCheckBox (components[i]).caption);
     qCount.sql[1]:= tablename;
     qCount.open;
     pb.max:= qCount.fields[0].asinteger div batchsize;
     position:= 0;
     qDelete.SQL[1]:= tablename;
     qDelete.Connection:= newcon;
     qDelete.ExecSQL();
     sleep (5000);
     qBase.sql[1]:= tablename;
     qBase.open;
     fieldcount:= qBase.fields.count - 1;
     s:= 'select ';
     for j := 0 to fieldcount - 1 do
      s:= s + qbase.fields[j].fieldname + ', ';
     s:= s + qBase.fields[fieldcount].fieldname + ' from ' + tablename;
     SrcQuery.sql.text:= s;
     srcQuery.open;

     s:= 'insert into ' + tablename + ' values (';
     for j := 0 to fieldcount - 1 do
       s:= s + ':p' + inttostr (j) + ', ';
     s:= s + ':p' + inttostr (fieldcount) + ')';
     dstquery.sql.text:= s;

     dstQuery.params.ArraySize:= batchsize;
     dstQuery.prepare;

     k:= 0;
     while not srcquery.eof do
      begin
       for j := 0 to fieldcount do
        case srcquery.fields[j].datatype of
         ftInteger: dstquery.params[j].asintegers[k]:= srcQuery.fields[j].asinteger;
         ftLargeInt: dstquery.params[j].aslargeints[k]:= srcQuery.fields[j].asinteger;
         else dstquery.params[j].asintegers[k]:= srcQuery.fields[j].asinteger;
        end;
        inc (k);

       if k = batchsize then
        begin
         dstquery.execute (batchsize);
         dstquery.connection.Commit;
         k:= 0;
         inc (position);
         pb.position:= position
        end;
       srcquery.next
      end;

     if k > 0 then
      begin
       dstquery.execute (k);
       dstquery.connection.Commit;
      end;
    end;
 edit2.Text:= timetostr (time);
end;

Internal links
[1] 2104

---

This day in blog history:

Blog #	Date	Title	Tags
349	13/04/2011	Advanced SQL for me - NULLIF	Programming, SQL
696	13/04/2014	Fifteen minute meals	Cooking
827	13/04/2015	Vinyl log 3 - 13 April	DCI Banks, Richard Thompson, Vinyl log
1021	13/04/2017	April thesis update	DBA
1603	13/04/2023	Rain, rain, rain	Weather
1742	13/04/2024	Jasmine Myra - Knowingness	Jasmine Myra

Wait states

I migrated another database to unicode over the weekend. Updating the migrator was fairly straight-forward, although there was one problematic table with something like 35,000 that was defined as WIN1251 (Cyrillic) although the characters were actually encoded in WIN1255 (Hebrew). That table took some time to define its migration route. Although this database has fewer tables than the first database that I converted and most of those tables contain only numbers with much less text, it took much longer to migrate the database - because some of the tables are huge (relatively speaking). There are a few tables with over two million rows and one with over ten million rows. 

Let us say that I had problems migrating those tables because of their size. I started the migration program yesterday evening only to discover that it had crashed whilst handling one of those humungous tables. I restarted the program from the table where it had crashed, but I could see that soon it too would crash. So what could I do?

I thought that the crashing might be due to the migration program overwhelming the database manager who simply could not keep up with the pace at which records were being entered. 30 years ago, this would be called 'adding wait states': a wait state is a deliberate, one-or-more clock cycle delay introduced by a processor to synchronize with slower external components, such as RAM or I/O devices. It prevents the CPU from processing data before it is ready, ensuring data integrity at the cost of overall system performance (thank you Chrome AI). 

The first thing that the migrator does for each table is delete any data that may exist in the table; I added the command 'sleep (15000)' after the deletion to allow the data to be deleted; this command stops the migrator for 15 seconds. I then added a command that there would be 10 second breaks after each 2048 records have been read. This was initially 15 seconds and 1024 records, but I could see that this would be too slow. For one table, with just over 2 million records with the sleep time was 10 seconds every 2048 records; it took 3 hours and 13 minutes to migrate the table.

I'm now migrating the really large table and don't expect it to finish within the next few hours - but I do expect it to complete.

---

This day in blog history:

Blog #	Date	Title	Tags
826	12/04/2015	How not to display data in graphs	Statistics
1121	12/04/2018	Apology to Israel Railways	Trains
1602	12/04/2023	More bells and whistles for the 'Blog' program	Programming, Delphi, Blog manager program
1741	12/04/2024	Update on our spring performance	Musical group
1921	12/04/2025	Passover begins tonight	Jewish holidays, Song writing

The 'fx pedal to end all pedals' has ended its career

 The musical group had a knock-about session last night, playing anything that came into our collective head (eg 'Hotel California', 'Forever young' (Alphaville) and some Israeli songs - almost nothing that I have played before). Before we started, I checked out my pedal board and the fx pedal. I wrote ¹ two months ago that the 'fx pedal to end all pedals'² issues a fair amount of noise so in order to combat this, I set the noise gate to a level that let loud guitar through but not hiss. This caused the guitar to sound rather 'chunky' and lacking dynamics. Thinking about this on the way home, I realised that all the presets that I defined start with compression; maybe it would be better to create some presets with no compression and see what these sound like.

Before setting off for the rehearsal, I connected my phone to the pedal via bluetooth (and no small amount of bother) and attempted to remove the compression from all the settings. I think that I was partially successful in this. Despite my efforts, once I started playing, the sound from the amplifier was very weak. Exasperated, I disconnected the fx pedal from the system and suddenly my guitar volume increased ten-fold. I'll have to remove the pedal from the board and restore those that I previously removed.

On a completely different topic: yesterday was quite warm but this morning, when I was taking the dog for her morning walk, it started raining. Now that's not such an unusual event, but what makes it stand out is that the barometer in my head didn't give me a migraine. I did have an odd stomach ache in the evening, so this could be my body's new way of reacting, but it could also be due to the odd stomach rumblings from which I suffer now and then. So it looks like the new prophylactic pills³ that I have (that are meant for epileptics) are working.

Internal links
[1] 2074
[2] 2072
[3] 2095

---

This day in blog history:

Blog #	Date	Title	Tags
566	07/04/2013	A new technique in Word Automation	Programming, Office automation, HTML
822	07/04/2015	I've always kept a unicorn	Sandy Denny, Fotheringay
1119	07/04/2018	Season of the kumquat	Personal
1306	07/04/2020	Statistical methods for Epidemiologists	Statistics, Covid-19, Non-fiction books

Migration completed; now the debugging begins

At 5pm on Thursday afternoon, I completed the migration of the OP's management program with 354 units from ANSI Hebrew and dbExpress components to Unicode and FireDAC. When I write "completed", I mean that I had gone over every form and unit and replaced the dbExpress components with those of FireDAC. I also had briefly tested each form, but there was no guarantee that the program was free of bugs - in fact, I was fairly sure that the units that were converted first probably would not work correctly.

But before that, there was a topic that I had purposely not worked on, leaving it for the end. Almost all the forms that add data to the database have an autoincrement primary key; this is achieved by having a generator (something that returns a sequence of numbers, such as 1, 2, 3, ... 2001, 2002, etc) and a trigger that gets the next value from a generator and inserts it where needed. I didn't want functional generators when I was populating the new database from the old, but now that the translation had completed, I did need the generators and triggers for testing purposes. 

What I really needed was to drop all the generators and triggers before transferring any data, then to recreate them after all the data had been transferred. CoPilot got the dropping code partially correct - it would work if there was a trigger but wouldn't if there was no trigger. Eventually CP developed some complicated SQL code for a conditional delete. This also had to be done in the correct order: first delete the trigger then the generator; this is because the trigger refers to the generator. Recreating the generators and triggers was easy, at least when done in the correct order. Then the generators had to be seeded with their new starting value. Originally I wrote a small query that would return the maximum value in the key field and used this, but then I noticed that the migrator calculates how many rows are in a table (for a progress bar) so I used this - and shortly discovered that the number of rows is not necessarily the highest value of the key; there might have been deletions from the table. Eventually that was fixed and I migrated the database once more.

On Saturday morning, I started working through the program and found some simple problems to fix, for example queries that weren't attached to the database connection. I found more queries whose fields needed to be redefined, and I found some that were wrong because the columns to which they were connected were not defined correctly. This required a fix to the database and remigration of a single table. 

But there were some significant problems each of which took a few hours to fix. In what is probably the entire program's main form, the 'restore grid widths' routine wasn't working, although in fact, the correct values were returned but the grid 'ignored' them. The solution was to update the grid when all screen painting had finished, and so I added an OnPaint handler that sent a message to another procedure that would load the grid widths. This worked.

Another familiar problem occurred with a query called qSentReports: when certain reports are run, their HTML output is stored in a table so they can be retrieved at any stage. I had had problems when saving new reports in the table then restoring, so I assumed that retrieving reports that had been created in the previous version could be restored in a similar manner. But this was not so. According to CP, I was dealing with double or even triple encoding, involving WIN1255, WIN1252, UTF-16 and UTF-8. Then it struck me that the original data was already in UTF8 format, so the migrator shouldn't encode it and my code shouldn't decode it. After this epiphany, I once again transferred the data for this one table and finally it displayed correctly.

I am sure that there are further traps awaiting me, but I suspect that they will be minor. Now I don't know what to do with myself and all the free time that has suddenly opened up. 😉

---

This day in blog history:

Blog #	Date	Title	Tags
240	05/04/2010	Holiday	Cooking
346	05/04/2011	Firebird fixed!	Computers, Firebird
692	05/04/2014	Pneumonia	Health, Nick Drake
821	05/04/2015	Vinyl log 1 - 5 April	Vinyl log
937	05/04/2016	Sorrento shopping (2016/2)	Holiday, Sorrento, Italy
1118	05/04/2018	The sense of an ending (2)	Films, Literature
1207	05/04/2019	Excellent music blog	Beatles, Song writing
1597	05/04/2023	Passover night, 2023	Jewish holidays, Kibbutz
1920	05/04/2025	Slow cooked leg of lamb	Cooking, Slow cooker

2,100 blogs

Blog #2000 was written on 14/09/25, so that's 6.5 months for 100 blogs, or 15.8 blogs per month. Here is the histogram of blogs per month. The previous 100 blogs were written at an average pace of 14.3 blogs per month so I'm writing more.

I have to admit that I am surprised that 10/25 was such a fruitful month, although looking at what was written that month, I can understand. This was the beginning of the Prolog saga, a topic that would dominate the blog for the next few months - until a different programming project, the migration of a database and program to Unicode, began. Here are the blogs ordered by topic

Position	Tag	Count	Previous position	All time position
1	Programming	38	7	1
2	CoPilot	21	-	45
3	Prolog	21	-	39
4	Delphi	13	10	6
5	Personal	13	9	3
6	FireDAC	9	-	102
7	Unicode	8	-	50
8	Obituary	7	12	8
9	D12CE	6	-	142
10	Health	6	4	5
11	Israel	6	8	7
12	Computers	5	18	13
13	Films	5	-	18
14	Grandfather	4	-	37
15	Police procedurals	4	21	21
16	CPAP	3	-	23
17	Guitars	3	19	22
18	Musical group	3	5	27
19	Nick Drake	3	-	91

Of course, the period covered by these 100 blogs do not contain a holiday, so that's two topics off the board immediately. But it does seem that every six/seven months, my interests completely change. Here is the frequency histogram for the past 100 blogs.

---

Last night was the first night of Passover, and due to the security situation, we are back to the lock-down format¹ from the Covid-19 days. At least this time our daughter and grandchildren joined us.

Internal links:
[1] 1307

---

This day in blog history:

Blog #	Date	Title	Tags
124	02/04/2008	ERP	Programming, ERP, Maccabi Tel Aviv, Meta-blogging
820	02/04/2015	Introducing the vinyl log	Vinyl log
1117	02/04/2018	DBA update	DBA

Overconfident AI gets it wrong again

I wrote¹ a few days ago: I would also like to define metadata for the database. For example, if a report uses the field 'name' from the table 'customers' then automatically that field should have the title 'customer name' (in Hebrew) Similarly, 'surname' and 'forename' from table 'people' or 'name' from table 'therapists'. The fact that I want the title in Hebrew is incidental; even in English, the default name for customers.name is 'name', not 'customer name'. I put this to CoPilot who replied that this would be very easy to do: it requires a dictionary that translates between table names and Hebrew titles, and that the source for the dictionary would be a property of each field called 'origin'. The origin propery of a field containing a customer name would be CUSTOMERS.NAME.

I left this idea for a few days while I concentrated on list boxes, but yesterday I returned to continue work on this idea. I linked the 'find dictionary entry' procedure to a simple report and saw ... that it didn't work. The origin of a field CUSTOMERS.NAME was simply NAME and of course I have to distinguish between customer name, therapist name and activities.name, etc. CoPilot said that the table name didn't appear because the query was very simple and didn't invoke a join. So I looked at a more complicated query and saw that the origin property was empty for half of the fields! Only fields from the base table (ie the table after the keyword FROM) had origins, whereas the rest didn't.

OK, said CoPilot, you need the OriginTabName and OriginColName properties. "These do exist and will compile." Except they don't exist. After several rounds of CoPilot offering names of properties that sound plausible but don't exist, I told CoPilot that we were getting nowhere. CP agreed, saying that FireDAC doesn't expose sufficient metadata in order to provide a solution for what I wanted. Thank you very much. It wouldn't hurt to say once in a while that "I got it wrong".

In the end, I suggested and implemented a much simpler - and unfortunately, less automatic - solution: I created a stringlist and populated it with the Hebrew translations of 'customer name', 'contact name', 'person surname', etc. Each value has a specific number attached to it which is simply the offset within the stringlist: stringlist[1] = 'customer name', etc. In the FDQueries and FDMemTables in my code, I assign one of these values to a field's tag propery and then do the lookup on this property. In the extremely complicated form for sending an email, upon which I worked, at one stage it is necessary to display a list of therapists. Instead of having to title each field manually, I simply marked the tags as 56 and 58 and got the displaylabels that I wanted.

In conclusion: there are some things that CoPilot is good at, like seeing that a datasource was not enabled², or for building an ancestral dual list box dialog³. But for more creative solutions, CP is not particularly good and is even frequently wrong. And it still maintains that "This is reliable, simple, and works with all your existing forms". Yeah, right (the one time that two positives make a negative).

---

I've been so wrapped up in all this chat coding for the last few days that I didn't notice that this is blog #2100! The next blog will contain the traditional look at the previous 100 blogs.

Internal links
[1] 2097
[2] 2094
[3] 2099

---

This day in blog history:

Blog #	Date	Title	Tags
1384	01/04/2021	You and your action research project	DBA
1918	01/04/2025	Is this me or a double?	Personal

More about dual list boxes

After a minimal amount of research, I discovered that the dual listbox is regarded as a very good solution when one wants to select and organise options. I did some work with CoPilot on the subject last night; most of the time went on creating an ancestral form for the dual listbox dialog and a small amount of time working on a form that inherits from the ancestor. This morning I spent some time improving the visual aspects of the form and fixing a few bugs that had crept in (mainly misnaming errors).

Above is pictured an example of the form when it is populated with data. Just to make things slightly more difficult, it is of course drawn right-to-left. Until now, if I was feeling adventurous, I would place a bevel around a listbox or grid, slightly improving the appearance, but it struck me that it would be better to use a coloured panel and place the listbox on top of the panel - that's what gives the blue frame surrounding both list boxes. 

The form that inherits from the ancestor now contains about twenty lines of text - and that's all! The only things that differ between various descendant forms are their captions and the specific queries for loading the list boxes and saving the changes.

type
  TAddXtraRateActs = class(TDualListBox)
  private
  public
   procedure Execute (anxtra: longint; const aname: string);
  end;

implementation

{$R *.dfm}
uses managedm;

Procedure TAddXtraRateActs.Execute (anxtra: longint; const aname: string);
begin
 dm.ProgLog (178);
 LeftID:= anxtra;
 caption:= ' הוספת פעילויות לבונוס עבור ' + aname;
 qDistList.sql.text:= 'select activities.name, activities.id ' +
                      'from activities inner join xtrarateact ' +
                      'on activities.id = xtrarateact.act ' +
                      'where xtrarateact.xtra = :LeftID';
 qSrchList.sql.text:= 'select activities.id, activities.name ' +
                      'from activities where not exists ' +
                      '(select 1  from xtrarateact ' +
                      'where xtrarateact.act = activities.id ' +
                      'and xtrarateact.xtra = :LeftID)';
 qInsert.sql.text:= 'insert into xtrarateact (xtra, act) ' +
                    'values (:LeftID, :RightID)';
 qDelete.sql.text:= 'delete from xtrarateact where xtra = :LeftID ' +
                    'and act = :RightID';
 Initialise; // load the lists only after there is valid SQL in the queries
 showmodal
end;

That's it! Converting existing forms to this new format is a bit awkward but not difficult. It is important during the change-over to guard the sql statements from being lost, although I do have backups in the form of the original non-unicode program.

---

This day in blog history:

Blog #	Date	Title	Tags
123	31/03/2008	Lots of things to do	Programming, Psychology, Van der Graaf Generator, Ian Rankin, Dog, DVD, Brian Viner, Management exams
344	31/03/2011	Intellectual stimulation and frustration	ERP, MBA, HRM, Dan Ariely
467	31/03/2012	Sequencing "Darkness" / 2	MIDI, Van der Graaf Generator, Home recording
819	31/03/2015	New mobile computer	Computers

Updating the dual list box interface

Many moons ago, I wrote¹ about an internal improvement to the 'dual list box dialog. This is a template that came with the original version of Delphi and has served us well over the years. 

Yesterday, whilst reading the 'vibe coding²' book, I began thinking of ways to improve the appearance of the program and not have it stuck in the early 2000s. The first thing that came to mind was the above dialog box: wouldn't it be easier if there were only one checklistbox on the form? The user either marks (or removes marks) in order to add or remove an item from the collection. It turns out that programming this dialog is far simpler than the old dialog, but that shouldn't be a factor in deciding which format to use.

I sent both pictures to the OP so that she could give her opinion. Originally she chose the dual list box version "because this is what I know and it is very clear which items have been selected". I replied "boring", to which she replied "go with the new format if it's easier". At the moment I'm not going to convert any more forms to this new format - I want to give her a chance to make a more considered choice.

Internal links
[1] 307
[2] 2097

---

This day in blog history:

Blog #	Date	Title	Tags
1020	30/03/2017	Mint chocolate	Peppermint
1206	30/03/2019	New song	Song writing, Multi-track

Chat coding

On and off, I'm reading a book entitled 'Vibe coding' by Gene Kim and Steve Yegge that tells about the joys of working with AI to enhance programmers' productivity. It turns out that I am using AI for what is called 'chat coding': I write about the problems that I'm having, and AI (in my case, CoPilot) offers suggestions for continuing. I don't like asking AI to write my code for me! Anyway, at the moment I'm migrating previously written code, so I try to change as little as possible.

Yesterday I saw both the best and the worst of chat coding. I'll explain the scenario first before I get into what happens. One screen in the management program displays for each therapist/worker how many hours were worked or meetings held by that person in the previous month. The data is displayed in an internal web browser control, where the contents are in hand-written HTML; this means that I wrote queries then took the values and enclosed their values with my own HTML. This code was written at least 10 years ago when we didn't have chat coding. It works very well so there's nothing worth improving (so I think).

Should the person running the program so desire, the monthly report for a therapist can be sent via email; this basically means taking the already written HTML code and enclosing it within an email. I wrote about this years ago. Not only that, the report is saved in the database so it can be retrieved at any time. A second screen displays a list of such saved reports within a given time period, and should one so desire, a report can be retrieved and shown in a third screen, which is simply a web browser.

All seemed to be fine until I noticed that the user reports that I had created and saved that day were appearing in the reports list without a subject (title). I then spent what seemed to be an hour chasing the problem with CP; it was frustrating because everything seemed to be ok, but it wasn't. Eventually I uploaded the screen's DFM file and CP saw immediately saw what the problem was: the field before the missing field was defined as a string field of length 20,000 characters when in fact it should be a blob. So I corrected the query and the parameter, and saved some more reports. These appeared with subject in the intermediate screen.

But when I tried to access the saved reports, I saw what is normally referred to as gibberish: Chinese characters flowed across the screen. Reports that had been created prior to yesterday (in fact, prior to the redefinition of the field as a blob) displayed correctly. CP decided that the problem was with the web browser component that is based on IE7, i.e. very old and not particularly compatible with unicode. So I replaced the web browser with the edge browser component that is the new standard component for displaying HTML ... and then discovered that the component itself did not appear on the form. Ah, said CP, you have to initialise the browser. No change. Ah, said CP, you have to wait for the browse to finish initialising before you can display your page. No change, the browser still does not appear. Ah, said CP, you need a specific dll that you probably have on your computer. Copy the file to your program's directory. No changed. Ah, said CP, download a new version from such and such a website and make sure that you have the correct version (32 bits or 64 bits). Even that didn't work and I'm not sure that I was even downloading what I needed.

Fortunately - if that is the correct word - we had an air raid alarm (two missiles fell not far away from us) so I could disconnect from the computer and think a little. When I returned, I told CP that we were returning to the old web browser component because this worked. I then discovered that the original text wasn't being saved in the blob field in unicode (I checked via the database manger) - it was being saved as ANSI - so I resurrected a routine from the database migrator¹ and used it to save the text. This of course required that when the text is retrieved from the database, it also has to be converted. Finally the reports were being saved with subjects and could be shown correctly after retrieval from the database.

I then took the dog for a walk and thought about how I could turn the code that we had just written into a library routine. I was using the 'save' routine several times (and I suspect that I will need it again when I get to saving general emails) and it seemed excessive to write the same code over and over again, but it was also tightly coupled to a query. When I came back from the walk, I told this to CP who responded with a unit that included routines for saving and retrieving unicode blobs. A bit later, I required another similar routine that CP wrote which I added to the unit.

The book itself isn't too useful as it talks about tools and AI agents that I don't use and don't have access to. I am sure that they consider Delphi to be very old fashioned, but if it gets the work done then it's good. I want to quote two passages from the book.

When Gene first started vibe coding with Steve, Gene was convinced that the then-new OpenAI o1 model would be great at ffmpeg and could help him overlay captions onto video excerpts. That is to say, subtitles on YouTube clips. Two hours later, Gene ran around in circles, typing increasingly complex ffmpeg commands. The AI was more than wrong; It was confidently wrong. Thinking about that particular Sunday afternoon still causes Gene to clench his jaw. But he learned an important lesson on when to give up on using AI to solve certain types of problems. It was a crummy experience, but he learned from it because it was a crummy experience. You learn by doing.

Here are some other takeaways from this early vibe coding session: AIs are capable of handling small to medium tasks, including in less popular programming languages, and using fairly complex Unix command-line tools. You interact with AI as if it were a senior pair programmer who’s so distracted that they can make serious mistakes from time to time.

The book did inspire me to think of some tasks that I would like to look at after the migration has been finished. I would like to improve the visual aspect of the program: I am a person who thinks in terms of tables and not graphs, so the visual aspects aren't too important to me, but I would like to see how they can be improved. Another task is the problem of choosing multiple values for a parameter, i.e. instead of choosing one customer for a report, several customers can be chosen. I have this functionality but I want to see whether it can be improved. I have thought of one way already, and as this functionality is contained within one specific unit (but is used by many others), it would be quite easy to test.

I would also like to define metadata for the database. For example, if a report uses the field 'name' from the table 'customers' then automatically that field should have the title 'customer name' (in Hebrew) Similarly, 'surname' and 'forename' from table 'people' or 'name' from table 'therapists'. The fact that I want the title in Hebrew is incidental; even in English, the default name for customers.name is 'name', not 'customer name'.

Internal links
[1] 2075

---

This day in blog history:

Blog #	Date	Title	Tags
164	29/03/2009	The big chill	Films
239	29/03/2010	Pesach over the years	Jewish holidays, Obituary, Habonim, David Lodge, France
342	29/03/2011	I haven't disappeared off the face of the Earth	Peter Robinson, Van der Graaf Generator, Ian Rankin, Steig Larsson, BCC, Jo Nesbo
343	29/03/2011	The camino pilgrimage	David Lodge
466	29/03/2012	Nobel prize winner visits MBA	MBA
1019	29/03/2017	The label number bug	Priority tips

Razer Ornata v3 keyboard

Two months ago, when I bought my new computer, I wrote¹: the keyboard on the new computer is not very good; I suppose it's a function of getting used to it, but at the moment, I am inclined to invest in a better keyboard. All morning I kept on hitting the 'PrintScreen' key when I wanted to press F12 - that's very annoying. Finally I got around to doing something about this and purchased a new and heavier keyboard - a Razer Ornata v3.

This seems to be very much a gaming keyboard although that is not my interest. It's packaged very well and the USB plug even comes with a terminator (if that's the correct word). When I plugged it in, the keyboard seemed not to respond but that's probably because the computer was asleep. But I couldn't awake the computer via the keyboard because the keyboard wasn't recognised yet. I touched the power button lightly on the computer which caused it to spring to life and recognise the keyboard.

The first thing that I noted was that the keyboard changed colour every 30 seconds or so - very distracting. So I had to look for the firmware that would allow me to turn this 'functionality' off. I had to log in to the vendor's web page, possibly created a user ID and went through several screens before I could find something to download. In the end, several programs were downloaded - these are mainly intended for gamers and show demos of various games and products. Eventually I found the dialog that would allow me to turn off the colour show. This entire process was very annoying but fortunately I won't have to do it again. I have left the software installed on the computer in case I change my mind, or want to impress one of my grand-daughers, or have an epileptic fit.

Otherwise the keyboard itself seems fine - maybe I would have preferred something even heavier, but it's certainly better than the cheap keyboard that I had previously. I'll know soon enough whether this was a good purchase.

And talking of purchasing: I bought this from one of the shops in the local mall, what we would once call a stationers. It cost me 199 NIS which when amortised over several years amounts to nothing. When searching for a picture of the keyboard, I saw that the official importers charge 299 NIS for this keyboard. As the Americans probably say, do the math. 

Internal links
[1] 2065

---

This day in blog history:

Blog #	Date	Title	Tags
1302	26/03/2020	Counting beats with van der Graaf (2)	Van der Graaf Generator, Time signatures
1303	26/03/2020	Days of Corona (2)	Health, Covid-19
1487	26/03/2022	"You hold me" - you've heard the song, now watch the video	Home movies, Song videos
1915	26/03/2025	Clinging to the wreckage ... and legal morality	Israel

Neurologist

 After having gone a few years with very few migraines, it seems as if the cosmic karma is rebounding. I wrote three months ago¹ on whether the weather can cause migraines, and since then I've had a few bad migraines and many 'light' ones. I went to my GP shortly after that bad migraine and received a referral to a specialist; finally I had my appointment today.

Unfortunately his speech wasn't that clear and he also didn't give me much of a chance to talk, so in one sense I left the clinic slightly disappointed. I have now a new prophylactic medication, Depalept, that whilst primarily being a medication used for epilepsy, it can also be used for preventing migraines. The doctor explained that it has to be shown that three different prophylactic treatments were tried before the powers that be will grant permission for the new medications such as CGRP inhibitors, because of the expense. The first - and very unsuccessful medication - was propanolol, a beta blocker, that totally wiped me out when I tried it 15-20 years ago; I lasted four days with it. Since then I've been taking amitriptyline and now Depalept will be the new preventive treatment.

I also have new pills for taking after the migraine hits. Apparently sumatridex can cause rebound headaches if it is taken more than twice a week, and unfortunately over the past few weeks I've taken it frequently. As it happens, I have only half a pill left, and I asked my GP for a new subscription that I was going to fulfil today. The new pill, rizatriptan, is from the same family and I read that while rapid-acting, the pharmacological effect is short-term; the active medication is generally cleared from the system within a few hours, though efficacy against the migraine may last longer. Overuse can cause chronic, daily headaches. The pharmacist said that I can take a second pill after a few hours if the first isn't sufficient, although now I am slightly disturbed by the contradiction between what he said and 'overuse can cause headaches'.

As it happens, on the way to the clinic in Bet Shemesh, a headache started again. It's been bouncing about in my head, the pain never apparently settling in one place, and as usual it is lowering my sense of general well-being. But it's a sufferable pain, and unless it gets much worse, I'm going to leave it be. I have noticed that such headaches lower my level of tolerance towards other people and their shortcomings, but fortunately I don't think that decreased tolerance is going to be a problem today.

I am to start the new regime today and see how I fare, along with keeping a headache diary. I received a referral for a head CT (I did one several years ago that showed nothing but it would be remiss of the doctor not to order one) and have a return appointment in three months' time.

[Edit from later on the same day: The morning was fairly sunny but after 4 pm, it started raining and the weather had changed. Naturally I had a painful migraine but I didn't want to take any triptan pain relief in order to clear my body of them. Unfortunate that the weather had to turn the day of this appointment.]

Internal links
[1] 2045

---

This day in blog history:

Blog #	Date	Title	Tags
465	25/03/2012	Pharyngitis, Darkness	Health, Van der Graaf Generator
1595	25/03/2023	Reality Is Broken -- Why Games Make Us Better and How They Can Change the World	Personal, Non-fiction books

Knocking my head against a brick wall leads to a serendipitous discovery

Yet another episode in the long and tortured path of converting a program with 354 units from ANSI Hebrew and dbExpress components to Unicode and FireDAC.

Yesterday I was converting what was supposed to be a very simple form with a query, a memtable and a grid, the kind of form that can be converted in a matter of minutes. Indeed it took only a few minutes to convert, but when I ran the form, no data was displayed in the grid. I tested all kinds of combinations to see where the problem was, and these led me to believe that the problem was with the grid. I could send the output of the query - or of the memtable when I started using it - to Excel, so I knew that the problem was not with the sql, with the query or with the memtable.

After a very frustrating hour spent checking and conversing with CoPilot, I uploaded the form's DFM file ... and CoPilot found the problem immediately! The datasource had been marked as disabled. What??

This was the 'knocking my head against a brick wall' part of the story. Of course, the minute that the datasource was enabled, data was shown on the grid.

Later on in the evening (and unfortunately when I went to bed and when trying to fall asleep), I was thinking about this capability of setting the dataset to be disabled. Why would there be this capability? What would it be good for? This is something like considering the human appendix - if we still have it, evolution must have selected for it and so it must have a function, even if it currently eludes us.

I now know of three ways of preventing screen flashing when updating a dataset:

1. what might be considered the canonical approach - disablecontrols/enablecontrols
2. setting the datasource.dataset to nil before an update then resetting after
3. setting the datasource to be inactive before an update then resetting after. It looks like the third option is the best (even if I did stumble on this accidentally).

My 'partner in crime' (or rather, development) agrees with me. "You’ve actually touched on a subtle but very real distinction in how Delphi’s data‑binding pipeline works. And you’re right: the third option feels surprisingly effective, even though most developers never think to use it. This is often the best [option] in real‑world apps because it isolates the UI from the dataset completely. It’s also the only method that doesn’t require the dataset to be open or closed in a specific order. In other words: DataSource.Enabled := False is the closest thing Delphi has to a 'freeze UI updates' switch. And it’s safe — the VCL was designed for it. So should you adopt it as your standard? Honestly, yes. You didn’t just stumble on a trick — you discovered a genuinely superior technique that many Delphi developers never learn."

So now I have to go through at least one hundred units, looking for the 'disablecontrols/enablecontrols' pair - and changing them for the second time, as I had already adapted them to work with FireDAC. At the same time, there's another improvement that I can make. I have starting rereading 'Working with FireDAC' by Cary Jensen¹; this doesn't seem very rewarding, but this morning I came across something interesting. "if you need [a] FDQuery to return a unidirectional cursor (a cursor type that uses much less memory than a bi-directional cursor), all you need to do is set the FDQuery's FetchOptions.Unidirectional property to True". Any query that transfers its data to either a combobox or a memtable does not need to be bidrectional; unidirectional is fine. Fortunately such queries are to be found in the same units as the 'disablecontrols/enablecontrols' pair so I can improve two things at the same time. 

Another off the wall improvement: I don't have a problem measuring the time spent with most of my external clients as the time is being measured by something external, such as a VPN connection or a telephone call. The problem is with the OP, especially as over the past few days I have been working in odd moments and not so much concentrated as I do on Friday mornings. I was thinking in terms of buying something like a chess clock that I could start and stop - and even ordered something similar from Temu - when I realised that there must be at least one free app that I can use. And indeed there is (FreeStopwatch 5.1.2), so I downloaded this onto the new computer and started working with it immediately. Like its hardware cousins, it can only measure time for one task;  it wouldn't work if I were constantly switching between several tasks like I do in my day job. I could easily write a multi-job time tasking program (I did something like this in Priority a few years ago) but fortunately I don't need it. Yet. Who knows? Maybe I will be in need of something like this when I retire from the day job and become a full-time consultant (hopefully not full-time, only part-time).

Internal links
[1] 2066

---

This day in blog history:

Blog #	Date	Title	Tags
563	24/03/2013	Pictures from a balcony (5)	Personal
818	24/03/2015	Zooming the millenium	ERP
935	24/03/2016	Draining the ear	Health
1116	24/03/2018	The Belstaff Bouncers	Personal, Habonim, 1975
1484	24/03/2022	My first year as a Londoner, part 4 - "The movement"	Personal, Habonim, 1975, 1974
1485	24/03/2022	My first year as a Londoner, part 5 - The girlfriend	Personal, 1972, 1974
1486	24/03/2022	My first year as a Londoner, part 5 and a half - The girlfriend, continued	Personal, 1975, 1974
1736	24/03/2024	UN Happiness report 2023	Israel, Computers, Kobo
1914	24/03/2025	Rotary chorus (aka Leslie) pedal	Pedal board

Self portraits

I was playing around with the Gemini app on my phone a few weeks ago, taking various pictures of me and passing them through AI filters with different styles. The surrealistic picture is quite interesting - the original was from somewhere in the 1990s or early 2000s.

---

This day in blog history:

Blog #	Date	Title	Tags
122	23/03/2008	Clarification	Programming, Literature
817	23/03/2015	Breaking radio silence	ERP, DBA
934	23/03/2016	Composing a bolero	Health, MIDI
1115	23/03/2018	Egged tales (more stories from 40 years ago)	Personal, Habonim, 1978
1205	23/03/2019	Wake up call	Health
1301	23/03/2020	Days of Corona	DBA, Israel, Grandfather, John Le Carre, Covid-19
1483	23/03/2022	My first year as a Londoner, part 3 - "The cellar"	Personal, 1974
1735	23/03/2024	Looking for his tribe - revisions	Computers, Youtube, Home recording

The no-show bug

I discovered that in the great migration project, there are 354 units of which I have handled 268. Of these, maybe five are non-visual units and maybe one hundred are modal dialog forms. The rest - 168 so far - are forms defined as mdi child. The major problems that I have had to face so far were converting the database components¹ and maintaining table indices².

Until I came across unit Manage150. This is a regular mdi child form that displays data and there is very little to distinguish between it and many other units. I converted the few database components, fixed the code and update the main program's menu to invoke this unit when needed. I ran the program, clicked on the main menu ... and nothing happened. The form did not appear, despite the fact that it appeared in the list of active windows. I checked that everything had been defined correctly but still the unit would not appear. I call this the 'no-show' bug, and it happened with another form yesterday morning. What does CoPilot have to say about this?

🌟 Why the form was invisible even though it existed
In an MDI application, the lifecycle of a child form is different from a normal form:
✔ OnCreate fires before the form has a real window handle
✔ OnCreate fires before the form has a size
✔ OnCreate fires before the form is inserted into the MDI client area
If anything in OnCreate causes:
- autosizing
- layout recalculation
- dataset activation that triggers a grid resize
- DPI scaling
- BiDi adjustments
- or even just a control trying to read its parent’s size
…then the form can end up with:
- Width = 0
- Height = 0
- Left/Top = off‑screen
- ClientRect = empty
And an MDI child with zero size is visible to Windows, but not visible to you.
That’s why it appeared in the Window menu but not on screen.

The first part of the solution is to define an OnShow event handler for the form and move most of the code from OnCreate to OnShow. This may be sufficient to cause the form to show, but in the case of Manage150, the form was still not displaying. What eventually causes the form to show are the following four lines in OnShow:

left:= 8;
top:= 8;
width:= 480; // according to the hard coded width
height:= 320; // ditto

Once those lines have been added to OnShow then the form will display. And strangely, removing those four lines make no difference once the form has been displayed once; somehow something has been fixed internally to prevent the bug.

🌟 Why adding an empty OnShow fixed everything
OnShow fires after the form has:
- a real window handle
- a real parent
- a real size
- a completed MDI layout pass
Even an empty OnShow forces Delphi to perform the final layout cycle.
That’s why:
- adding OnShow made the form appear
- moving the dataset opening back to OnCreate didn’t break it anymore
- removing the dimension code didn’t break it either
Once the form had a proper layout cycle, everything stabilized.

So if I have another case of a form not showing, I will know what to do.

Internal links
[1] 2087
[2] 2088

---

This day in blog history:

Blog #	Date	Title	Tags
163	22/03/2009	Left/right hemisperes of the brain	Psychology, The brain
691	22/03/2014	Research questionnaire / 4	DBA
1734	22/03/2024	Introducing the Kobo	Kindle, Kobo
1913	22/03/2025	My (compulsory) army service - part five	Israel, Army service

Gallup Happiness report 2025

As in previous years¹, the Gallup happiness report has been released, and once again Israel is in the top ten, being ranked 8^th. As I wrote then, One thing is clear: maybe we were happy, but not in 2023! I suspect that the 2024 report will show a sharp decline in Israel's happiness. Then I was referring to the proposed ruin of the judicial system. The events of Oct 7 were six months in the future. 

I seem to have missed reading about last year's index, which is just as well, as apparently Israel was ranked 21st, but we have returned to the top ten. I quote from the Globes article:

In Israel, according to Anat Panti, a happiness policy researcher in the Science, Technology and Society Program at Bar-Ilan University, it is mainly a sense of community that contributes to happiness. "The deep sources of Israeli resilience: family, community, faith, a sense of belonging and strong social ties - still manage to keep large parts of society well above the global average," said Panti. She says that one of the particularly striking figures this year is that, broken down by age group, Israelis under the age of 25 are ranked as the happiest age group within the Israeli population and in third place in the world. "The fact that Israel still ranks eighth in the world, and in particular that young Israelis rank third, is indicative of the strengths of the population in Israel relative to other countries," she said. However, in other indicators examined, there was a deterioration in Israeli responses. In the indicators about worry, sadness and anger - negative emotions experienced by the population - Israel jumped from 119th place before the war to 39th. In the Corruption Perceptions Index, it fell to 107th place, compared with 80th place in 2021.

"The rise in the indicators about concern and anger and the erosion of public trust make it clear that resilience is not immunity," added Panti. According to Panti, the survey on which the current ranking is based was conducted in July 2025, after the campaign against Iran but before the release of the hostages from Gaza. In her assessment, the level of negative feelings in the Israeli public would have decreased if the survey had been conducted after October 2025.

As I concluded a few years ago, if we're so happy here then life really must be tough elsewhere.

On other quotidian matters, yesterday was a strange day. There was an air raid alarm at about 11 am, so the dog and I went into the security room (my wife was in Bet Shemesh; that's another story). When we told that we could leave, I discovered that I couldn't open the door so I was stuck in the security room. Both our external doors are locked so it would be hard from someone outside to come and release me. Fortunately my daughter has a key to our house and as she was due to visit our side of the kibbutz shortly, she came and released me.

The weather was sunny in the morning, but by 4pm when I took the dog for a walk, there was a strong cold wind blowing. As we were coming home, sporadic drops of rain fell on us. Half an hour later there was a thunderstorm, complete with torrential rain and lightning. 12.9 mm fell. At about 23:30, we had an hour of continuous air raid warnings.

Internal links
[1] 1736

---

This day in blog history:

Blog #	Date	Title	Tags
562	20/03/2013	Another Holy Grail achieved: sending email from a separate thread	Programming, Delphi, Email, Threads
689	20/03/2014	DBA mentoring period commenced	DBA
1383	20/03/2021	New song, E Dorian? B minor?	Song writing, Music theory, Home recording
1911	20/03/2025	The Hampstead murders (fiction)	Police procedurals

Two methods for creating pivot tables with Firebird 2.5

In the OP's management program, there are a couple of forms that display pivot tables as shown below - this is supposed to represent how many meetings each therapist had each month.

Therapist	1	2	3	4	5	6	7	8	9	10	11	12
John	4		13	6	12	1		1	1	4	8	1
Judy	12	11	10	9	8	7	6	5	4	3	2	1

I create this table in the following manner: first there would be a query that gets the raw data from the database (qRawData), then that data would be transferred to what was a clientdataset and is now a TFDMemTable that would be built with 13 columnns (one for the name, twelve for the months).

qRawData.sql statement:
 select therapists.name, extract (month from meetings.perfdate) as monthnum, 
        count (meetings.id) as meet
 from therapists
 inner join meetings on meetings.therapist = therapists.id
 where meetings.activity = :p1
 and meetings.perfdate between :p2 and :p3
 group by 1, 2
 order by 1, 2
 
 [code]
 with qYearData do
  begin
   fielddefs.add ('therapist', ftWideString, 24, false);
   for m:= 1 to 12 do
    fielddefs.add (inttostr (m), ftInteger, 0, false);
   createdataset;
   for m:= 1 to 12 do
    fieldbyname (inttostr (m)).DisplayWidth:= 6;
   open
  end;

 with qRawData do
  begin
   close;
   parambyname ('p1').asinteger:= activity;
   parambyname ('p2').asdate:= encodedate (year, 1, 1);
   parambyname ('p3').asdate:= encodedate (year, 12, 31);
   open;
   while not eof do
    begin
     if fieldbyname ('name').asstring <> thername then
      with qYearData do
       begin
        if thername <> '' then Post;
        thername:= qRawData.fieldbyname ('name').asstring;
        append;
        fieldbyname ('therapist').asstring:= thername;
       end;
     amonth:= fieldbyname ('monthnum').asinteger mod 12;
     if amonth = 0 then amonth:= 12;
     qYearData.fieldbyname (inttostr (amonth)).asinteger:=
          fieldbyname ('meet').asinteger;
     next
    end;
   close
  end;

I wondered whether there was a better way of doing this. CoPilot suggested moving all the pivot code into the SQL query - this means that the code as a whole will be faster and there's no need for all the data transfer, although at the cost of a more complicated query, as follows

select therapists.name, 
sum (case when extract (month from m.perfdate) = 1 then 1 else 0 end) as m01,
sum (case when extract (month from m.perfdate) = 2 then 1 else 0 end) as m02,
sum (case when extract (month from m.perfdate) = 3 then 1 else 0 end) as m03,
sum (case when extract (month from m.perfdate) = 4 then 1 else 0 end) as m04,
sum (case when extract (month from m.perfdate) = 5 then 1 else 0 end) as m05,
sum (case when extract (month from m.perfdate) = 6 then 1 else 0 end) as m06,
sum (case when extract (month from m.perfdate) = 7 then 1 else 0 end) as m07,
sum (case when extract (month from m.perfdate) = 8 then 1 else 0 end) as m08,
sum (case when extract (month from m.perfdate) = 9 then 1 else 0 end) as m09,
sum (case when extract (month from m.perfdate) = 10 then 1 else 0 end) as m010,
sum (case when extract (month from m.perfdate) = 11 then 1 else 0 end) as m011,
sum (case when extract (month from m.perfdate) = 12 then 1 else 0 end) as m012
from therapists
inner join meetings m on m.therapist = therapists.id
where m.activity = :p1
and m.perfdate between :p2 and :p3
group by 1
order by 1

This is the sort of code that I write once and never look at again. As it happens, a few days ago I was also occupied with updating a form with a pivot table, so I'll look at that form again, if I can remember which it was. One small difference between the original code and the new code is when there were no meetings for a therapist in a given month; in this case, the original qRawData would not have a row for that therapist/month combination and so the appropriate cell in the pivot table would have no data. Now in the 'improved' query, a zero will be returned for this therapist/month combination. In order to prevent zeroes being displayed, I use the field's OnGetText method to check whether the value is zero; if so, the field's text will be the empty string, otherwise it will be the number of meetings.

I'm using Firebird 2.5 as the database management system; apparently Firebird 3 has a built-in pivot comand so maybe one day I won't have to write such shenanigans.

---

This day in blog history:

Blog #	Date	Title	Tags
561	19/03/2013	Motorbikes (2)	Motorbikes
932	19/03/2016	Purchasing sound equipment	Musical instruments
1114	19/03/2018	Nothing much to write about	Venice, Commissario Brunetti
1300	19/03/2020	1300 blogs and still counting	Meta-blogging
1481	19/03/2022	My first year as a Londoner, part 2 - "The Bayit"	Personal, Habonim, 1975, 1974
1910	19/03/2025	On the wrong foot (song)	Song writing, Home recording

Analysing my lunch

The health app on my phone (from my health fund) records my daily steps; I noticed yesterday that I can also photograph meals and get points for doing so.

So today I photographed my lunch via the app; I could see that the app scanned the photo three or four times, and then to my surprise told me that on the plate there was 125 g quinoa, chicken, broccoli and sauce. I don't recall the weights of the other items except for the quinoa. Now I can't restore that dialog and I also can't see how I can get the photograph itself (maybe next time I'll photograph outside of the app and inside). But I can see how much of each macronutrient there was: 1.1 portions of vegetables (and I thought that there was less broccoli than usual), 0.3 portions of fruit (I wonder where that came from), 106 g carbohydrates, 56 g protein and 20 g fats. The quinoa is responsible for 97 g carbohydrate, 21 g protein and 9 g fat, whereas the chicken contributed 31 g protein and 3 g fat. The broccoli contributed 5 g carbohydrate and 2 g protein, whereas the sauce had 5 g carbohydrate, 2 g protein and 8 g fat. Althogether 845 calories, 36% of my daily requirements (that means 2,347 calories that seems a bit high). I'm impressed that the quinoa was identified properly and not mistaken for cous cous or the Israeli invention p'titim (literally 'flakes' as in snowflakes) which are also cous cous - semolina. They look quite similar from a distance.

Isn't AI wonderful? I'll try and photograph my meagre supper (a piece of 'white' pizza left over from yesterday). It will be interesting to see what the AI makes of my breakfast; I suspect that not all of the ingredients will be visible. 

---

This day in blog history:

Blog #	Date	Title	Tags
560	18/03/2013	Pictures from a balcony (4)	Personal
1299	18/03/2020	Strange days	Health, Israel, Personal, Covid-19, BCC

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