Firebird date fields, continued

Following on from my previous post, I discovered that the code which I posted did not work.


What did work was a query with parameters:

update person set
dob = :a1, examdate = :a2
where id = :an

Here is the corresponding program code

with qDates do
 begin
  close;
  parambyname ('a1').asdate:= dob;
  parambyname ('a2').asdate:= examdate;
  parambyname ('ap').asinteger:= pid;
  execsql;
 end;

It's nice to know that this blog is being read by someone (see comment attached to previous post), and it's even better to know that we reached the same conclusion.

At the moment, the code which enters a new person into the database is split between two queries: one is an insert query, passing string literals, and the other (shown above) is an update query passing parameters. I now wonder whether it is possible to use only one query, which will insert using parameters. This will make the code run quicker and will also be clearer. Early days.

The port of the simple program from BDE to FB was completed last night with the above code. It's interesting to draw conclusions from the port:

1. One unit reads a text file (which is the output from the exam program), and inserts data into the 'people' and 'results' table. This is done using the TSQLQuery component with 'insert' and 'update' statements (should only be 'inserts' - I'll work on this tonight).
2. One unit allows the 'people' data to be edited - this is done at the moment with the trinity of TSQLDataSet, TDataSetProvider and TClientDataSet. Now that I think of it, one needs the 'trinity' only when a bi-directional dataset is needed, and in this case I am displaying only one record, which means that I may be able to use the TSQLQuery alone, in the same way that I used TQuery in BDE programs.
3. One unit displays all the people who have taken the exam - as this uses a dbgrid, the trinity is used.
4. Two units output data (to Word) - TSQLQuery.

So, apart from correcting the input unit (a simple matter), I should investigate whether the editing unit can be simplified.

One final 'gotcha': in order to use the program during development, the TSQLConnection has the appropriate database string and is automatically opened. This has to be changed after primary development has been finished, as the program must be deployed without a database string, meaning that the connection must be initially closed. I can't see a way of doing this automatically; I will have to remember to change the connection parameters before deploying the program.

Firebird date fields

In continuation of my previous post about problems of inserting/update date fields in FB databases, I did a little poking around and eventually - by trial and error - found the correct syntax:

update data

set datefield = '01/01/2008'

where id = 1;

It's going to be an interesting exercise producing code like that from Delphi, as the single quotation mark is the string delimiter, and it's always fun trying to create strings which include the quote. It'll have to be something like this:

with sqlquery.sql do

begin

add ('update data');

add ('set datefield = ''' + query1.fieldbyname ('datefield').asstring + ''' where id = 1';

end;

Let us not forget that the date is being passed from a query which is accessing the original BDE database.

Climbing the learning curve

I've spent the last week getting Firebird to work in my occupation psychologist's office. On my development computer, this is very easy, as the FB server runs on the same computer as the development system, but in the office, the FB server runs on a computer server, and the programs accessing this server run on different computers.

Eventually, after a lot of huffing and puffing, I got FB running in the configuration that I want: server program on server computer, client programs on client computers. In the process of doing this, I wasn't able to utilise the 'aliases.conf' file of FB successfully; this is a system in which each client program uses an alias to access the actual database data. The alias allows one to move the database file from location to location without having to redefine within the client program the physical location of the database file. This is fine in theory, but I couldn't get it to work in practice (the problem lies in the fact that at home, the file is on the same computer as the client, whereas in the office, it isn't).

In the end, I solved the problem by ignoring aliases, and storing the database location in the client computer's registry. I had originally intended to do this in the client program, but after a few minutes cogitation, I realised that this would not be a good idea, as the same code would have to be duplicated in every client program. Accessing the location from the registry is not the problem, but rather saving the data in the registry (which would involve a 'find file' dialog).

With this in mind, I sat down yesterday to write a small program which displays the data saved in the registry, along with the possibility of adding and editing this data (I'm not going to bother with deleting values at the moment, even though this would be simple). The program is akin to many other simple database programs that I have written, although with two very important differences:

1. the data is stored in the registry, not in a database, meaning that the access methods are completely different
2. the data is displayed in a list view and not in a dbgrid

I have never used a list view before, but was able to find simple code which explained what I needed to do.

I have also been porting a program from using the BDE to Firebird; to warm myself up, I took a simple program (actually two programs) which has a small database. As I wrote before, the screens which display data are fairly easy to convert. Adding data is more difficult, and this time I went a stage further than the first program which I ported: I learnt how to use a generator in order to provide an auto-incrementing value. In the BDE, this happens automatically, but in FB one has to do this manually.

The major problem in porting an application would seem to be converting the data from one database format to another. Whilst there is no problem in exporting and importing integer and string values, there is a minor problem with boolean values (BDE has them, FB doesn't), and a major problem with dates. This is the next problem to be cracked. Fortunately, the database which I am porting only has about 45 records (two dates per record), so I added the dates manually, but the major program suite which I will port in the near future has over 3,000 records (not a huge amount for mankind, but large enough) with two dates per record, and there is no way that I am going to add those dates manually.

So this week, I've learnt the following things

• how to install FB on a network
• how to access FB from client computers on that network
• how to use the list view component
• how to use an FB generator for auto-incremented integers
• how to insert/update a date into an FB table [to do]

That's enough for one week.

Fortunately (and purposely) my MBA load is very light at the moment, so I don't have to devote more than five hours on a Friday morning to this on-going project. The accountancy course is very simple - for me, as I spent years of my life working on the material which is being presented - but apparently is difficult for people when it's the first time that they come across it.

Moving office

The powers that be decided that our factory should be moved to a new building. I think that the decision was based on two factors:

1. Not being satisfied with our current arrangements (where the raw materials warehouse is 4km from the production areas!)
2. A reduction in the volume of orders means that the current leased floor space is too large

So we're moving to a brand new building, about 500 metres away. As it's new, everything has to be installed - primarily electricity, but also sprinklers, telephone wires, network cables, plumbing - and an office has to be built. Due to the scarcity of space, the office consists of two separate floors.

Each cubicle looks at the moment to be very small. I have been given to understand that my table will fit in the space allotted me, but I don't know what else will fit. Maybe it's time to start a diet, so that I will be able to slide between the wall and the desk in order to get to my chair.

The server room has also been reduced in size; I gave an estimate of the minimum amount of space that I need, and that's the size that I've been given. What I didn't know is that one wall of this 'room' - the outside wall - is mostly taken up with a window! This is bad on several accounts:

1. Anyone can break in through the window (so bars are supposed to be fitted)
2. Too much light comes in
3. Too much heat comes in
4. Valuable wall space is lost - there is equipment which could have been fixed on the wall in that area.

Today we spent a few hours packing up. It's interesting to note how many cartons have been left unpacked since our previous move, 20 months ago. I have a real problem with throwing things away, but this time I was able to part with ten (or more) old printer cables, an external modem, uncounted electricity cables, old ink cartridges for printers that no longer exist, a few L120 cassettes, ad infinitum. I could also part with documentation relating to the upkeep of my old Alpha server which was decommissioned several years ago.

Speed IV

Honestly, Ma, I tried as hard as possible using threads, but there was always some problem. So I dropped using threads and tried to improve my code algorithmically.

The problematic module is basically composed of four sections: an initialisation section, and three different queries. Most of the time was being spent executing the second query, so I concentrated my efforts here. The first optimisation was to note that data from one table was being read several times in several different queries; although it goes against the grain, it proved quicker to extract that data with a simple query, store the data in an array in the program code, and then simplify the other queries which would look up the missing value in the array. This speeded up the execution, but still not enough.

Then I went down to the metal. There was a query which basically was returning the same data again and again which would then be sent to an expensive operation. I had implemented a check before the expensive operation several months ago to make sure that the same data wasn't being sent, but now it was clear that the check was occuring too late. I was checking after the query had returned a dataset instead of checking before accessing data. Once I had repositioned and recoded the check, the program ran much faster.

So basically I've spent two weeks running around, learning about threads and emitting HTML code, when in fact I could have saved all that effort by concentrating on improving one query.

I read somewhere in this blog that SQL joins slow down a program and should be avoided at all costs. This sounds like shooting oneself in the foot, because if there are no joins, then all the power of SQL is lost. Well, not quite. Although it's messy, it makes no sense to extract the same data continually from the server; better to extract it once, save in in RAM and use lookup statements in code.

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.

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.

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.

MBA

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....

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.

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
 begin
  self.Top:= ReadInteger (prog, 'Top', -1);
  self.Left:= ReadInteger (prog, 'Left', -1);
  self.Height:= ReadInteger (prog, 'Height', -1);
  self.Width:= ReadInteger (prog, 'Width', -1);
 end;

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);
begin
 with sender.Canvas do
  begin
   case longint (node.data) of
    1: font.color:= clBlue;
    2: font.color:= clGreen;
    3: font.color:= clRed;
    0: font.color:= clBlack
   end;

  if node.level = 1
   then font.style:= [fsbold]
   else font.style:= []
  end
end;

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);
{$I-}
reset (f);
{$I+}
if ioresult = 0 then
 begin
  readln (f, line);
  while not eof (f) do
   begin
    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));
   end;
   readln (f, line)
  end;
  closefile (f);
 end
  else
   try
    tv.loadfromfile (extractfilepath (application.exename) + 'progman.txt')
    except on e: exception do
    tv.Items.AddChild (nil, 'Programs');
  end;

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.

Optimising Excel automation

The key to optimising automation is to call the automation server (ie Word or Excel) as little as possible. In Word, this especially means not calling the server inside a loop. This is normally achieved by building a string by concatenation in a loop, and then passing this string to Word when it is finally built, eg

tmp:= '';
with qExample do
 begin
  close;
  params[0].asinteger:= n;
  open;
  while not eof do
   begin
    tmp:= tmp + fieldbyname ('name').asstring + #13;
    next
  end
end;

wrdSel.typetext (tmp);

Optimisation should not be limited solely to outputting text. Looking over one of my programs, I discovered that I had output text to a table, and then set the shading for each cell in a column separately. It is, of course, better to shade a table column all in one go, and then set any cells which need to have differring shading. Bearing in mind what I wrote previously about accessing Word collections in Delphi, the code becomes

wrdTable:= WrdDoc.Tables.Add (wrdapp.selection.Range, row + 4, 12, 1, 2);
wrdTable.columns.item(1).select;
wrdSel.range.shading.texture:= 100;
// 'unshade' certain cells
wrdTable.cell (1, 1).range.shading.texture:= 0;
wrdTable.cell (2, 1).range.shading.texture:= 0;

And now onto Excel.... The major activity when automating Excel is passing values to the various cells; other activities such as formatting the worksheet exist, but are much less important. Remembering the prime directive in automation, "call the server as infrequently as possible", it seems as if there is little to be done to automate Excel. Well, not quite.

The first, most simple and most effective optimisation is to hide Excel until all the data has been transferred. Whilst it is cool to watch the data being transferred, the screen updating is also slow. So the standard code to launch Excel should be

XLApp:= CreateOleObject('Excel.Application');

XLApp.Visible:= false;

XLApp.Workbooks.Add;

Sheet:= XLApp.Workbooks[1].WorkSheets[1];

and at the end of the code should be the line

XLApp.Visible:= true;

How can the Word optimisation of concatenating the data before exporting it be translated to Excel? The answer is to declare an array in Delphi, populate this array in Delphi, and then transfer it in one go to Excel. I don't remember where I picked up this technique so unfortunately I can't credit anyone. [Edit: here is the link]

Here is the complete code from a demonstration program which I wrote for myself; the only component on the form is a 5X5 stringgrid renamed sg. The program populates this stringgrid and then performs the magic. The important stage is to chose a range in the worksheet which is the same size and dimensions as the array to be passed; this is the 'range:= ' line.

procedure TForm1.Button1Click(Sender: TObject);
var
 xls, sheet, Range, arrData: Variant;
 i, j: integer;

begin
 {create variant array where we'll copy our data}
 arrData:= VarArrayCreate([1, sg.RowCount, 1, sg.ColCount], varVariant);

 {fill array}
 for i:= 1 to sg.RowCount do
  for j:= 1 to sg.ColCount do
   arrData[i, j] := sg.Cells[j-1, i-1];

 {initialize an instance of Excel}
 xls:= CreateOLEObject('Excel.Application');
 xls.visible:= false;
 Sheet:= XLApp.Workbooks[1].WorkSheets[1];

 Range:= sheet.Range[sheet.Cells[1, 1], sheet.Cells[sg.rowcount, sg.colcount]];
 Range.Value:= arrData; {copy data from allocated variant array}
 xls.Visible:= True;
 range:= unassigned;
 sheet:= unassigned;
 xls:= unassigned;
 close
end;

Happy optimising!

Yet more Word automation

Today I was checking out possibilities of optimising my Word automation code. I know that theoretically it's possible to send to Word a list of strings and then get Word to turn those strings into a table, but I tried this once and didn't succeed. The first stage is to build a long string, whilst separating the various values with a separator character, which is obviously one which won't appear in the text itself. I used the caret (^) as a separator, and one tells Word to use this character with the DefaultTableSeparator method. The code for the first step is thus:

wrdApp.defaulttableseparator:= '^';

tmp:= '';

with qJobs do // get text from this database table

 begin

  close;

  parambyname ('a').AsInteger:= a;

  parambyname ('b').AsInteger:= b;

  open;

  while not eof do

   begin

    tmp:= tmp + fieldbyname ('name').asstring + '^';

    next

  end

end;

tmp[length(tmp)]:= ' '; // remove the final caret

wrdSel.typetext (tmp);

The exported text will be in the form "a^b^c^d^e^f^g".

The key to turning this text into a table is to selected the exported text first and then turn it into a table. My normal method of exporting text into Word doesn't leave the text selected at the end, so I had to figure out how to select it. The exported text is always going to be a paragraph by itself, and it's always going to be the current paragraph (but not necessarily the final paragraph at the time of export). To quote an article which I found today, "if you want to know [the current paragraph number] in order to operate on the currently selected paragraph, table or other object, you can simply use: selection.collectionname (1)."

which in Delphi translates to "wrdSel.paragraphs.item(1).range.select". The Delphi "gotcha" is that Word collections have to be referred to as "collections.item(x)", not "collections(x)".

The next stage is turning the selected text into a table; this is done via the 'ConvertToTable' method, which takes a large number of parameters. Fortunately, we only need the first three; the first parameter says how to separate the table items, which is via the default separator character. The Word constant for this is "wdSeparateByDefaultListSeparator", whose value is 3. The second parameter is the number of rows in the table; although this parameter might seem to be important, it is irrelevant as the table adds rows according to the need, and so I pass the number 1. The third parameter, which is much more important, is the number of columns. Here is the code for this part:

wrdSel.paragraphs.item(1).range.select;

wrdSel.converttotable (3, 1, 2);

Normally I add a table to a Word document using this code:

wrdTable:= WrdDoc.Tables.Add (wrdSel.Range, 1, 12, 1, 2);

In the case of ConvertToTable, however, the table has already been created, and so the above line is useless. The solution is to access the current table which has been added to the document, not assuming that this is the first or last table. The way to do this is the same way in which I accessed the current paragraph, viz

wrdTab:= wrdSel.tables.item(1); // the new table

Once I have a pointer to the current table, I can do anything I want with it, such as removing the borders, shading one column, setting text alignment, etc.

Here is the final and complete code:

wrdApp.defaulttableseparator:= '^';

wrdSel:= wrdApp.selection;

tmp:= '';

with qJobs do

 begin

  close;

  parambyname ('a').AsInteger:= a;

  parambyname ('b').AsInteger:= b;

  open;

  while not eof do

   begin

    tmp:= tmp + fieldbyname ('name').asstring + '^';

    next

  end

end;

tmp[length(tmp)]:= ' ';

wrdSel.ParagraphFormat.Alignment:= 2;

wrdSel.typetext (tmp);

// the below line always selects the current paragraph

wrdSel.paragraphs.item(1).range.select;

wrdSel.converttotable (3, 1, 2);

wrdTab:= wrdSel.tables.item(1); // the new table

wrdTab.borders.insidelinestyle:= false; // no borders

wrdTab.borders.outsidelinestyle:= false;

wrdSel.homekey (wdStory); // jump to beginning of document

I used this code in a different place in the program to create a table with 75 rows of 5 columns each. The code executed almost instantaneously, as opposed to the length of time it took before. This technique cannot be slower than building the table manually, but the advantage becomes clearer the more rows and columns that have to be added.

Incidentally, I have one table with twelve columns and an changeable number of rows, but there is text only in the first and last columns. Of the other ten columns, only one will contain an 'X', which represents the numerical value for the first column in a range between one and ten. I did not use the 'ConvertToTable' technique to build this table as it is sparse, and the CTT technique should only be used for dense tables.

Next up is a similar conceptual technique for exporting data to Excel.

Counting beats with Van der Graaf

I had a long drive yesterday to the north of Israel, and took with me a few disks to listen to on the way. Coming back, I played Van der Graaf Generator's "Godbluff" and "Still life" and for want of something better to do, I started counting beats.

People have always ragged VdGG with their weird time signatures, but I've never really noticed and always assumed that those people were just tarring VdGG with the time signature brush, whether it's true or not. If you want real funny time signatures, listen to National Health....

So: "The undercover man" starts in 3/4 for two verses and then moves to 4/4 for the rest of the song. "Arrow" is in 4/4 throughout. "Scorched earth" is a different kettle of fish, though. The opening statement is three bars of 4/4 followed by one of 3/4. The verse has lines of 4/4 and lines of 5/4. The closing riff sequence is mainly in 5/4. There's a restatement of the riff at one point in 6/4. There's one section which seems to have 5/4, 3/4, 4/4 in successive bars. I also counted some bars as 7/4. That's fun for all the family.

Over the years, I haven't listened much to "The sleepwalkers", so this was quite a metric shock. I haven't figured out yet in which time signature the opening verse is - this will have to wait for another time. After the middle "if I only had time", there's a long riff section of 18 beats per repetition, which is probably three bars of 4/4 and one of 6/4. When the final verse comes in, the main structure of the vocal lines is 4/4, 2/4, 4/4, 4/4, with the instrumental bits in 3/4. I wonder whether the song was originally written like the final verse but became "tarted up" during the arranging process, during which beats were lost forever.

I listen to most of "Still life" quite frequently, but there are still some interesting things to be heard. The opening two tracks, "Pilgrims" and "Still life" seem to be totally in 4/4. Yesterday, I heard "La Rossa" as a slow 12/8, and on that basis, the middle section became very interesting ("if we made love now..."): a bar of 3/4 followed by two of 4/4, repeated several times (that could be written as 9/8 followed by two bars of 12/8, to make the triplet beat clear).

I noticed a few weeks ago that "My room" has a similar metric structure to the final verse of "Sleepwalkers" - 4/4, 2/4, 4/4, 4/4. If we take the first line, "Searching for" is a bar of 4/4, "diamonds" is a half bar of 2/4, "in a sulphur mine" is two bars of 4/4.

"Childlike faith in childhood's end" has most of its verses in 13/4 (or 4/4, 4/4, 5/4)!

Buy me a metronome for Christmas.

Better late than never

Several years ago I noticed that the more that I program, the less music I make. Unfortunately at the moment it's much easier to write computer programs than it is to write songs; for one, I am never short of ideas whereas I never have any ideas for the other.

But while I am writing programs, I am also listening to music, and in the 'better late than never' department, I must note my increasing interest for Joni Mitchell's classic "Blue" album from 1971. I've been listening to this album for the past few years but have never payed too much attention to it. At first, the faster songs ("All I want", "Carey", "California") served as my introduction, but lately their appeal has begun to fade, and to be honest, I almost wish that they weren't on the album, for I have fallen in love with the slower songs.

"Little green", "Blue" and "River" have become mainstays of my listening in the past few days. I admit that mainly I listen to the music and not to the words, not having enough spare processing power for them. The title song alone is worth the price of admission, especially in the 'middle bit', where a line sung in 4/4 suddenly compresses to be sung in 3/4 ("acid, booze and ass").

Inter-program communication

I wrote the other day about inter-program communication using a user-defined message which is broadcast to all windows. This technique is sound because only the programs involved recognise the user-defined message. Today I was reading about the wm_copydata message, which allows one program to pass data to another program. Here, broadcasting the message to all windows is strictly forbidden, as any program which has a wm_copydata handler will try to handle the message, sometimes with disastrous results. Here, the technique has to be to identify in advance (via the findwindow function) the receiving program. My use was limited only to passing a trigger: in plain English, 'when you receive this message, you'll know that I've finished'. If I ever use this technique in the future, I could pass a parameter in the 'wparam' field which has an agreed meaning. Here's a link to a program written in Delphi which uses wm_copydata. This isn't quite as robust as the one shown in the first link, although the article uses 'findwindow' and thus glosses over certain problems.

More Word Automation

After figuring out how to add bold text to a Word document from a Delphi program, it was only a short step to figure out how to add underlined text. But the real cherry was finally figuring out how to add bulleted text, ie

• line one
• line two

This may appear to be trivial, but the code which Word creates when doing the above as a macro is fairly complicated. I found some code in a Japanese Delphi blog which basically copies the Word macro code and turns it in Delphi, but it didn't work for me. It turns out in the end that most of that code is actually unnecessary, and I managed to write a 'bullet' procedure with only a few lines of code. I am posting the code here in the hope that Google will bookmark it, so when some poor programmer tries to create bulleted text in Delphi, he will have an example which works. Here it is:

procedure wrdBullets (app: variant; const s: string);

{ This procedure MUST be passed the wrdApp object, as ListGalleries is a

property of this object, not of the selection object}

const

 wdBulletGallery = 1;

 wdListApplyToWholeList = 0;

 wdWord10ListBehavior = 2;

var

 template, sel: variant;

begin

 Template:= App.ListGalleries.Item(wdBulletGallery).ListTemplates.Item(1);

 Sel:= App.selection;

 Sel.Range.ListFormat.ApplyListTemplate

(Template, false, wdListApplyToWholeList, wdWord10ListBehavior);

 Sel.typetext (s);

 sel.Range.ListFormat.RemoveNumbers;

end;

As always, the problem with creating Word automation code (in any language, but especially Delphi, which is a non-Microsoft language with a different syntax) is knowing which methods belong to which objects.

I have created a Delphi unit which exports all the more complicated functionality, such as bold, underline, bullet and gotobookmark. This way, I only have to write the complicated code once, and then every program which I write can use this code simply by including a call to a procedure.

Encouraged by these achievements, I also included the unit a procedure which receives a range of Excel cells and puts around and in them a double border.

Exam program launcher

I work with my occupational psychologist friend every Friday morning. Today, whilst we were talking about a program that we are developing, I noticed that her computer's desktop was filled with icons from my programs: normally three icons per program (one manager, one exam and one results program). I suggested writing a 'program launcher' program, which would present the user with a choice of programs to be run, and then run the chosen program. This would cut down the number of desktop icons drastically.

In the end, we decided against such a program (or rather, left it as an optional exercise for the future) as it doesn't improve the working environment. But out of this idea came a better idea: write a program launcher for the various exams that clients sit. What, you might ask? If a program launcher for multiple programs was discarded, why write one for only a subset of those programs?

The difference is this: a client will sit down at one of the computers in the computer room. The secretary will bring up one of the exams, fill in the client's details (surname, forename, id number, etc) then let the client do the exam. When the exam finishes, the secretary will bring up another exam, type in the client's details again and then let the client do this exam. All the exams were written purposely as standalone programs which need no external files and so can be distributed and run at other sites with a minimum of effort. This is why the user's personal details have to be entered into every exam. Use of a launcher program would mean that the personal details need only be entered once, and then the user can get on with all the exams.

The exam program launcher which I designed works on the following basis:

1. The user's personal data are entered into the launcher
2. The launcher saves these data in the computer's registry
3. A list of exams which the user will sit are chosen from the list of all exams (this list comes from a database which holds the exam's name, its location and any command line parameters)
4. The first exam in the chosen list is removed from the list and run
5. When the exam finishes, it notifies the program launcher than it has finished
6. If there are more exams in the chosen list then the launcher loops to step 5
7. The launcher removes the data saved from the registry and exits

Whilst the above isn't exactly rocket science, one will not find many examples of how to do inter-program communication in Windows. In DOS, this probably would have been done with an interrupt; in fact, during the dying days of DOS, I became quite adept at writing TSR programs, initially in assembly language and then in Turbo Pascal, which used the $2F multiplex interrupt for this purpose. But now we are in Windows, and so we must use a message. But which message?

It turns out that Windows has the 'RegisterWindowMessage' function which creates a unique message number for the parameter passed to it. Several programs can call the same function with the same string; the first program that calls the function will create (register) the message number, and every subsequent call will retrieve the same number.

I modified some of the exams to do the following:

• In the 'GetDetails' dialog form, the program checks the registry to see whether it has values. If the exam has been called from the program launcher, then there will be values, whereas if the exam is run directly then there won't be any values.
  
• When the exam finishes, it makes a call to the RegisterWindowMessage to get the special message number and then broadcasts a message to all the running programs with that message number.

If the exam is run directly, nothing untoward happens, but if the exam is run from the launcher, then it will indeed notify the launcher when it completes. The launcher handles this completion message by executing the next exam in the exam list.

It's a bit difficult to explain this asynchronous, inter-program communication in a synchronous manner (ie writing this explanation) and to someone who isn't used to event-based programming, but like every clever idea, it is in fact quite simple (especially in retrospect). One of the neat things about this program is that the exams can also be run directly, with no side effects.

Who plays with whom?

In 1996, I started writing a cd database program; this would have been one of my first serious efforts at writing database programs in Delphi. The program would store physical information about musical cds (the cd's internal id number, how many tracks, how long each track lasts), and I would add data such as cd name, song title, who made the cd, who played, who composed, etc. Once this was done, I could get out of the database arcane facts such as how many tracks were composed by Richard Thompson, on how many tracks Simon Nicol played and so on. Maybe the highlight of the data mining was calculating on which tracks both RT and SN played.

Under the hood, the program was not written very well. My original source of information about Delphi database programming used the 'ttable' component all the time, so tables were lavishly scattered around the code. Somewhere around 2003, I started replacing tables with queries, at least in the places where I understood what was happening. Such queries made the code much simpler to understand, with a few terse but clear lines replacing such monstrosities as a record by record search of a table.

The 'who plays with whom' form was an exceptional case of using tables, most of them temporary, and presumably because of this form's complexity, I had stayed away from trying to improve it. Yesterday, I was having a routine look through the program, changing bits and pieces to current standards, when I came across this form. I knew what the code was supposed to do, but didn't like the way it was being done - and in fact, I cringe at what I had written twelve or thirteen years ago. Well, I didn't know better.

As it was dog walking time, I had the perfect opportunity to think about improving this function. I was fairly sure that I could replace almost all of the code (some of which dealt with finding which songs were common to two or more people, and some of which dealt with finding data about those songs from various tables - as if I had never heard of the 'join' statement in SQL) with one moderately advanced query. When I came back from our half hour walk, I set to work, and came up with this:

select tracks."track name", artists."artist name", cds."cd title", tracks."track id", count(*)

from musician, tracks, artists, cds

where musician."track id" = tracks."track id"

and tracks."artist id" = artists.id

and tracks."cd id" = cds."cd id"

and musician."person id" in (0)

group by tracks."track name", artists."artist name", cds."cd title", tracks."track id"

having count(*) = :an

The '(0)' gets replaced by a list of people (their id numbers), so basically the query returns a list of tracks (their name, the artist and the cd on which they appear) and how many times those tracks were returned from the musicians' table, given a list of musicians. The musicians table, being a link table between people and tracks, has three fields: a meaningless id, a person id and a track id, where each record means that person 'a' played on track 'b'. In retrospect, the record's id field is unnecessary but harmless; but I have refrained from changing the database structure as this would entail multiple changes in the program code.

The parameter 'an' is the number of people contained in the list passed to the query. The query returns a list of all the songs on which one or more people in the musicians' list played, but the count value can vary: it could be one (in which case only one of the people in the list played on the song), two or more. This value can only be checked after the query has completed, which is why the final line is a 'having' clause, something which I rarely use. The query must return only the songs on which all the people played, so the 'count' field must equal the number of people in the list.

Holy Grail, part 2

It turns out that the holy grail is not so easily found.

The code which I posted here worked perfectly at work. So when I came home, I plugged it into a real program ... and of course, there was no bold text. Hmmm, I thought to myself, maybe I've written this code before and discarded it because it doesn't work on my computer. Maybe it will work on my client's computer, which is the main point.

Then I took the dog for a walk. Whilst on the walk, I thought about this 'bold' problem, and decided that I should attack it with a more scientific approach. First see whether the demo program does print bold text on my computer - after all, I'm using the same version of Word (2003), and the code which I saw on the internet was several years old, meaning that the functionality has existed from much earlier versions of Word.

So when I came home, I ran the original demo ... and got bold text. I then took the specific code for the bold text and plugged it into my program. Still bold text. I replaced the literal string being printed bold with what I actually want as bold - no bold. Aha! The moment of revelation! Could it be that this code prints English in bold text, but not Hebrew? This wouldn't be the first time that I've seen a gotcha with Hebrew text. As I say, maybe I have been here before and discarded the (almost) correct solution because it didn't print Hebrew text in bold. This time, I knew that the code was almost correct, and this gave me the impetus to carry on.

The next step was to see what Word does itself, by recording a macro and performing the required operation, then by looking at the resulting macro. I often do this, but sometimes there's a problem recognising the variable which Word uses for the operation. Anyway, this time I could see what was going on: first, there was the call to set the value of the 'bold' property, and then there was an extra call, setting the value of a property called boldbi (presumably a contraction of 'bold bidi', ie bidirectional, where 'bidi' is often used in Windows when printing right to left languages such as Hebrew and Arabic). Based on this knowledge, I then added a call in my program to set the value of this new property - and then my Hebrew text appeared in bold! So here is the complete 'bold' procedure:

Procedure Bold (const s: string);
begin
 wrdSel.Font.Bold:= 1;
 wrdSel.Font.BoldBi:= 1;
 wrdSel.TypeText (s);
 wrdSel.Font.Bold:= 0;
 wrdSel.Font.BoldBi:= 0;
end;

wrdSel is a variable global to this procedure, declared as per yesterday:

wrdSel:= wrdApp.selection.

Holy grail found

Every now and then, a 'programming holy grail' emerges: this is normally something that I very much want to learn how to do in a program, but can't find out how to do it. Such a holy grail has to be something incidental - otherwise the program's development will be stymied and never completed. The latest holy grail is something which has been bothering me for some time - how to print from a Delphi program bold text within a Word document.

Creating a Word document and adding contents to it is called automation; whilst there is a fair amount of information about this on the internet, it tends to be basic, such as how to create the document, add some text and then close it. The finer points of formatting tend not to be discussed in internet articles. As it happens, I bought several months ago an excellent book on Office Automation, "Microsoft Office Automation with Visual FoxPro"; the only problem with this book being that it's written for Visual FoxPro (duh!) whereas I'm using Delphi, and the syntax is different. It's not too different, but sometimes sufficiently so and not clear enough which object has which properties and methods.

To create a Word document, and some text and then close it is done like this in Delphi:

var

 wrdApp, wrdDoc: variant;

begin

 wrdApp:= CreateOleObject ('Word.Application');

 wrdApp.visible:= false;

 wrdDoc:= wrdApp.documents.add;

 wrdDoc.select;

 wrdApp.selection.typetext ('Wow - I''m adding text to a Word document from  Delphi');

 wrdApp.selection.typeparagraph;

 wrdApp.visible:= true;

 wrdDoc:= unassigned;

 wrdApp:= unassigned;

end;

All calls to the wrdApp object are slow, and so I've found all sorts of ways to improve the duo of typetext and typeparagraph; the latter call is unnecessary, as appending a carriage return (character 13) to the string being printed is enough to force the cursor to a new line. When printing blocks of text, I now add the text to a wide string variable, and only pass that variable to the typetext method when all the text has been added.

So how does one add bold text? As it turns out, it's very simple, although this method negates the above optimisation. First of all, I define a new variant, wrdSel:

wrdSel:= wrdApp.selection;

This by itself simplifies the typing of the original code, for now I can replace wrdApp.selection with wrdSel, thus saving 10 characters each time. This may not seem important, but it does save wear and tear of the fingers. The 'selection' object has a 'font' record which holds a 'bold' property, and in order to print bold, all one has to do is turn this property on (and afterwards turn it off). So the simple code now becomes

var

 wrdApp, wrdDoc, wrdSel: variant;

begin

 wrdApp:= CreateOleObject ('Word.Application');

 wrdApp.visible:= false;

 wrdDoc:= wrdApp.documents.add;

 wrdDoc.select;

 wrdSel:= wrdApp.selection;

 wrdSel.typetext ('Wow - I''m adding ');

 wrdSel.font.bold:= integer (true);

 wrdSel.typetext ('bold');

 wrdSel.font.bold:= integer (false);

 wrdSel.typetext (' text to a Word document from Delphi' + #13);

 wrdApp.visible:= true;

 wrdSel:= unassigned;

 wrdDoc:= unassigned;

 wrdApp:= unassigned;

end;

Fortunately, I doubt whether I'll be using bold text very much in the body of a print-out; it's more likely that a paragraph heading will be in bold and the paragraph body in regular text. In this case, all the body can be loaded into one string and then printed.

I don't know how long I've been searching for this holy grail, but I'm pleased now that I've found it. I imagine that soon there'll be a new grail to search for.

Heron

My previous entry, a ten year old review of Heron's "River of fortune", seemed to come out of nowhere. What inspired it was receiving a double cd of all the tracks that Heron recorded for Pye's "progressive" label Dawn in the early 70s.

I made reference to the "penny tour" - this took place at the beginning of November 1970. Reasoning that for the admission price of one old penny there was no way that I could lose, I went to the concert. I know that four groups appeared, but two obviously made no impression as I could never remember who they were. One of the two groups that I do remember was Comus, who might have had Lindsay Cooper (later to play with National Health and Henry Cow) in their ranks at that time. The other group was Heron.

So impressed was I with their performance that I immediately went and bought their eponymous album. If I remember correctly, this was via mail from some pre-Virgin outfit which offered discounts, so I have no way of knowing whether the album actually hit the shops.

The simple arrangements - the record was recorded "live" in a field, supposedly with no overdubs - caught and tickled my ear, and some of the songs accompanied me throughout the years. Unfortunately, an accident with a record player managed to leave the opening song on side two, "Lord and Master", with a huge scratch, making it unplayable.

At the beginning of the 90s, in a fit of nostalgia, I discovered that there had been a cd release for Heron called "The best of ... plus", which featured songs from both of their albums as well as a few oddities. Whilst the cd was better than nothing, it wasn't really what I wanted, which was the first album in its entirety, sequenced as per the vinyl. "River of fortune" was interesting in its exhumation of the old songs, but sounded too modern for the material.

So I was delighted when I found the new compilation, which begins with the first album in toto. Here's the sole review at Amazon, which sums things up nicely: I can't believe how good this album is - imagine if in the summer 1970 Simon and Garfunkel, the Beatles and Crowded House (no kidding) got together and recorded a hazy summer filled acoustic album in a field complete with heavenly vocal harmonics and real background bird song and field sounds - this album is that and more. You will NOT be disappointed, a true gem of a find; how this band didn't end up massive is a true mystery to me. I'll put money on it being your new fave album within 10 minutes of listening.

Apart from the delight of being reunited with old friends who haven't lost their charm, it's also very instructive to compare the songs from the first album with their other material. Most fascinating from my point of view is "Harlequin 2 (long version)", an alternate take of this exquisite song. I remember from the penny concert when Gerald Moore introduced it by saying that "harlequin is such a lovely word" - so much so that he wrote several numbered Harlequin songs. After the song part of H2 ends, there is a brief pause and then a longish (by Heron standards) instrumental section, followed by variations on the song's chorus. The instrumental section is adorned by several instruments being played out of time, explaining why the song was cut at the end of the vocal section and so appeared enigmatic. I wrote that this is an alternate take; it may be the same basic take as the final version, but mixed slightly differently and without the Hammond organ overdubs, which give the lie to the "recorded live in a field" statement. Actually, H2 is the only song which sounds as if overdubs were added.

I am reminded of the quote which I put in the 'River of fortune' review: we shortened a couple of the original recordings because of cock-ups. The out of time instrumental contributions certainly fall into the cock-up category.

There is another extra song, 'Rosalind', which sounds like the other songs on the first album. In my review I compared Heron to Crosby, Stills and Nash, but this song sounds much more like Simon and Garfunkel. In fact, listening to the entire first album reveals a division in sound between two camps: on the one hand there are Roy Apps and Tony Pook, whereas on the other hand there is Gerald Moore. The Apps/Pook songs have two part harmonies and are gentle (for example, "Car crash", "For you"), whereas the Moore songs tend to be more adventurous musically (the aforementioned "Harlequin 2") and sound closer to pop. The best songs, naturally, are when everybody contributes fully.

This division becomes clearer on the second album, when bass and drums were added to the Heron mix (and I can also hear electric guitar here and there). All the charm of the first album has been lost, replaced by some homogenous acoustic pop sound which is not particularly good. I have only listened to a few of the songs from the second album; those which included on the 'Best of' compilation are similar but not as good as those from the first album, and those which weren't included are dissimilar and nowhere as good.

It seems very much like I am going to confine my listening to the first album and recommend it to everyone.

Heron - River of fortune

Below is a review which I wrote of the above disc ten years ago; it appeared on the online review magazine, The Greenman Review, but is no longer available there.

------------------------------------------------------------------------------------

If your taste runs to obscure British groups, then you're in luck as you can't get more obscure than Heron. Originating in a Maidenhead folk club in 1968, this group (Tony Pook - vocals; Roy Apps - guitar, vocals; Steve Jones - accordion, piano; Gerald Moore - guitar, mandolin, vocals) achieved a certain amount of notoriety by eschewing the recording studios and making their first eponymous record in a field. In an attempt to publicise their record, they took part in what was known as the 'Penny tour' - the price of admission to the concerts being one (old) British penny. Unfortunately, Heron the record didn't sell vast amounts in the record shops, and although a second album ("Twice as nice at half the price" - also recorded in a field) was released, this too was a commercial failure, and so the group split up - 25 years ago [1972].

Further on down the road in 1997, the "boys" decided to get together again (without Gerald Moore, who had found some success with GT Moore and his reggae guitars, but with Gerry Power), and "River of Fortune" is the result. Of the sixteen songs on this disk (70 minutes long), eight are rerecordings of songs which originally appeared on their first album, three are rerecordings of songs from their second album, and five are new recordings of songs which date from that era but didn't appear on any of their records.

And now to the music: Heron, in my humble opinion, could have been the British answer to Crosby, Stills and Nash: acoustic guitars, a bit of organ or accordion, and sometimes three piece harmonies.

The songs are simpler, less "arty" and more "down to home" than CSN, being generally less pretentious (although Gerald Moore's lyrics tend to the abstract). In keeping with the Heron tradition, ROF was recorded in a field, but the production is much more modern, with reverb added to the vocals and instrumental overdubs added at will. While this intially detracts from the special feel of their earlier albums, it allows one to listen to ROF without an anachronistic feeling.

The songs are pleasant and melodic, well-played without being over-arranged, and there is plenty of variation. Whereas first time around, the songs were short (only one song on their first album was over three minutes long), on this one they are much longer. As Steve Jones wrote to me, "With regard [to] lengthening songs, has it occurred to you that the earlier versions may have been shortened? One of the reasons for including some of the songs was so that we could do them the way they were originally intended (more to the point, we shortened a couple of the original recordings because of cock-ups: it's the problem with spontaneous recording)."

An example of this is Moore's "Harlequin 2", one of my favourites from the original album; here the verses are arranged much as they were, but there are long instrumental interludes, which weren't present on the original. The title song starts off with a burst of Spanish guitar and some moody synthesizer, but then moves into a jolly singalong for the chorus; this track is also extended and features a live-sounding vocal coda.

The general sound of the disk is much fuller, this being due to the influence of Steve Jones the keyboardist, who also served as producer. Obviously he was quite limited 30 years ago as to which instruments he could take into a field, but also the range, variety, quality and usage of keyboard instruments has changed greatly during that time.

The complete track list is as follows: "Car crash", "Lord and Master", "River of fortune", "Wanderer", "Yellow roses", "Stars", "Friend", "I wouldn't mind", "Harlequin 5", "Adagio", "Carnival and penitence", "Summer in the city", "Harlequin 2", "Upon reflection", "Smiling ladies", "Gypsy trails".