Executing a program and waiting for it to finish

At the beginning of the month, I wrote "The next stage in the process is to convert the xlsx file (after completion) to a tab delimited file by means of the increasingly valuable program which I wrote with Delphi 10. This turns out to be much harder than expected - a topic for another blog entry."

Here is that second blog entry. In the past, I have used the ShellExecute procedure in Windows in order to execute a program in the context of another program; here is some sample code (taken from a 'program launcher'):

chdir (extractfiledir (path));
cmd:= ini.ReadString (key, 'Cmdline', '') + ' ' + pdata + #0;
ShellExecute (handle, 'open', pchar (path), pchar (cmd), nil, 
              sw_show)

Whilst this procedure does what it is supposed to do, the executing program has no way of knowing when the child program finishes. Sometimes this is not a problem, but sometimes it is. In the context of converting an xlsx file to tab delimited, it is very important to know when the child program has completed.

There is a second procedure in Windows called ShellExecuteEx which both executes the child program and also waits for it to finish. I am sure that I used this procedure once in a program, but I couldn't find the code and so had to rewrite it from scratch. Unfortunately, some of the examples which I looked at were misleading, so it took me some time to figure out exactly what had to be done. Here is the code which executes the converter program:

var
 exInfo: TShellExecuteInfo;
 exitcode: DWORD;
 prog: string;

begin
 With exInfo Do
  begin
   cbSize:= Sizeof (exInfo); 
   fMask:= SEE_MASK_NOCLOSEPROCESS or SEE_MASK_FLAG_DDEWAIT;
   Wnd:= aHandle;
   lpVerb:= 'open';
   lpFile:= pchar (extractfilename (prog));
   prog:= extractfiledir (prog);
   if pos (' ', prog) > 0 then prog:= '"' + prog + '"';
   lpDirectory:= pchar (prog);
   lpParameters:= pchar ('"' + f1 + '" "' + f2 + '" ' + flags);
   nShow:= SW_HIDE;
  end;

 if ShellExecuteEx (@exInfo) then
  begin
   while GetExitCodeProcess (exinfo.hProcess, exitcode)
         and (exitcode = STILL_ACTIVE) do Sleep (500);
   CloseHandle (exinfo.hProcess );
  end;

I discovered that if the program to be executed has a space in its name (or more likely, in the directory name), then one has to put quotation marks around the name. Similarly, the parameters have to be protected by quotation marks. Since having written this code, it works very well in two programs of mine which execute the converter program.

This morning I had an epiphany about the above code: in a different program suite, there is a loader program which receives as input data from an examinee along with a list of exams (i.e. programs) that the examinee has to take. Each exam has to signal the loader program when it has been completed in order to cause the next exam to be executed. The loader program uses the ShellExecute method, which solves half of the problem; the second half of the problem (notification) is achieved by means of a complicated system of inter-program messages. Sometimes there are bugs with this method. It occurred to me this morning that I can drop all the message creating and receiving code: the launcher can use ShellExecuteEx in order to execute each exam, and of course, the launcher will receive a reliable message when the child program has completed. The child program doesn't even have to know that it is being run by the launcher, which simplifies a few things.

Intermediate thesis accepted!!

Following is the text of a letter which I received from the university today.

Dear No’am

I trust you are well.

The Doctoral Review Committee considered your intermediate thesis at their meeting this morning. I am delighted to inform you that they consider it to be of a very good standard and are happy for you to proceed to the main study.

You will soon receive detailed feedback from the reviewers via your supervisor. You are encouraged to discuss the feedback with your supervisor and address any issues to your supervisor’s satisfaction before progressing to the next stage.

Obviously it's a good idea to have undertaken a failed doctoral project before embarking on a successful one. The first time around, it took me eight months and one and a half attempts to get my research proposal accepted; second time, it took about three months, fighting my mentor almost all the way, to have the proposal accepted at its first meeting. The first time, it took about two years and two attempts to have the intermediate thesis accepted; second time, it took exactly one year and one attempt!

Of course, it's not such a good idea to have a failed project as it ate time and money which could have been put to better purposes. On the other hand, the experience gained from failing allowed me to know in advance what I needed to know in order to succeed. 

So what lies ahead? First, I have to see the detailed feedback - I suspect that the paragraph about feedback in the letter is boilerplate. Assuming that there is nothing serious there, the next step is to start the interviewing process. At the same time, I have to discuss how results will be presented in the thesis. It would have been much clearer how to present results in a thesis based on statistics which prove or disprove hypotheses.

E Dorian

Throughout October, I was toying with a chord sequence which I developed, trying to turn it into a song. I think that the fact that I was toying or dabbling means that I wasn't very happy with it. About a week ago, I sat at the piano and played the sequence once again, trying to find a tune which would work; after knocking around for a bit, I discovered that I was playing a song written by someone else (I don't recall now which song, not that it matters) and that the rhythm had changed from 4/4 to 6/8. I started to sequence this variation, but didn't get very far.

