Emerging from the morning mist

As today is Saturday, it is my habit to take Mocha the dog for a long, off the leash, walk in the hills behind our house. We started out at about 6:45 in the morning, and as winter seems to have returned to our parts, there wasn't very much light at this hour. On the path leading up to the graveyard, I could see two figures looming out of the mist: one seemed to be the size of a dog, so I assumed that the second figure was the dog's owner.

As I came closer, I became puzzled about these two figures. For a start, they weren't moving at all, and the larger figure didn't seem to be a person - in fact, it didn't seem to have an upper body at all. Was I hallucinating? When I came much closer, I could see that in fact I was looking at a donkey and its foal. Mocha had been interested in some smells arising from the vegetation so she had been behind me, but shortly she came up and started trotting towards the donkeys. They in turn started moving away from us. 

Judging by the route that they took, they probably came from an encampment of Bedouin which is sited a few kilometers from the kibbutz. 

I couldn't get very close for a picture (every time I tried, they retreated), so below is the best that I could do.

Dynamic parametric queries

As I wrote the other day, there is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 rows in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. Although I have sorted out the problem of transferring that data to Excel, there remains the thorny question do I need to display all 4,000 rows every time I display the report? What if I only want receipts which have been issued since 01/03/2013 or cash receipts over 1,000 NIS?

What is needed is some form of preliminary screen with edit boxes which correspond to fields in the report. If the user places a value in an edit box, then the report (or more accurately, the query) has to take this value into account, whereas if the edit box is empty, then it should be ignored.

After some cogitation, I decided to use place a page control component on my form, with two different tab sheets. The first sheet would hold my preliminary, 'parameters', components whereas the second would hold the 'results' grid. I worked up a simple example with two edit boxes whose titles are 'from date' and 'to date'. The parameters sheet also has a 'show' button which builds the query, opens it and displays the results sheet.

My original query was in the form 'select <nine fields> from <some tables>'. The query has no parameters which is why 4,000 rows were being returned. I very naively wrote something like the following pseduocode which is executed when the 'show' button is pressed.

check whether 'fromdate' is empty or holds a valid date
if not, exit
check whether 'tilldate' is empty or holds a valid date
if not exit

whereflag:= false;
copy the original query to string 'newquery'
if 'fromdate' is not empty then
 begin
  if whereflag is false then  
   begin
    whereflag:= true
    append ' where' to newquery
    end
   else append ' and' to newquery
  append ' receipts.curdate ' to newquery

  if 'tilldate' is not empty then
   begin
    append '  between :p1 and :p2' to newquery
    parambyname ('p1').asdate:= strtodate (fromdate)
    parambyname ('p2').asdate:= strtodate (tilldate)
   end
  else
   begin
    append ' >= :p1' to newquery
    parambyname ('p1').asdate:= strtodate (fromdate)
   end
 end
else if 'tilldate' is not empty then
 begin
  if whereflag is false then
   begin
    whereflag:= true
    append ' where' to newquery
   end
  else append ' and' to newquery
  append ' receipts.curdate >= :p2' to newquery
  parambyname ('p2').asdate:= strtodate (tilldate)
 end

This mess did actually work but it was clear that it was far too wordy. As I wanted two paired values (dates and amounts) and two non-paired values (receipt number and payment type), it was clear that the above was not amenable to expansion. My first optimisation was to remove the 'whereflag' variable: it was needed so that I could append 'where' to the standard query for the first new statement, whereas every following statement would use 'and'. I realised that if I added the meaningless statement 'where 1 = 1' to the standard query, then every dynamic statement could start with 'and'.

The second optimisation came when I realised that being too clever and that using 'between' was counter productive: it would be easier to build the dynamic query if I had a separate clause for each edit box. In other words, instead of writing

and receipts.curdate between :p1 and :p2

I could write

and receipts.curdate >= :p1
and receipts.curdate <= :p2

This lead to the third optimisation which was checking all the edit boxes in one loop to see which were empty and which had values. This required setting the 'tag' value of each edit box. Once all these changes had been incorporated, the final code is as follows

procedure TReceiptsList.ShowBtnClick(Sender: TObject);
const
 maxflags = 6;

var
 fdt, tdt: tdatetime;
 i, tag: integer;
 flags: array [1..maxflags] of boolean;
 s: string;

begin
 if (edFromDate.text <> '' ) and 
     not TryStrToDate (edFromDate.text, fdt) then
  begin
   errflag:= true;
   EdFromDate.setfocus
  end
 else if (edTillDate.text <> '' ) and 
          not TryStrToDate (edTillDate.text, tdt) then
  begin
   errflag:= true;
   EdTillDate.setfocus
  end;

 if errflag then
  begin
   laErrText.caption:= date_errtext;
   laErrText.Color:= clWhite;
  end
 else
  begin
   for i:= 0 to componentcount - 1 do
    if components[i] is TLabeledEdit then
     begin
      tag:= TLabeledEdit (components[i]).Tag;
      flags[tag]:= TLabeledEdit (components[i]).text <> ''
     end;
   flags[6]:= rg.ItemIndex > 0;

   s:= '';
   for i:= 0 to querytext.Count - 1 do s:= s + querytext[i];
   for i:= 1 to maxflags do
    if flags[i] then
     case i of
      1: s:= s + ' and receipts.kabnum = :p1';
      2: s:= s + ' and receipts.curdate >= :p2';
      3: s:= s + ' and receipts.curdate <= :p3';
      4: s:= s + ' and receipts.price >= :p4';
      5: s:= s + ' and receipts.price <= :p5';
      6: s:= s + ' and receipts.cash = :p6';
     end;

   with qReceiptsList do
    try
     disablecontrols;
     close;
     sdsReceiptsList.commandtext:= s;
     for i:= 1 to maxflags do
      if flags[i] then
       case i of
        1: sdsReceiptsList.parambyname ('p1').asstring:= edKabnum.Text;
        2: sdsReceiptsList.parambyname ('p2').asdate:= fdt;
        3: sdsReceiptsList.parambyname ('p3').asdate:= tdt;
        4: sdsReceiptsList.parambyname ('p4').asinteger:= 
                                       strtoint (edFromRCP.text);
        5: sdsReceiptsList.parambyname ('p5').asinteger:= 
                                       strtoint (edTillRCP.text);
        6: sdsReceiptsList.parambyname ('p6').asinteger:= rg.itemindex;
       end;
     open;
    finally
     enablecontrols;
     dbgrid1.columns[prevcol].title.font.color:= clRed;
    end;
   pc.tabindex:= 1;
  end;
