Another database query in a background thread

In the last blog, I wrote about running a database query that automates Word in a background thread, no mean technical feat, but not necessarily useful. After discussing the subject with my client, the redoubtable Occupation Psychologist, we saw that there wouldn't be much need for this technique, at least for the moment.

On the other hand, every now and then we force our flagship product to recalculate averages and standard deviations for thirty plus scales. Whilst calculating the average is not difficult, calculating the standard deviation and updating users' scores against the new standard deviations is very time consuming. This is the sort of task that would be speeded up by calculating in a separate thread.

As the program is a MDI (multiple document interface) program, the user can display several forms at once, in the same way that Windows displays several programs at once. Unlike Windows, the program is only single threaded, meaning that although several forms can be displayed at once, only one is actually 'working'. Using a background thread means that more than one form can be 'working' at the same time.

With regard to the example form that we chose: the user is presented with a list of scales, a grid and a progress bar. Upon choosing a scale, the form begins running a query and updates the progress bar in order to let the user know that something is happening. When the query is complete, the grid fills with the calculated data. The new scenario which I envisage for this form would be:

• The user chooses a scale to be calculated
• The form activates the background thread (query) and minimises itself
• When the thread (query) has completed its work, it notifies the form that it has finished
• The form reads the result of the calculation from a database table and returns to its original size (not maximising, which would fill the entire screen)

Originally I thought that it would be possible to hide the form while it was calculating then show it upon completion, but it seems that it is not possible to hide MDI child forms; instead I have to minimise it and then restore. In terms of programming, this doesn't require any extra work, but I think that the end result would have been slightly less confusing had I been able to hide the form.

The last post showed how to create a thread in a simple way without resort to Delphi's TThread object via the Windows API. A limitation to this approach is that the thread cannot access any of the form variables, especially not any of the visible components of the form. In the previous post, I got around this limitation by outputting the query's result to Word; here I get around the limitation by outputting the query's result to a database table. The TThread object has a 'synchronize' method which allows for the background thread to access the form's visible components, but it seemed like overkill here.

Another limitation of the API approach is that only one variable can be passed to the thread (in the Word example, no variable was passed). The problem here is that several variables have to be passed to the thread and several variables have to be passed back to the form. The thread has to know which scale has been chosen for calculation and it has to know what the handle of the form is, in order that the thread can send a message to the form's handle upon completion. Ideally, the thread should also be told the location of the database. The thread has to inform the form how many records have been processed: this was done by using one of the parameters which the thread sends to the form upon completion.

Whilst the thread cannot access any of the form variables, it can access any of the variables defined as global to the form. In other words, if the form definition looks like this

interface

uses ...

const
 TH_MESSAGE = WM_USER + 12;
 TH_Finished = 217; // random number

type
 TScaleAnswers = class(TForm)
  lb: TListBox;
  DBGrid1: TDBGrid;
  DataSource1: TDataSource;
  Label1: TLabel;
  Label2: TLabel;
  qScales: TSQLQuery;
  XLBtn: TBitBtn;
  qScaleAnswers: TSimpleDataSet;
  qClear: TSQLQuery;
private
 working: boolean;
 procedure ThreadMessage (var Message: TMessage); message TH_MESSAGE;
public
end;

implementation

var
 scale: longint;
 thread: dword

Then the thread can access scale and thread but not working. An invocation specific number (not surprisingly, the thread's id) is stored in thread whilst the chosen scale's internal number is stored in scale. The calling form's handle is passed as the parameter to the thread. Thread is used as a key when inserting data into the database table in order to facilitate its retrieval and eventual deletion.

The thread is created in the following manner

procedure TScaleAnswers.lbDblClick(Sender: TObject);
begin
 if not working then
  begin
   working:= true;
   scale:= sendmessage (lb.handle, lb_getitemdata, lb.ItemIndex, 0);
   CreateThread (nil, 0, @DoQuery, pointer (self.handle), 0, thread);
   windowstate:= wsMinimized;
  end
end;

After the thread has finished its calculation, it sends a message to the form:

sendmessage (formhandle, th_message, th_finished, pcount);

This is handled in the form by the following code:

procedure TScaleAnswers.ThreadMessage (var Message: TMessage);
begin
 if Message.WParam = TH_FINISHED then
  begin
   with qScaleAnswers do
     begin
      connection:= dm.sqlconnection1;
      dataset.params[0].asinteger:= thread;
      open
     end;
    label2.caption:= inttostr (message.lparam);
    windowstate:= wsNormal;
    working:= false;
   end;
end;

How does this code 'know' to execute? Referring back to the form's definition, there is a procedure defined thus: ThreadMessage (var Message: TMessage); message TH_MESSAGE. This means that the above procedure will be invoked whenever the form is sent a message with the TH_MESSAGE parameter. A sanity check (probably unnecessary) is made against the wparam parameter. This code activates a SimpleDataSet to retrieve the correct data (utilising thread in a 'where' clause) which is then displayed. Note how label2 receives its data from the message's lparam parameter, which is equivalent to the thread's pcount variable.

Conceivably, this form could be invoked simultaneously with several different scales being calculated. In practice, the calculation occurs so quickly on my computer (the database server is local) so that I never get a chance to do anything else, although I suspect that on the client's network it won't be quite so fast. It transpires that updating the original form's progress bar was taking a good proportion of the calculation time, so removing it improved up the calculation's apparent speed. But even so, the main program is unresponsive until the calculation has completed.