Thursday, July 30, 2009

Speed III

Carrying on from yesterday: I converted the specific module to emit HTML stored in a file. After the report is finished, Word opens the HTML file. The conversion was surprisingly easy; the main thing which I was worried about was creating the main table in the report which has a slightly irregular structure. The 'colspan' command helped here, and in fact, it turned out to be much easier to create a table in HTML than it is in Word!

After completing everything and debugging so that the program worked, I checked its performance; it's maybe only 10% faster than Word and definitely not a huge improvement. So the plan is as follows:
  1. Turn the new code into a thread
  2. In the main program, when the user presses on the button which executes this report, the button is deactivated.
  3. When the thread finishes, it has to notify the main program, and will set a flag saying that there is an HTML file waiting to be displayed.
  4. Check whether it is safe to run Word, and if so, do so and read the file. At the same time, clear the 'pending' flag and enable the button.
This reminds me of programming TSRs in assembly language 20 years ago: is it safe to pop-up? Set flags to show that the pop-up is waiting.

When is it safe to run Word? I am going to take a very conservative view, and say that it is unsafe any time a module in the program is run. This will be very easy to implement, as every module is activated by pressing a button on the main screen; I simply add 'wordsafe:= false' in the button's handler before the module is displayed, and 'wordsafe:= true' after the module closes. At the same time, a check should be made to see whether there is a file pending, and if so, display it.

Wednesday, July 29, 2009

Speed, speed, speed/2

It occurred to me yesterday afternoon that a better strategy would be to emit HTML code, and then read that file into Word. This way, I get the advantage of using a standard template, and I also don't have to worry about pagination. I probably won't have to use a thread, either.

I wrote a short program yesterday evening which emits some HTML code in Hebrew, including building a table, and then imported it into Word. Apart from the fact that the table looks different from a table built in Word, everything was ok (or to use a good word, copacetic).

All I need now is the time to convert my code to emit HTML.

Tuesday, July 28, 2009

Speed, speed, speed

As is apparent from my previous blogs about Word Automation, one of the applications which I wrote/develop/support uses WA to write a complicated report. Unfortunately, as the complication of the data rises, so the report takes longer (this isn't because of the WA), so much so that running this report on the client's computer was taking several minutes. I have tried to speed up the report as much as possible, by using techniques such as precalculating values and moving expensive operations (such as SQL selects) out of loops, but there is a limit to how much one can optimise.

Jon Bentley devotes a chapter in his seminal book "Programming Pearls" to optimising, and points out that the kind of optimisation which I was performing can only save a certain amount of time. In order to get big savings in time, one has to change the algorithm. Whilst I can't see how the algorithm can be changed (it's a question of get these data and print them, then get other data and print them), I started thinking of other ways of printing. This led me to consider the option of using a separate thread to print the document. I have never used threads before, but I found an article which demystified them.

I spent most of Thursday evening playing with this thread code. Fortunately, it explains how to make thread-safe calls to the Borland Database Engine (BDE), so I didn't have any problems there, although the lack of a main window meant that I had to write out the sql code manually. Calling Word from the thread worked, and it seemed that I had found a solution to the speed problem. In another life, I cynically call this 'good management' - let someone else worry about the problem. Here, instead of my program worrying about printing the document, a separate thread could do the work, and free my program to the user so that more examinees could be inputted.

I tested the new program extensively, once I had got all the obvious bugs removed, and was dismayed to find that the program would fail frequently but unpredictably. I came to the sad conclusion that Word was not thread-safe, and that all my attempts were for naught.

Today I was reading another of Joel Spolsky's blogs, this time on Office formats. A comment at the end hinted at a new solution:
Use a simpler format for writing files. If you merely have to produce Office documents programmatically, there’s almost always a better format than the Office binary formats that you can use which Word and Excel will open happily, without missing a beat.

In my application, I don't write a Word file, but instead use Word as an automation server. What if I used another program as an automation server? What if I outputted a file with the correct format as HTML, loaded that file in a browser and then printed it? Fortunately straight away I found a snippet of code which shows how to operate IE as a server, and only a few minutes later I had written a test program which worked.

I only have two problems left:
1. Closing IE after printing seems to cause the file not to print; somehow I must add a delay before closing IE.
2. I have to convert my program's output to HTML. Whilst this is going to be extremely tedious, it's not going to be too difficult. Whilst I know how to write code in HTML which produces a table, I don't yet know how to shadow one or more columns.

I'll give this solution an hour or two's work and see how I get on.

Monday, July 27, 2009


As of yesterday, I am a fully paid-up student in an MBA programme which is being run at Ramat Gan college. The degree is awarded by Heriot-Watt University, or the Edinburgh School of Business.

This is a decision which has been fermenting in my mind for the past few months. Originally, I was considering studying for a degree in psychology from the Open University, but I became dissuaded after reading "Psychology for dummies" (maybe not an ideal textbook, but at least something which would give me an idea of what I want). This led me to believe that I was more interested in industrial psychology, but I was unable to find any introductory texts on the subject. Then I considered a degree in Business and Management, again through the Open University. Eventually I received the prospectus from the Israeli OU, along with the costs. I would need to take something like 20 courses, and although the cost would "only" be about 40,000 NIS (6,300 pounds sterling at current exchange rates), it would take forever (close on ten years, assuming that I only take one course per semester).