end;

I think that the loop which adds the conditions to the query should be merged with the loop which set the query parameters. It also occurs to me whilst editing this entry that instead of converting string values to integers (for the receipt amounts), I could pass them as strings and write parambyname ('p5').asstring:= edFromRCP.text, thus saving an unnecessary conversion. Improvements never end.

I should mention that this arrangement of preliminary parameters screen and final results screen works very well. The user can switch back and forth between the screens and issue new queries regarding receipts which return results at the speed of lightning (unless the user ignores all the parameters and requests everything!). There is at least one other screen in the program which will greatly benefit from this technique, but first I have to show it to the OP.

Blogger note: I am now using the <pre> HTML command to preserve code formatting. This saves me a great deal of time and means that I can use angle brackets without fear of them being replaced.

Pictures from a balcony (7)

This is a picture taken from our kitchen window. The kumquats are ripening and turning yellow; soon the birds will be coming and eating them. On the left is the balcony with the bird station barely visible at the end. What is visible is what appears to be a transparent bird; this is actually a plastic bird with a solar LED inside which lights up at night. There used to be two, but one night there was a heavy wind which threw one of the birds onto the ground.

Speeding up Excel

There is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 lines in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. The other day, the OP asked me to add the possibility of outputting the data to Excel.

Whilst I demurred at the need for such a report, it's not my money paying the programmer's cheque, so I quickly wrote the necessary commands using standard Excel automation code. The  first few program runs took so long that I added a progress bar to the form, which enabled me to see that the program was indeed running. I should note that updating a progress bar 4,000 times also takes time, so I changed the code to update the bar once every 32 records.

I started thinking about how I could improve this. My first idea, interestingly, was to put the Excel code into a separate thread, having learnt how to do this with the emails. Whilst this worked, I found that the program was unresponsive, even though the heavy code was in a separate thread. I read a few articles about this on the Internet, which suggested that it was not possible to put Excel in a separate thread. I also had my doubts about the output.

Casting around for new ideas, I discovered code which output files in Excel's native file format. What could be better than this, I wondered. After playing around a bit, I got the code to work on my computer and shortly I was outputting xls files! At first, I continued with the progress bar, but it was clear that this new version was performing much better, so I removed the bar. 

So pleased was I that I considered replacing all the Excel code in the management program with this new library, but a cooler head prevailed. Let me check first that this code works on the OP's computer: even though I checked it with Excel 2003 and Excel 2007, she has Excel 2013 and who knows what surprises await there. Indeed, when I ran the program on her computer, I received an error message stating that the file had been blocked. I tried setting the trust values in her program, but the hard-to-understand Microsoft Hebrew and the fact that I was running her computer by slow remote control defeated me.

I then checked a few facts and discovered that I had been creating what is called BIFF-5 code, which Excel 2013 disdained to read. BIFF-5 was the primary format for Excel 5 (many years extinct); more modern versions prefer BIFF-8 code whereas Excel 2013 prefers BIFF-12 (although it can read BIFF-8). I searched but was unable to find an implementation of BIFF-8 (let alone BIFF-12) in Delphi. I was rapidly losing hope until I remembered something that I had written in a blog entry of mine from three and a half years ago (amazing how much we can forget):

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 the same way that I spent the weekend outputting HTML in order to import it into Word, I could output a file in CSV format (as opposed to BIFF-5) and import that into Excel. I wouldn't need any fancy libraries and could code this very easily. So I did. I also checked that it worked properly with Excel 2013.

I thought it best to take the scientific approach and measure how long it took to activate Excel and pass it the data (either by automation or by file import, including the time needed to create the file). I was not surprised to find that the original automation method required on average 44,420 ticks (about 44 seconds) to handle the 4,000 lines (I thought it took longer). The BIFF-5 code on my computer took a mere 5,300 ticks - 8 times faster! 

So how fast could the csv code be? I couldn't believe my eyes when my measurements showed a mere 530 ticks! I checked that the resulting spreadsheet was correct - it wasn't, I had outputted the file with the 'xls' extension instead of 'csv' so Excel had put everything into one column. That mistake was quickly corrected; when I ran the program again, 563 ticks were required for the correct output. I ran the program four more times with an average execution time of 500 ticks. In other words, this csv code ran 111 times faster than the original code and nearly 11 times faster than the supposedly fast BIFF-5 code!!

Most the Excel code which I write simply outputs values into spreadsheets; after the data has got into the spreadsheet, I set column formats via automation, something which can't be transferred in a csv file. In other words, 99% of the time is spent simply transferring values into the spreadsheet. There are other programs which create fancy graphs so obviously in those cases there is more automation and less data transfer; I imagine that I wouldn't see a hundred fold speed improvement there.

A new CPAP machine

I hinted a few weeks ago that I was commencing the series of events which would lead to me getting a new CPAP machine. For some reason, the health fund insists that one sees a ENT doctor in order to get an appointment with a sleep doctor; this seems to be needless bureaucracy. Once that first and simple hurdle had been cleared, I was pleased to discover that the sleep doctor makes weekly visits to my local town, which means that I didn't have to go to Jerusalem each time. My appointment with the sleep doctor was similarly brief; I told her that I suffer from sleep apnea and was diagnosed five years ago. She signed a paper then sent me to a neighbouring office where a young woman was waiting with several CPAP machines. Her job was to match patients with machines and monitor the results.

So I left the clinic with a new (to me) CPAP machine and more importantly, a new mask. Even though I had told the technician what the settings on my old machine were, she set the machine to determine those settings automatically. The machine starts at a certain pressure: if I told her that 7 cmH₂O was the determined setting, then the machine starts at 10 cmH₂O, then slowly reduces the pressure until a certain rate of apnea are measured. The machine then raises the pressure and the rate of apnea should be reduced. This game continues until the machine establishes what the most effective pressure is. Not surprisingly, this was 7 cmH₂O.