After a few days cogitation, I decided to start afresh, keeping only an instrumental portion from the song. I decided arbitrarily that the song would use the E Dorian scale (E F# G A B C# D), and unusually for me, all the chords used (at least, in the verse) are based on this scale: Em F#m G A Bm and D. I quickly developed a 15 bar sequence for the verse and even built a complete arrangement. 

As someone noted on the Music Practice and Theory stack exchange site, this scale doesn't really have the concept of perfect cadences (e.g. B -> Em); the normal cadence for the Dorian mode is D -> Em. Thinking about this, I decided to reverse the chords in the last three bars of the verse, from Em D A to A D Em, as this would have a better 'cadential' feel. This meant that I had to change several bars in the arrangement. Sitting down once more at the piano in order to develop a tune, I realised that I needed a 16th bar to the verse; back to the sequencer in order to add this bar to every verse, and then restructure the drum loops. The tune may be a bit too high for me to sing comfortably: it ranges from D above middle C to C# an octave higher. Lowering the key by two or three semitones would bring it into my comfort zone, although I have reached that high C# without help in the past.

That's all behind me now. The song - or rather, the arrangement - is now complete, at least until I have some more ideas. All that's left to do is write the words. I haven't given this much thought as I was waiting for the melody to settle down. The current idea is to call the song 'Wide awake', where the lyrics are something like 'Wide awake at five past one, the daily grind has just begun; wide awake at ten past two, ......'. I don't know whether those words even fit the new rhythm and I'm unsure as to whether this is a topic which will maintain an entire song.

Improving a solution

I thought that I had written about a problem which I had with loading external files containing Hebrew into Priority; the Hebrew text wasn't being reversed when read by Priority (or rather, it was being reversed but not unreversed). One day, I realised that I could write a program which would reverse the Hebrew in advance (this would have the Hebrew left to right). Once read into Priority, the left to right Hebrew would then be corrected to right to left, which is the way it should be.

The original data flow was:

1. Copy the external file to a special directory, changing its name (this is so the interface program in Priority can find its input)
2. Read this file into a holding area within Priority, very basic structure
3. Move the data from the holding area into another holding area with better defined structure
4. Convert the data in the holding area to the customer order format.

The problem with the Hebrew was in the first stage: the basic holding area had reversed Hebrew strings. The original program - which stopped working - corrected the data in this holding area, between stages 2 and 3. As I am not able to write a program which accesses data within Priority tables, this option was denied to me, so I decided to attack the first stage: my program ('RevHebrew') altered the original external file prior to its being read into Priority.

At some stage, I realised that I only need create a backup file if RevHebrew actually reversed anything in the file; this cut the number of backup files by at least 50%.

A few days ago, it occurred to me that I could simplify matters if I used RevHebrew at a later stage. I wrote a new version of the program which didn't save its original file with a new extension (this required removing exactly one line from the original program) and set this to work on the file which has been copied to the special directory. 

The benefits of this are two-fold: there is no 'original backup' file, and the file which is backed up by the Priority program has the Hebrew in its correct form, not in reversed form. Thus if we ever need to input the file again (which I sometimes do in order to check where a problem occurred with a file's contents), there won't be a problem with the Hebrew.

The programming house who were supposed to check the original program and write a replacement had the cheek to send me a bill for work done. I returned this, noting that their 'solution' is not a solution and that the assigned programmer obviously did not understand the problem, judging by his solution. This was only yesterday, so of course I have yet to receive any response from them.

Improving Bill of Materials data in Priority

I have often wanted in Priority that the lines in the Bills of Material (BOM) would show when a given line was added. Looking at the screen which displays this data yesterday, I discovered that there is indeed such a field and that there is a screen trigger which fills that field (and also stores the name of whoever added the line). So why were all our BOMs lacking this data?

I don't know whether the company for which I work is exceptional in this, but I estimate that 99.99% of our BOMs are created by copying previously existing parts. Obviously, changes are made in the BOMs themselves, but again about 99.99% of these are made by a configuration program. It seems as though the copying program and the configurator, both of which are external C programs, directly insert parts into the BOM without using the screen interface. So these lines lack the date they were entered, and by whom.

This is how the COPYPART procedure works: first, the user is asked for the catalogue number of the part which is to be copied. Then an empty screen is presented, in which the user writes the catalogue number(s) of the new part (s) - one can copy an existing part to a new part, or to several parts. Then the copying begins. My code takes advantage of the screen in which the new part numbers are written: these numbers are stored in a table called NPART.

My code runs at the very end of the COPYPART procedure: it loops over all the parts in the NPART table created by the current user and inserts a tuple into the 'extended BOM table', storing the user name and date. PARTARC is the name of the table which stores the BOM data.

DECLARE D1 CURSOR FOR
SELECT PART.PART
FROM NPART, PART
WHERE NPART.PARTNAME = PART.PARTNAME
AND NPART.USER = SQL.USER
AND PART.TYPE = 'P';
OPEN D1;
GOTO 300 WHERE :RETVAL <= 0;  
LABEL 100;
FETCH D1 INTO :NPART;
GOTO 200 WHERE :RETVAL <= 0;
DECLARE D2 CURSOR FOR
SELECT SON, SONACT, ACT, RVFROMDATE
FROM PARTARC
WHERE PART = :NPART;
OPEN D2;
LOOP 100 WHERE :RETVAL <= 0;
LABEL 110;
FETCH D2 INTO :SON, :SONACT, :ACT, :RVDATE;
GOTO 120 WHERE :RETVAL <= 0;
/* This is effectively PARTARC/BUF5 */
INSERT INTO PARTARCA (PART, SON, SONACT, ACT, RVFROMDATE)
VALUES (:NPART, :SON, :SONACT, :ACT, :RVDATE);
UPDATE PARTARCA
SET USER = SQL.USER, UDATE = SQL.DATE
WHERE PART = :NPART
AND SON = :SON
AND SONACT = :SONACT
AND ACT = :ACT
AND RVFROMDATE = :RVDATE;
LOOP 110;
LABEL 120;
CLOSE D2;
LOOP 100;
LABEL 200;
CLOSE D1;
LABEL 300;

The only strange part of this is code which was adapted from the existing screen trigger (PARTARC/BUF5): first an insert is performed into PARTARCA and then the same table is updated. As I understand it, this code first inserts a new record; the insertion could fail if there were no existing record - if someone were editing an existing BOM line. The update naturally updates; this is now guaranteed to work. If there were only an update without an insert, then new lines would not be inserted. Inserting without updating would fail when editing. So this slightly odd approach has been used.

I suppose I could simply have written an insert for my code, as at the time when it executes, there is guaranteed to be no existing line. I've kept it like it is in order not to depart too far from the original.

Intermediate thesis submitted!

Since I last wrote about my thesis, there have been great improvements. I utilised the Succot holiday to improve the research methodology. After this was approved by my advisor, I commenced the pilot study. As I wrote in the thesis:

The pilot study was conducted with a manager responsible inter alia for Priority development, testing the proposed approach and methodology. The objectives of the pilot study were:

• to verify that the questions proposed in section 4.2 are appropriate
• to discover whether there are additional questions which need to be asked
• to determine how much time is needed for an interview
• to determine how much time is needed to analyse the interview
• to determine the suitability of the general structure of the interview.

Originally I reported on the pilot study without any defined structure, but after finding a recent thesis from my department which was accepted, I adopted the structure of the report as appeared in that thesis. I sent this version off to my advisor a few days ago; yesterday evening I received his approval, and so this morning I submitted the thesis to the research committee.

The committee will be sitting towards the end of the month. As with the research proposal, the submission is sent two weeks prior to a meeting of the research committee; two members read the document, make notes and present their findings to the committee which then decides whether to accept or reject the document.

Unlike my first research project, where I adopted the strategy of submitting the first time a version that I think is good but probably not good enough, I have high confidence in this version. As my supervisor wrote yesterday, I think that you and I have spent enough time reviewing this and it now needs some 'external' eyes on it. I think you've really created a document, easy to follow and understand... which is the absolute core skill of a good researcher.

So there's going to be a hold on reporting DBA activity until the end of the month. Ironically, I received an email this morning asking for my quarterly progress report. As my plans for the next quarter depend entirely on the research committee's decision, I think that I will delay sending in the report until the committee's meeting.

Protecting cells in Excel files

Yesterday the OP and I were discussing time sheets: some of her psychologists prefer to fill an Excel worksheet with their hours worked. Looking at this (and bearing in mind recent work of mine in Priority), I said that I could write a program which would input the data from the worksheet into the appropriate table within the OP's management program. Of course, I noted, we would need to have a standard file format, so then I found myself agreeing to write a program which would output such a file.

The first stages in writing the 'calendar' program were very simple; there was a certain amount of 'fun' involving saving the file in xlsx format, but this was swiftly overcome. I realised that I would have to protect the format of the file - I don't want people changing the structure. In order to do this, I would have to protect the cells in two columns (date and day name) whilst leaving the third column (hours worked) editable. I've never done this with an Excel file, let alone with automation, so first I had to learn how to protect the cells, then how to do this within the framework of a Delphi program.

The necessary code appears below:

// allow users to edit only the hours
 sheet.Protection.AllowEditRanges.Add (Title:= 'range1',
                                       range:= sheet.columns['C:C']);
 sheet.protect;
 sheet.Range['C2'].Select;

 // save as xlsx
 XLApp.Workbooks[1].saveas (filename:= copy (s, 1, length (s) - 3) 
                            + 'xlsx',
                            FileFormat:= xlOpenXMLWorkbook);

The final line is required as the Excel file is created by loading a csv file, thus a regular 'save' would save the file as csv, not xlsx.

The next stage in the process is to convert the xlsx file (after completion) to a tab delimited file by means of the increasingly valuable program which I wrote with Delphi 10. This turns out to be much harder than expected - a topic for another blog entry.