I happened to mention this to the occupational psychologist with whom I work, and she suggested that I investigate the possibility of a master's degree as opposed to a bachelor's. This led me to checking various MBA programmes in Israel and abroad. Most of these fell at the first stage whilst checking their requirements and costs. The Ramat Gan college didn't give much information either, but they invited me for a chat; once there, I was given full explanations.

As most MBA students are in full-time employment, such courses are generally run on the weekend (most Israelis don't work on Fridays) or in the evenings. Thus one can work and learn without one interrupting the other.

Assuming that I pass every course first time, the course will cost a tad over 50,000 NIS, but some of that is college fees, as opposed to tuition. The Hebrew University wanted 99,000 NIS, not including the cost of a ticket to New York and a week's board at a hotel there (part of their programme is a week's lectures in New York). Right. Other advantages of this programme are the waiving of any entry requirements (save a first degree - so no need to prepare for and sit GMAT exams) and the fact that all the teaching material is in both Hebrew and English. Whilst I have no problem sitting through lectures in Hebrew, I still find it easier to read technical material in English. Attendance at lectures are optional (although of course recommended) and there is no homework. What could be easier? Well, there are still exams to pass....

Intriguingly, the exam papers are both in English and in Hebrew; I have yet to choose in which language I will respond.

I have decided to start with courses whose material I already know; this way I can dip my toe into the sea of academia gently after a long absence, rather than plunge directly into the unknown. Thus I am starting with Accounting; judging by the syllabus, I should have no problems with this, as the subjects listed are items with which I duel daily [today I spent most of the day working on new reports for slow moving and dead inventory]. Then I'll do Organisation Behaviour in a shortened, winter semester and finish the year with Economics (I did a course in micro-economics many years ago with the OU - nothing much has changed since then).

One has to pass seven compulsory courses and two elective courses in order to obtain the degree. If I take one course a semester, then I will be finished in three years. Once I get the hang of things, I may be able timewise to take more than one a semester, but I doubt it.

As it happens, I was reading last week the blog of Joel Spolsky; although most of the time he writes about running a software company, and writing a selling product, he also devotes some time to management issues which I found illuminating. There was even a blog about economics, which was very apposite. I notice that in the past few weeks I have been looking at everything with an MBA's eye.

Opening day is Friday, 7 August. Only 11 days to go....

Saturday, July 25, 2009

Better late than never - II

As someone wrote on a music mailing list, the fact that he likes no current music allows him the time and money to purchase and listen to all the music that he missed earlier. I've been following his advice, and bought two discs recently, both of which date from another era.

The earlier of the two is "The best of Peter Green's Fleetwood Mac". As can be inferred from the title, this collection is from the earliest line-ups of the band, before they went American. When Peter was in the band, they were basically a blues group, and most of the tracks on this disc are in this style. The exceptions - and the reasons why I bought the disc in the first place - are the singles: Albatross, Black magic woman, Green Manalishi, Man of the world, Oh Well. I never listened to the blues when I was growing up; this was a kind of music which hit its zenith in Britain in the mid-60s, and was already dying by 1969, approximately the year of this collection. I eschewed the blues changes as much as possible, a direction probably influenced by Richard Thompson, Robert Fripp, Peter Hammill, etc (in other words, all the musicians to whom I listened from 1970 onwards).

There is one track on this disc which reminded me slightly of Blodwyn Pig, who were, I suppose, a second generation blues band who were stretching the form by adding jazz and rock influences. BP were the first underground band that I saw, certainly the first that I liked and their's was the first rock record that I bought. It's interesting to note how much distance they had put between themselves and Fleetwood Mac, even though chronologically they were separated by a year at most. Maybe Peter Green too was heading out of the ghetto, an impression which is supported by "Oh Well"; unfortunately his health unravelled and he had to leave the band he founded before he could take it to new pastures.

Fast forward by two years, and it's time for Shirley Collins' "No Roses" album. I had a friend who bought the vinyl album at the time of its release, so I've been familiar with this for years. NR was Ashley Hutchings' first project after leaving Steeleye Span, and only two years after "Liege and Lief". What can I say? Not as groundbreaking as L&L, not as influential, but certainly more varied, and ultimately more interesting. Amazon were selling it for three pounds; the postage cost more than the disc. A hole in my collection and one which needed filling.

Serenpiditiously, this morning appeared an email on a music group listing about a Fairport Convention torrent from 1982. When I looked for this, I also found a television program about the Albion Band from 1979, featuring several snippets of interview with AH himself, several songs (including one with Shirley Collins) and a cut-up interview with Richard Thompson. I was back in the folk-rock belt, a place where I spent the seventies, and curiously, the nineties.

Saturday, July 11, 2009

Treeview program manager