After a week of varying pressure, I returned to the clinic, where the technician reset the machine so that there would be a constant pressure of 7 cmH₂O. I used the machine for another week then returned to the clinic in order to analyse the results. It seems that I had on average 15 apnea per hour during that week; considering that previously I had about 10 apnea per hour, it was clear that this machine was not helping me.

The technician didn't have a better machine with her, but after a few days, another technician (in reality, a salesman) paid me a visit at home and set me up with a different machine which has better diagnostics. After a week with the new machine, the salesman extracted the data and we discovered that I had on average 6 central sleep apnea events per hour and 0.5 obstructive sleep apnea events per hour. I am pleased that the number of apnea per hour decreased substantially but am somewhat surprised that I have CSA and not OSA: CPAP can overcome the latter but not the former.

A small misunderstanding left me for two nights with my old machine and old mask; I get the feeling that I didn't sleep too well during those nights. Then the salesman came and set me up with my new machine. There have been advances in diagnostics and connections in the five years since I got my first machine: that one had an old style serial port socket (I think it's RJ-45: similar to a telephone socket) for which the technician had a lashed-up cable with RJ-45 on one end and a nine pin 'new style' serial plug on the other. I think that the first machine which I tried this year gave its diagnostics via coded read-outs which the technician typed into a program in order to decode them. Certainly she didn't connect the machine to her computer.

