Running a database query that automates Word in a background thread

Now that Word seems to be automating properly, I thought that I would devote some time to implementing a technique which has eluded me so far. Several of my programs run database queries which take minutes to run, and during this time the programs are unresponsive. The solution is to run the query in a separate thread from the main program, but this is easier said than done. Some programs run database queries which output their results to Word - no user intervention is required, and again it would make the program more responsive if the Word code were handled as a background task.

A few years ago, I read this article which seemed to make implementing background queries easy. Although I tried several times to get this code to work, I was never successful - until last night. Handling such a task requires two special techniques to make it work: neither of these are difficult in themselves, but they both have to be present

• All database components have to be declared in the thread procedure
• If the thread automates Word, then CoInitialize and CoUnInitialize have to be invoked in the thread

I had tried to use the dbExpress database components but discovered that the TSQLConnection component requires many properties to be defined and I was missing a few. So for a change, I decided to go with the Interbase database components which come with Delphi (IBDatabase, et al.). As I have never used these components before, I wrote a small test program which uses these components in a normal manner; this way I could see how the components needed to be connected and which properties have to be defined. Once I knew this, I could transfer the visual definitions to a text definition.

The thread cannot access any of the components defined on the same form; I'm not sure whether this applies to the program's datamodule as well. Just to be on the safe side, I retrieved  the database's physical location from where I store it in the registry, although it may be possible to retrieve this value from the datamodule's TSQLConnection.

Here is part of the code;

function DoWord (p: pointer): longint; stdcall; // see the linked article
 var
  ibdb: TIBDatabase;
  qDefaults: TIBQuery;
  trans: TIBTransaction;
  wrdApp, wrdDoc, wrdSel: variant;

begin
 CoInitialize (nil);
 wrdApp:= GetWordObject;
 wrdApp.visible:= false;
 wrdDoc:= wrdApp.Documents.Add;
 wrdSel:= wrdApp.Selection;

 ibdb:= TIBDatabase.Create (nil);
 with TRegIniFile.create (regpath) do
  begin
   ibdb.databasename:= ReadString ('firebird', progname, '');
   free
  end;

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

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

qDefaults:= TIBQuery.create (nil);
with qDefaults do
 begin
  database:= ibdb;
  transaction:= trans;
  sql.Add ('select * from defaults');
  active:= true;
  while not eof do
   begin
    wrdSel.typetext (fieldbyname ('id').asstring + #13);
    next;
   end;
  close;
  free
 end;

 wrdSel.homekey (wdStory);
 wrdApp.visible:= true;
 wrdApp:= unassigned;
 wrdDoc:= unassigned;
 wrdSel:= unassigned;
 trans.free;
 ibdb.free;
 CoUnInitialize;
 result:= 0
end;

Obviously, I've left out most of the Word code, because that's not relevant to the subject. Now that the technique has been implemented successfully, I have to decide where I am going to use this! It may be that the technique is useful from an academic point of view, but anyway it is good to have another tool in my toolbox.