A few weeks ago, I was looking at the screen of the computer belonging to the occupational psychologist for whom I write programs. The screen was extremely cluttered with icons, in main due to the fact that there were icons for each of the program suites which I have written for her. With ten different suites and two or three icons for each suite, that works out to a lot of icons! I idly suggested writing a program which would manage all of those programs, leaving her with only one icon on the screen. Basically, I was advocating a return to something similar to Windows 3.1 with its program groups.

There are several ways in which I could have implemented this; in the end, I chose an implementation based on the treeview control. This has four levels:
  • 0 - the top level, always 'programs'
  • 1 - the program group (for example, 'Adjectives')
  • 2 - the type of program (administrator, exam, results)
  • 3 - the path to the program
It would make things easier if I showed a screen shot of the program. Naturally, almost all of the data are in Hebrew, but as they say, one picture is worth a thousand words.

The program loaded its data via the treeview's 'loadfromfile' method, and of course saved its data with the 'savetofile' method. As opposed to most of my programs, this one has a sizeable main window (the treeview automatically resizes itself to fill the entire area), so I decided to add code which remembers the window's size. This data is stored in the registry. A neat hack.
// code to load a window's size from the registry
ini:= TRegIniFile.create ('\software\nbn');
with ini do
  self.Top:= ReadInteger (prog, 'Top', -1);
  self.Left:= ReadInteger (prog, 'Left', -1);
  self.Height:= ReadInteger (prog, 'Height', -1);
  self.Width:= ReadInteger (prog, 'Width', -1);
Yesterday, the psychologist asked whether nodes could appear in different colours; this would make it easier for her secretary to chose the correct programs. I did a certain amount of research via the Internet looking for Delphi code to do this, and it became apparent that I would need to use the 'CustomDrawItem' of the treeview. After a little more work, I discovered that I had already used this method in another program of mine and that it was very simple to do what was necessary. It's sometimes amazing to see how simple Windows code can be in Delphi which seems to be so complicated in other languages:
procedure TMainForm.TVCustomDrawItem(Sender: TCustomTreeView;
Node: TTreeNode; State: TCustomDrawState; var DefaultDraw: Boolean);
 with sender.Canvas do
   case longint ( of
    1: font.color:= clBlue;
    2: font.color:= clGreen;
    3: font.color:= clRed;
    0: font.color:= clBlack

  if node.level = 1
   then [fsbold]
   else []
After successfully displaying node text in colour, I had to decide how the colour of each node would be represented, and how this colour would be stored between invocations. At the moment, the program only recognises three colours (apart from black), and the index to these colours is stored in each node's 'data' property. The value can be chosen in the 'edit program' dialog box which appears whenever the user double clicks on a program title (level 2; double clicking on a node at level 3 will execute the program whose path is displayed). I quickly established that the 'savetofile' method does not save the 'data' property, meaning that I would have to write code to save the data, and then similar code to load the data.

The code to save the data was actually very simple: an ordinary text file is created, and for each node in the tree a line is written, consisting of the node's level, its text and its data, represented as an integer, where null data is equivalent to 0.
assignfile (f, datfilename);
rewrite (f);
for i:= 1 to tv.Items.count do
with tv.items[i-1] do
writeln (f, level, ',', text, ',', longint (data));
writeln (f, '*** end of file'); // dummy line
closefile (f);
Reading in the file and building the tree structure was a bit more difficult. The key to building the tree is to remember that the parent of a node at level 0 will be 'nil' - there is no parent, as this is the top node. The parent of a node at level 1 will be the node at level 0 (which will always be tv.items[0]). Following a node at level 1 will be its sons, and following each son node will be its son nodes; thus all that is necessary is to save a pointer to each node being inserted, and use this node as the father for the next nodes. Nodes at level 2 also have to have their colour restored.
datfilename:= extractfilepath (application.exename) + 'progman.dat';
assignfile (f, datfilename);
reset (f);
if ioresult = 0 then
  readln (f, line);
  while not eof (f) do
    i:= pos (',', line);
    level:= strtoint (copy (line, 1, i-1));
    line:= copy (line, i + 1, length (line) - i);
    i:= pos (',', line);
    case level of
     0: tv.Items.AddChild (nil, copy (line, 1, i-1));
     1: node:= tv.Items.AddChild (tv.items[0], copy (line, 1, i-1));
     2: pnode:= tv.Items.AddChildObject (node, copy (line, 1, i-1),
                                             pointer (strtoint (copy (line, i + 1, length (line) - i))));
     3: tv.Items.addchild (pnode, copy (line, 1, i-1));
   readln (f, line)
  closefile (f);
    tv.loadfromfile (extractfilepath (application.exename) + 'progman.txt')
    except on e: exception do
    tv.Items.AddChild (nil, 'Programs');
There is a slight bug in this code: eof (end of file) will be true after the last line of the file has been read, but that line won't get processed because the code exits the loop. Instead of adding duplicate code to process that final line, I decided to add a dummy line to the file which will allow the final line of data to be processed whilst not being processed itself. This is called the 'sentinel' technique.

If the above code can't find its data file (which will be called 'progman.dat'), it looks for a file called 'progman.txt', which was the data file from the previous version of the program. If this file is found, then the tree will be built, albeit with no colours. If this file isn't found, then the program creates a new tree with a dummy first node.