The new machine has some form of connector (I don't remember which) but more importantly comes built in with an extractable memory card. Every morning when I turn the machine off, I see a little red light come on while the machine writes its data to the card. According to the salesman, data is written in the form of an Excel spreadsheet. I haven't had a chance to verify this yet as I don't have a card reader. I thought that I would be able to put the card in the mp3 player which I bought a few months ago but this card is much larger than the mp3 player's card.

So I went looking for a card reader. At first I tried Israeli sites, but a google query pointed me to an American site. I bought a reader for the princely sum of $2.50 with free postage to Israel! I hope that it works! I can hardly lose at this price.

I hadn't thought about this before, but that phrase data is written in the form of an Excel spreadsheet intrigues me, seeing as I've spent no small amount of time in the past few days doing exactly that. The gory details will come probably in my next blog.

Pictures from a balcony (6)

More pictures which were previously thought to be lost. My daughter bought a pedigree Boston Terrier puppy about two weeks ago; she brings him over on Friday nights when she comes to visit. Until now, our dog and the puppy have not got on too well: Mocha (our dog) feels that the puppy is invading her territory and also taking our affection, so she frequently growls at the little dog. It doesn't help that the puppy - being a puppy - is full of energy and is constantly running around, examining his foreign environment. As we say, he's like the Duracell bunny - and I wish that my daughter would remove his battery before he visits.

During the Passover holiday, my daughter went away for a few days so she brought her puppy to us so that we could look after him. Both dogs spent most of the time on the balcony where they seemed to have formed some kind of truce. I would take both of them for walks, and it was interested to see how the puppy would follow the adult dog, trying to imitate and learn what the elder dog was doing. These walks didn't faze Mocha very much but the puppy got tired (which was partially the point of the walks).

Those few days were also very hot: we were (and still are, occasionally) experiencing 'sharav' (aka khamsin) weather, which is when a very hot and dry wind blows. These days can be extremely uncomfortable and I tend to be inside (with the air conditioner activated) when a sharav blows. Whilst Mocha seems to spend most of her time laying flat out on the balcony, this was a new experience for the puppy.

Mobile phone problem solved

It's just occurred to me what the problem was with my mobile phone. In USB mode, there are three options: 

• PC Suite - use Nokia PC Suite on your PC
• Printing and media - use your device with a PictBridge compatible printer or with a compatible PC
• Data storage - connect to a PC that does not have Nokia software, and use your device as data storage

Until this episode, I have always connected my phone to the computer at work, where I installed Nokia PC Suite several years ago. Thus the USB setting has always been PC Suite. During the holiday, I tried connecting my phone to my home computer, where there is no PC Suite. Unsurprisingly (in retrospect), the phone complained that the setting was wrong. Unfortunately the message was in terse Hebrew and didn't seem to make much sense. 

I must have played with the settings slightly, for one day I came to work (to fetch something) and tried connecting the phone to the computer. As the USB mode was not set to PC Suite, again I got the terse error message.

The penny has just dropped: I reset the USB mode to PC Suite and connected to my work computer with no problem. Should I ever need to connect the phone to other computers (such as my mobile, which I take with me on holidays, then either I will install PC Suite on the mobile or else I'll change the USB mode of the phone. I think that I'll choose the former option as it is less "panic free" - the phone will connect painlessly and I won't need to remember to change anything.

It's a shame that the girl in the service centre yesterday didn't have the same insight.

Passover hall pictures

I mentioned a few days ago that I seemed to be having problems connecting my mobile phone to computers in order to download pictures that I had taken with the phone. I went yesterday to one of my mobile provider's service stations; I was told that there was nothing wrong with the phone, but that some definition which previously had been automatic now had to be set manually each time I connected the USB cable. I have just looked in the phone's manual and discovered how I can reset the automatic definition.

With that out of the way, I was able to connect the phone to the computer and download pictures. Today's pictures were taken two weeks ago on the afternoon and evening of the Passover festival meal. If you guessed that the hall looks like the sort of place where weddings take place, then you would be correct, as for most of the year, this hall (in fact, a huge tent) hosts wedding parties.

As I mentioned before, the walls are decorated with hand stitched panels depicting scenes from the Passover story.

The next picture show the hall in the afternoon when we were rehearsing the songs: the tables have been laid but there are no people. The final picture shows the celebration in full swing. The decorated walls can be seen at the back of the picture.

A new technique in Word Automation

In the first few months of the year, I spent time creating reports for the Occupational Psychologist in HTML: the reports were intended to be sent by email and this seemed to be the best way of doing this (I probably wrote ad nauseam about doing so). As a result, I managed to restore my HTML skills to the same level that they were about ten years ago. There hasn't really been any need for manual HTML code since, in the same way that there hasn't been any need for manual x86 assembler code. Whilst the latter seems to have gone the way of the dinosaurs, the former can still be useful (indeed, every now and then I manually edit HTML code on this blog; for example, there seems to be no other way of adding a table).

In a totally unconnected fashion, the OP has updated a few of her computers to use Office 2013. I don't know what this version is supposed to bring to the party, but her son was able to obtain licences for a paltry sum ($9?) and so she decided to update. As a result, it seems that certain parts of my Word Automation code don't work as well as they used to; the problem seems mainly to be with tables. In the last few days, there seem to have been all kinds of glitches with tables, making them almost unreadable, and so we needed a new solution.

And so I leveraged my HTML skills. Over the weekend, I spent several hours converting automation code into HTML code; the process is fairly easy but every now and then there are problems. Word allows one to view a table as a two dimensional array of data which is very familiar to programmers (although I find that I hardly ever use arrays any more) and allows one to access cells on a random basis. HTML, by contrast, works in a line by line fashion.

As it happens, the first Word code which I converted to HTML worked in a vertical manner: the Word code would fill in the first column for twenty rows, then the second column for twenty rows, etc. In order to keep my sanity, I declared a two dimensional array within my program, wrote the necessary data on a by column basis, then output it to HTML on a by row basis.

Apart from this extreme example, I also had to deal a few times with sparse arrays: in a row of twelve cells, maybe three have values and the rest are blank. This was easy to deal with using random access code but slightly more difficult with HTML.

I should note that the reports are generated quicker: it is easier to create a text file containing HTML code and then insert that file into a Word document than it is to automate Word.

Here are some examples:

(Word)

wrdTable:= WrdDoc.Tables.Add (wrdsel.Range, 1, 8, 1, 2);
wrdSel.paragraphformat.alignment:= wdAlignParagraphRight;
wrdTable.select;
wrdSel.ParagraphFormat.LineSpacing:= 10;
wrdsel.paragraphformat.characterunitleftindent:= 0;
wrdsel.paragraphformat.characterunitrightindent:= 0;
wrdsel.paragraphformat.lineunitbefore:= 0;
wrdsel.paragraphformat.lineunitafter:= 0;
wrdTable.cell (1, 1).range.text:= 'Scale';
wrdTable.cell (1, 2).range.text:= 'Mark';
wrdTable.cell (1, 3).range.text:= 'Percentile';
wrdTable.cell (1, 4).range.text:= 'Niner*';
wrdTable.cell (1, 5).range.text:= 'Value';
wrdTable.cell (1, 6).range.text:= 'Average';
wrdTable.cell (1, 7).range.text:= 'Std Dev';
wrdTable.cell (1, 8).range.text:= 'Num Items';

(HTML)

with html do
 begin
  add (tabheader + divgrey);
  add (AnsiToUtf8 ('!th!Scale!/th!!th!Mark!/th!!th!Percentile!/th!' +
                               '!th!Niner*!/th!!th!Value!/th!!th!Average!/th!' +
                               '!th!Std Dev!/th!!th!Num Items!/th!!/tr!!/div!'));
end;

As always, I can't actually post the real HTML code here as the Blogger editor interprets my quoted HTML as real HTML (even if using the <pre> tag). The exclamation marks in the above snippet need to be replaced with angle brackets.

There is less fine control with HTML: tables are rendered in some default style. Most of the time, this is not a problem, but here and there I have noticed some functionality which was in the Word program which I cannot reproduce with HTML.

The code is certainly less verbose although I have yet to decide whether this is an advantage. Normally, verbose code is easier to debug and theoretically one can easily get lost in all the HTML tags. One advantage of using HTML is that during development, I can always display what I have coded so far; it is easy to see where tags are missing or code has not been aligned correctly. With Word, the program starts and then crashes whenever the automation is wrong.

I should point out that some reports were a mixture of Word and Excel automation: text would be written, followed by a table, followed by a graph (create by Excel automation, then copied into the Word document). In these cases, I have not touched the Excel automation; most of the Word automation too has been left untouched, and only the tables have been created with HTML (meaning multiple insertions of HTML code).

I await to hear from the OP how the HTML automation is received.

[edit from a few days later: I am chagrined to discovered that I stumbled upon this technique of exporting HTML then reading it into Word several years ago. At the time, the HTML technique seemed to speed things up only by about 10% so I quietly dropped the idea then promptly forgot about it. Today I reread all my blogs about Office Automation and came across this again. Of course, today I have a much more serious problem than speed]

Back to blogging

I see that it's very easy to get into the habit of not writing blog entries....

The Passover festival has passed over - it was one of those weeks when there were two days festival, two days work (except that the factories and offices were closed, so more holiday) then the weekend then another two days festival. The last day of work was on Sunday 24 March and the next day of work was Tuesday 2 April. It was back to work with a vengeance: each day (Tues,Wed, Thur)  had an important - and long - meeting.

So what did I do during this time and why didn't I write? It all boils down to laziness, I suppose; that's not a quality which I normally have, but sometimes lethargy gets the better of me. There were also some very hot days, which I will quote in my defence. I took some pictures of the garden with my mobile phone and some of the hall in which the Passover celebrations took place, only to discover that there seems to be a problem with the phone. I can see the pictures but the phone is not recognised by my computers and so I've been unable to transfer them. Tomorrow I will finally take the phone to be repaired but as the problem seems to be with the phone's memory card, it seems that those pictures will be lost.

I did spend some time - not as much as I would have liked - studying. I very much miss the weekly meetings in Ramat Gan; these very much helped to keep the material in the brain's temporary memory, and of course having a fixed timetable helps those who slack. Now, every time that I pick up the file, I have to spend a certain amount of time just getting into the correct mental space so that I can continue. I have found some interesting papers which impinge on my subject. I read one doctoral thesis which examined the advantages of Microsoft .NET as an implementation platform - a niche subject if ever there was one. The candidate obviously didn't attend Heriott Watt as the methodology was almost non-existent and the writing was poor (most obviously uses "it's" when meaning "its").

There was work for the Occupational Psychologist but I'll discuss that in a separate entry.

I cooked a great deal: having so many festive meals (and guests) meant that I had to plan meals and shopping in advance. I ran the whole gamut of my menu, even bringing back forgotten dishes such as pineapple chicken. One recipe which I heard about only after the festival might get cooked next year: matza mousaka (fried mince meat placed between slices of matza, then cooked in the oven). Minced meat is a very versatile ingredient; so much can be done with it but unfortunately my family don't like it very much (if at all) which means that the mousaka is likely to remain in the planning stages.

Lost videocassettes reappear

My wife was cleaning out some cupboards the other day in the hope of finding some new storage space. To the surprise of us both, she discovered a hoard of video cassettes which we must have forgotten about. Almost all of the cassettes held recordings of television programmes, which have long left our television screen but not from our memory: Cold Feet, Coupling, Star Trek DS9, Star Trek Voyager. 

As I have the complete Cold Feet series on dvd, I had no problem in throwing those cassettes away. I'm not too interested now in watching old Star Trek episodes (especially as a few seasons of DS9 weren't shown here) so discarding them also wasn't a problem. I have 'Coupling' stored on my computer, but only after throwing them away did I remember that the computerised episodes have no subtitles whereas those televised episodes did. A little regret there.

Out of maybe thirty cassettes, there were four which interested me greatly: one labelled Cropredy 2001 (I wasn't there that year; maybe this is a television programme), the Lindsay Anderson film "If ...", and two labelled "Forty something". These hold all six episodes of a British comedy/drama programme from 2003 starring Hugh Laurie and Anna Chancellor; I wrote about this series in one of my earliest blog entries, from nearly eight years ago.

I have just finished copying the video to dvd; this is done of course in real time, and it was a real pleasure to revisit the programme. I'm looking forward to watching "If..." again.

Pictures from a balcony (5)

Today's first picture comes from the same spot as in earlier photographs: on the ground, looking up to the balcony. The panasit tree is sprouting leaves and it's becoming difficult to see the balcony behind. As we have shortly passed the spring equinox, the tree will blossom soon.

 

The second picture is taken from the stairwell, looking out from the house into the gardens. There's so much vegetation in the picture that it's difficult to get one's bearings. In the top left hand corner is the kumquat tree outside our kitchen window. 

Another Holy Grail achieved: sending email from a separate thread

As I have noted, I have spent no small amount of time updating and expanding the use of email in the Occupational Psychologist's management program. Email is now sent via GMail and works very nicely ... except for one not so small problem: when one is sending an email with an attached file, the program apparently stops working for a minute or so. The solution to this problem is to send the email via a separate thread but until now I have lacked the programming knowledge to implement this.

After playing around with a few ideas (and not succeeding), I decided to ask the question on Stack Exchange and received perfectly cromulent code from Mr Indy, Remy Lebeau. As written, this works perfectly.

What is missing is logging - previously, whilst the email was being sent, there was a certain amount of diagnostic information presented to the user which basically meant "Although the program appears to be hung, it is doing something which takes some time so please wait". Due to well known technical problems with displaying in the main program output created by a worker thread, Remy's code does not address this problem.

I attempted to add a few types of logging to the program, none of which were particularly successful. I am reminded of the old academic adage, which has a lecturer talking to a student about a paper that the student has produced: "There is much here that is original and there is much which is correct. Unfortunately, what is correct is not original, and what is original is not correct".

One partially successful attempt had the thread writing its log to a file which was opened and closed all the time. This seemed to work until the program sent two emails almost simultaneously: one thread could write to the log whereas the second couldn't. As usual, one has to be away from the keyboard in order to find a solution. The initial improvement which my thinking suggested would have each thread create a string list and write all its logging to its list; after sending the email, the thread saves the stringlist's contents to a text file (each thread has a separate file). Then presumably something would concatenate the text files so that the program could display the entire log. This would require some form of identification within the log so that the log of two emails which were sent within seconds of each other can be traced.

The problem with this solution is the 'something' that concatenates; if two people are running the program at the same time (as frequently happens), then they may interfere with each other. Running a daemon - a separate program which runs only on one computer - would solve this problem, but I am currently considering a third option: instead of writing the stringlist's contents to disk, the contents get written to the database. This means that each thread has to have its own connection to the database. This actually is simpler than it seems.

Following is the code which I added at the end of the 'execute' procedure in Remy's code.

  email.Free;
  messagebeep ($40);

  ibdb:= TIBDatabase.Create (nil);
  with ibdb do
   begin
    loginprompt:= false;
    params.add ('password=masterkey');
    params.add ('user_name=sysdba');
    databasename:= dbdir;
    sqldialect:= 1;
    connected:= true;
   end;

  trans:= TIBTransaction.create (nil);
  trans.defaultdatabase:= ibdb;

  with TIBQuery.create (nil) do
   begin
    database:= ibdb;
    transaction:= trans;
    // add primary key via trigger
    sql.Add ('insert into emailog (msgnum, curdate, details)');
    sql.add ('values (:p1, :p2, :p3)');
    ParamByName ('p1').AsString:= FMsgNum;
    ParamByName ('p2').asdate:= date;
    for i:= 1 to log.count do
     begin
      ParamByName ('p3').AsString := log[i-1];
      execsql
     end;
    free
   end;

  trans.free;
  ibdb.free;
  log.free;
 end;
end;

A few notes: 

• The 'messagebeep' command has a parameter - I've always passed the value 0 to this function but it turns out that there are specific values producing specific sounds. The idea of including it in the thread is to give the user confirmation that the email has been sent. Making a noise doesn't compromise thread security.
• 'dbdir' is a global variable whose value is the location of the database.
• Databases are optimised for handling large amounts of data arriving in a short period of time. In the example above, there is a very small loop which basically sends some strings (in practice, seven) to the database, one after each other. Two threads could be doing this almost simultaneously - but the database can handle this easily.

Motorbikes (2)

I can definitely get used to my new motorbike! It's very quiet even though it's moving fast. The bike is heavier than the previous one - 110 kg as opposed to 85 kg - which becomes apparent when cornering or taking it on or off its stand. There is a bigger fuel tank - 7.5 litres - which means that I need refuel only once every three weeks (I'm assuming 20 km/l, which may be a modest assumption). Now that I think of it, my 120cc London bike's fuel tank capacity was 1 gallon and I used to get 100 miles/gallon. That seems to be equivalent to 35.2 km/l!

After posting those pictures the other day, I remembered that I had some early photos of myself and motorbikes. The first is from December 1970 when I was 14 - obviously too young to drive! I was just posing on my father's 50cc scooter on which I used to ride pillion every morning to school. I thought that the picture was from a year or two earlier. Unfortunately, the camera angle is too high: there's more tree than motorbike. Of course, in those days, the time between taking a photograph and actually seeing it could be a matter of days, if not months, so there was never any chance to review one's shots. Photography was so expensive that we never took multiple pictures; instead we took a long time framing the shots (obviously I didn't take this picture). It's not like today when there is instant review and one can take as many pictures as one likes.

The second picture is from June 1976, when I was nearly 20 (note the clogs which I had just started wearing). This is clearly taken outside of the house where I used to live in London and shows the motorbike in loving detail. There were a few other pictures from this time but they didn't concentrate on the bike.

Pictures from a balcony (4)

The first picture is not from the balcony nor of the balcony but rather the entrance to our building. From this angle, it's hard to see the entrance as the kumquats are hiding it. I am trying to give the impression that we live in a tropical paradise and that the vegetation hides ugly things such as buildings. That's not far from the truth.

For years, I wondered how we had such a vigorous kumquat tree on our side of the building as no one planted it and no one seemed to look after it. Presumably a seed fell from the tree on the other side of the entrance which took root; water came from the tap in the bottom right hand corner of the picture (if one looks closely, one can see a coiled hosepipe stored on the wall). There must have been a leak in the plumbing and the young kumquat drew its water from this source.

Whilst looking at the collection of pictures on my computer, I came across the one below which must have been taken just after the balcony had been built but before we had begun to decorate it. Somehow it looks larger in this naked state.

Sumptuous Sunday 6

I see that I haven't written about cooking for some time. Over the weekend I cooked five different meals -

• Friday lunch - grilled fish and vegetables
• Friday dinner - roast chicken pieces, chipped potatoes, vegetables
• Saturday lunch - grilled St Peters Fish, mashed potatoes, vegetables
• Saturday evening - spaghetti with tomato sauce and grated cheese
• For the coming week - chicken breast and vegetables

I wrote some months ago that I was making antipasti: dicing vegetables then placing them on a tray and baking them in the oven. Apart from the fact that this takes some time (30-35 minutes in a hot oven), it also uses electricity (to heat the oven). This isn't always a problem - for example, I cooked the chipped potatoes and chicken pieces in the oven at the same time on Friday evening - but sometimes it seems like a waste.

In order to avoid using the oven, I've been using a new technique for the past few weeks: using a ceramic pot which is called a 'garlic oven' in Hebrew.

 

One simply cuts the vegetables into little pieces then places them in the dish. I've discovered that it's easiest to take the bell part of the pot, invert it and then put the cut vegetables directly into the bell, instead of using another dish. When I've finished cutting, I put the base on the top of the pot and invert it again. 10 minutes in the microwave is the required cooking time!

I've had this pot for a few months after friends raved about it. At first, I was disappointed for I thought that the pot would give the vegetables a special taste, but after a while, I realised that this was a very quick and simple way to cook vegetables without losing any nutritional values. Regular steaming in the microwave requires a little water which probably leaches some vitamins B and C, but here there's no water at all.

Here's a picture of my grilled fish, along with the varicoloured vegetables and some mashed potato. To decorate the plate even more, I served the fish with a slice of lemon.

 

Due to the parsley which I sprinkled on the fish and the potatoes, it may be a bit difficult to tell them apart: the fish is on the right hand side of the plate whereas the mash is at the bottom.

Minimising the overhead

One of the factories in my company works with wood: the first step in any process is to cut the planks into pieces of the correct size as needed for production. The planks normally are sized 2.44 X 1.22 metres, so several parts can be cut from one plank. Laying out the parts on the plank is a specialised job: someone who does this well can save a great deal of raw material. Naturally, this is a job for a computer - this kind of program is called an optimiser.

The optimiser too has its raw materials: the dimensions and quantities of parts which have to be cut. This information comes from our ERP program and is manually entered into the optimiser. Someone wondered whether it would be possible to create a file in the ERP program and then input the file into the optimiser, thus optimising the operative's time and reducing the number of mistakes. This is where I came into the picture.

"Is the optimiser capable of reading an external file?" was my first question, and when it was answered in the affirmative, my next question was "What is the structure of this external file?". The answer to this question was a long time coming. I started by analysing the structure of existing files; whilst I could see that there was a definite structure, its details weren't clear to me. The next step then was to contact the manufacturer of the optimiser program (in Italy) and obtain the necessary technical data.

Unfortunately, it seemed that the defined structure was not equivalent to the structure that I was observing in files which had been created by the program itself after the data had been entered manually. There were hundreds of attempts made to create a file in the correct structure but I could never find the correct format. 

At one stage, I seemed to be making progress, only to receive a letter from my Italian contact which ended with the words "oh by the way, today is my last day at work with .... From now on, please contact X". Ouch.

To make a long story only slightly shorter, we eventually received a new mapping of data, and suddenly my files were being accepted by the optimiser. The values weren't particularly correct but at least I could see what needed to be replaced. After a few more attempts (I should point out that I am about 150km from where the optimiser is), I eventually found the magic format!

So we've now saved a great deal of work - the user can run a report in ERP, run my program on that report and create a file which can the optimiser can read. Can this be improved? I was idly thinking about this last night when it occurred to me that I can minimise the overhead even more: have the ERP program create its file then call my program to read this temporary file and then output an optimiser file. From my point of view, it's five minutes work, but from the user's point of view, it minimises the overhead.

---

As it happens, today I have been involved in implementing something else which promises to reduce overhead: our delivery notes will now bear a barcode (the delivery note's number). When we receive the delivery note signed by the customer, we scan the note in a scanner which is connected to special software; this software then assigns the scan to the delivery note in the database. If we had wanted to do this in the past, I would take the signed delivery note and scan it with a multi-function printer which sends me the scan as a pdf file via email. I would then save the pdf file in a directory and manually tell the erp program where the scan is. This would take about five minutes per document; this overhead has now been cut to around ten seconds (although of course we have to pay for the privilege).

---

It occurs to me that before starting work on the barcodes, I helped someone else minimise their overhead (maybe I should change my job description!). It began with an innocent question of how to get a inventory list for a given warehouse where the status is 'Goods'. It might be that the user was using the wrong report for this, but that's not the point; I showed her how she could get the list directly from the warehouse. Then I was told that she wanted to make a list of old work orders for these parts. My immediate reaction was to ask why; who needs a work order when the parts (chairs) already exist. "X needs the work orders so that he can print labels for them" was the reply. For every chair which is made, we print a label (which is stuck on the underside of the chair's seat) and it's easiest to source these labels from the work order.

Once I had got that straight, I pointed out that one could simply print labels for a given list of parts without having to find a work order. People are so used to a rigid set of rules that they don't see what lies behind those rules and procedures. It turns out that they had been dancing this dance every few weeks; hopefully now I will have minimised the overhead for them and saved a few minutes which can be spent on more useful pursuits.

Ring out the old, bring in the new! (My motorbikes)

Way back in 1975, in what now seem to be prehistoric times, I owned a Suzuki 120cc 'student' motorbike (I'm not sure why it had this nickname). I used it to drive around London for the next three years, as well as driving from London to Cardiff every now and then to visit my parents. When I left London and moved to Cardiff (prior to emigrating), I drove home on the bike, then drove it every day to Newport and back - I had found a job in a laboratory there. When I emigrated, I thought for a few minutes about loading the bike into our container but decided against; in those day, kibbutzim were very anti-personal transport and I doubt that a motorbike would have been a good thing to have brought with me. My parents gave it away to someone local who didn't look after the bike and crashed it after a few months (or so I believe).

Sometime during the mid 1990s, I became the ashamed user of a Piaggio 50cc 'motorbike'. This vehicle was bought by the financial manager of the company at the time; he used it to commute from the kibbutz to the factory, which was then about 1km from the kibbutz. Later, he was assigned a car and the bike was transferred to me. It was a good workhorse for the next few years but often had mechanical problems and surprisingly was even stolen about four times (although each time recovered quickly). The picture on the left is somewhat reminiscent of the bike I had (mine was certainly dirtier) but I think that it must have been redesigned in the following years. I was ashamed because frequently it didn't have enough energy to drive up mild slopes and I had to pedal in order to achieve enough speed.

After the factory burned down in June 2007, the various departments found homes in different buildings in the area; eventually the offices moved to a building about 5km from the kibbutz. It became clear that the Piaggio would not be capable of transporting me to these various buildings and so a 'new' motorbike was purchased. Just as I started looking, someone on the kibbutz offered a Peugeot Vivacity 50cc for sale which I promptly snapped up. It turned out that the owner was the son of one of my neighbours - I didn't know that he had a bike. The model was very similar - if not identical - to the scooter which I had hired whilst on holiday in Santorini, so I didn't have any problems in switching from the slim-line Piaggio to the wider Vivacity.

This bike has served me well in the past six years but there have been problems which have got worse in the past year. From day one, the electric starter didn't always work; this became a problem of Sunday mornings when whatever charge had accumulated would disappear (I don't use the bike on Fridays and Saturdays), meaning that I would have to start the bike with the kick starter. The electric starter has not worked at all since coming back from Croatia last summer and after spending a few weeks of staying at home (remember the whooping cough?); as a result, I have ruined a few pairs of shoes starting the bike with the kick starter. Although this motorbike did have a few regular services, the starter problem was never fixed.

The situation has been getting worse and worse; after one Sunday morning when I needed about ten minutes to start the bike, I informed the company's "transport officer" that it was time to find me a new steed. After about a week, he informed me that he had found a 125cc machine (Kymco Movie) which had barely been used. Since then, three weeks have passed in which the machine passed its MOT test and insurance obtained (I note that the cost of the insurance for the new bike is almost exactly double that of the old and nearly as expensive as our car insurance)... and eventually the bike has arrived!

Apparently the bike is very zippy and a great improvement on the Vivacity. I never dared to make a trip longer than 5km on the old bike, but this one should allow me to make much longer journeys - although I doubt that I will drive it to Tel Aviv! The problem is not so much the motorbike as Israeli drivers.

Pictures from a balcony (3)

It's been a few weeks since the last picture; Spring is in the air and the tree in front of our house (in Hebrew, it's called 'panasit', which means 'little lamp') is beginning to grow buds.

This is the view from the other side of the balcony. We had to prune the eucalyptus tree (it's on the right of the electricity pole which is in the middle of the picture) when the balcony was built. Many years ago, we thought that the eucalyptus was dead, but we pruned off most of the dead wood and it grew a new trunk. This tree is what gives me the feeling of living in a tree house.

Separating the two balconies are two loquat trees; obviously at the moment they're not blooming but there will be plenty of fruit in a few months.

On the other side of the building is a papaya tree. There's a huge fruit on the tree but it may be difficult to see: it appears to be growing out of the trunk at the bottom of the picture.

I'll never get those wasted hours back

Several years ago, the Occupational Psychologist and I developed a program in which the user would be presented with a variety of occupations (over 100) and would choose which appeal and which don't. On the basis of the answers, we performed some psychological voodoo and then presented a list of occupations to which the user would be suited. We still use this program in our 'laboratory' setting.

We later thought that this program could be used as an alternative revenue stream: we would convert it from a native Windows program to an interactive Internet program, put it on the OP's site and then invite youngsters to 'take the exam', paying a small charge for the privilege. As this kind of programming is not in my CV, we turned it over to an external programmer, the OP's eldest son (this is not a case of nepotism: he works for Intel, in case anyone is doubting his competence). We arranged the online exam so that one would first have to pay via PayPal, then using the PayPal receipt number as a serial number, the youngster would 'take the exam'; the output file would be sent by email to a certain address. My part in this was to parse the letter that we received from PayPal, notifying us of the paid user, then download the email holding the output file, perform the voodoo then send the results back via email. A non-registered user could still take the exam but the results would not be returned. 

[For any brave souls interested, here is the link to the exam. But beware: it's all in Hebrew]

Unfortunately this did not turn out to be the revenue stream that we had envisaged so the OP made the brave decision to turn the online exam into freeware (but still no one has heard of it...). I had to change my program slightly so that it would ignore anything which was connected with serial numbers etc. This modification was quite simple but then I came across something else which needed my attention.

The program sent its results by email: it was using code which used Outlook as the sending mechanism. Unfortunately, this required that Outlook run continuously on the 'server' which had caused problems in the past. Also, considering the programming work which I had been doing in the past two months, it was time to free the program from the shackles of the proprietary Outlook and move to the freeware of GMail. 

Whilst it didn't take long to perform the conversion, the testing took hours and seemed inconsistent. Sometimes the program would send an email but more often that not, it would stall in the portion of the program which actually sends the email, with no clues being given as to the source of the problem. After knocking my head against a brick wall and coming exceeding close to giving up, the answer suddenly presented itself: when sending mail via GMail, there are two security DLLs which need to be present in the same directory as the program. As soon as I moved those DLLs into the correct directory, the program worked (it had worked before because sometimes I tested the program in a directory which contained those DLLs)!

This is what I have referred to before as the Gumption Trap; once again I fell into its jaws.

But what this really reminds me of is a thread in Nick Hornby's "Juliet, Naked", which I last read a few weeks ago. The book starts off with a couple, Annie and Duncan, making a pilgrimage to America in order to visit places which supposedly had a strong connection to Tucker Crowe, the fictional recluse musician who made the eponymous record. When they come back to their dreary British seaside town, Annie and Duncan separate; Annie makes a calculation that she wasted fifteen years with Duncan and that no one was going to give her those years back.

This is how I feel (on a slightly different level, of course): I wasted a few hours and no one is going to give me them back. I could have been doing something else more productive, like studying for my doctorate....

Searching for Sugarman

I saw the Oscar winning film "Searching for Sugarman" yesterday and have to admit that I fit right into the film's intended audience. For those who know nothing about the contents of the film (like I was, prior to watching), here's a precis: at the end of the 60s and beginning of the 70s, an otherwise unknown musician known solely by his surname, Rodriguez, recorded two albums. Despite the enthusiasm of the producers, the records did not sell and Rodriguez was dropped by his record company after which he basically left the music industry. An American visiting South Africa brought with her a copy of his album(s) and people enjoyed listening to them, so much so that there were local pressings of the records made. As one person puts it, if you walked into a white liberal home in South Africa, you would always find three records: 'Abbey Road', 'Bridge over troubled waters' and 'Cold fact', Rodriguez's first record. Due to the isolation of South Africa and the fact that Rodriguez had been dropped, the audience knew nothing of the artist.

When a South African cd was issued of Rodriguez's second album in the mid 1990s, people began to wonder what had happened to the artist: there were stories circulating that he had committed suicide, which each story varying in the details. After a long search, contact was made between Rodriguez and South Africa and he visited there six times, each time playing several sold out concerts. It's the sort of story where the truth seems more unlikely than the fiction.

Having recently finished rereading Nick Hornby's novel 'Juliet, Naked', I am struck by certain parallels in the story. No one in Hornby's novel ever suggests that Tucker Crowe, the disappearing musician who made the eponymous record, is dead, but that's only because the rabid Internet fans had decided on the basis of virtually no information that a neighbour and friend of Crowe was actually Crowe. In 'Searching for Sugarman', the fans decided - again on the basis of virtually no information - that Rodriguez was dead. There were people in South Africa who thought that the person coming to sing to them must be an impostor - the parallel to the book is how Crowe can prove that he is who he says he is - "there are such things as passports".

According to Wikipedia, the film leaves out the awkward fact that Rodriguez was also very successful in Australia in the 1970's and became aware of it enough to tour there extensively in 1979 and 1981. While it could be argued to be "lying by omission" by implying Rodriguez was completely unaware of his success anywhere in the world, it is perhaps forgivable within the framework of the documentary since the film focuses on his mysterious reputation in South Africa, and the attempts of music historians there to track him down in the mid-90's, who were unaware of his Australian success due to the harsh censorship enacted by the apartheid regime coupled with international sanctions made any communication with the outside world on the subject of banned artists virtually impossible.

I find it interesting to note that although Rodriguez's first tour of South Africa was in 1996 (and was filmed both by his daughter and by media interests in South Africa), the film was only made in 2011.

I saw a note referring to this film a few days ago, asking "where are the royalties?" The original record label owner is asked this during the film and avoids the question, disparaging the interviewer who should be asking questions about the music and not about the money. It's a valid question, though; presumably the South African distributors took their percentage and passed the rest on to the American label owner who spent it on other things and did not keep accounts. There were probably also many bootleg recordings made which profited only the bootleggers. There is also a note at the end of the film that Rodriguez gave away most (all?) of his earnings from the South African tours, so presumably the missing royalties wouldn't have been an issue with him anyway.

Alvin Lee, RIP

The music lists are full of notices about the death of guitarist Alvin Lee, whose greatest claim to fame was probably 11 minutes of guitar pyrotechnics in the film 'Woodstock'. David Hepworth was quicker on the ball than I to write about this.

My major connection with Lee was in December 1969, only a few months after Woodstock, when I went to see his group, Ten Years After, along with Blodwyn Pig and Stone the Crows. I've written about this before, but from the outlook of BP. I was familiar with some of the TYA material - I had heard their album 'Sssh' and had sniggered over the lyrics of 'Good morning, little schoolgirl' - and was to understand the connection between the riff and rock music from TYA.

A few months later, I was to buy their next album, 'Cricklewood Green' and even the one after that ('Watt'), but the writing was already on the wall: all the songs sounded the same and they were all based on the twelve bar blues. In my defense, I will note that I was only 13 at the time of the concert, and 13 year olds were much more callow in the late 1960s than they were in the late 2000s. 

That period was climaxed by their single 'Love like a man': one side of the record played at 45 rpm whereas the other side (a live recording from the Fillmore East, IIRC) played at 33rpm; this live recording was about seven minutes long. The single version was actually an edit from a longer, studio, version which appeared on 'Cricklewood Green', but I think that the live version was the best of all three. I have a memory from about March 1970 of fixing a pickup onto a cheap acoustic guitar whilst listening to this song (along with 'Hot Rats' by Frank Zappa) and learning how to play it.

I read an article a few days ago which was written in response to the death of Kevin Ayers; the author was writing about the different generations of rock musicians who were dying. He noted sardonically that soon it will be the turn of the punk rockers - and unfortunately the only thing which I share with them is a similar year of birth: John Lydon aka Johnny Rotten, the Sex Pistols, is six months old than I am [A few days later, I remember that John Lydon also liked Van der Graaf Generator, which means that we have something else in common]