OmniThreadLibrary and Databases

Can you tell me how to use databases in combination with OmniThreadLibrary?

Using databases with the OmniThreadLibrary can be quite simple at times; on the other hand, it can also be quite tricky. The main problem with databases is that you have to create database components in the thread that will be using them. As the visual components (as the TDBGrid) must be initialized from the main thread, this implies that you can't directly connect database-aware GUI controls to database components.

Because of that you have to devise a mechanism that transfers database data from the task to the main thread (and also – if the database access is not read-only – a mechanism that will send updates to the task so that they can be applied to the database). In most cases this means that you should ignore database-aware components and just build the GUI without them. In some cases, however, you could do a lot by just splitting the existing database infrastructure at the correct point and leaving the GUI part almost unmodified. This example explores such option.

*An example is included with the OmniThreadLibrary distribution in folder

The basis for this article is the well-known Fish Facts demo program, included in Delphi's Samples[^delphi-samples-fishfact] folder. This is a simple application that uses database-aware controls to display data from an InterBase database.

[^delphi-samples-fishfact]: Samples\Delphi\Database\IBX\FishFact

The Fish Facts demo

I have built a view-only version of Fish Facts called TwoFish which uses two frames, each containing data-aware controls and a background thread which accesses the InterBase data. Both frames are running in parallel and accessing the data at the same time.

The TwoFish demo

Database Model

To create the TwoFish, I have copied Fish Facts components IBDatabase1, IBTransaction1 and IBTable1 into a data module twoFishDB. This data module contains no code, only these three components. I have also set IBDatabase1.Connected and IBTable1.Active to False.

Then I created the frame twoFishDB_GUI which uses the data module twoFishDB. This frame contains an unconnected TDataSource component DataSource1 and all data-aware components that are placed on the Fish Facts form – TDBGrid, TDBImage, TDBText and TDBMemo. They are all connected to the DataSource1.

TwoFish data model

Main TwoFish program creates two frames. Each frame creates a //Background Worker// abstraction that (inside the worker task) creates the data module and activates database and database table (more details are given below).

When data is to be retrieved, the task creates a TClientDataSet and a TDataSetProvider which 'pumps' all data from the IBTable1 to the TClientDataSet. This client data set is then sent to the main form which connects it to the DataSource1. This automatically causes the data to be displayed in the data-aware controls. To keep the example simple, I have disabled data editing.

The most important points of this example are:

- Data module is initialized from the task, not from frame's event handlers. This way it is initialized in the thread that uses it.

- Data module is destroyed before the OnDestroy is called (OnCloseQuery is used for this purpose). If you try to destroy the data module from the OnDestroy, a deadlock will occur inside the Delphi RTL code.

This example shows a different approach to frame-task interaction than the Multiple Workers with Multiple Frames – here the background worker is managed by the frame itself, not by the main form.

Frame and Worker

The frame wraps one background task that operates on the database and contains database-aware controls displaying the database data.

The Background Worker abstraction is created in the AfterConstruction method and destroyed in the BeforeDestruction method. AfterConstruction creates a background worker and specifies task initializer and finalizer (.Initialize and .Finalize). Delegates provided to these two functions (InitializeDatabase and FinalizeDatabase) are called when background worker task is created and before it is destroyed.

procedure TfrmTwoFishDB_GUI.AfterConstruction;
  FWorker := Parallel.BackgroundWorker
procedure TfrmTwoFishDB_GUI.BeforeDestruction;
procedure TfrmTwoFishDB_GUI.CloseConnection;
  if assigned(FWorker) then begin
    FWorker := nil;

You may have noticed that no code was provided to execute work items. The reason behind this is that the background worker will execute different types of requests. Instead of writing if … then tests to detect the work item type and trigger appropriate code, we'll pass the executor function together with each request.

BeforeDestruction destroys the background worker and destroys the FDataSet component (we'll see later why it is used).

Task initializer and finalizer are also very simple – they just create and destroy the data module. The data module is accessible to the background worker through the taskState variable.

procedure TfrmTwoFishDB_GUI.FinalizeDatabase(const taskState: TOmniValue);
procedure TfrmTwoFishDB_GUI.InitializeDatabase(var taskState: TOmniValue);
  FDataModule := TdmTwoFishDB.Create(nil);
  taskState := FDataModule;

Connecting to the Database

Data controls are initially in an unconnected state. They are only connected when the public method OpenConnection is called.

procedure TfrmTwoFishDB_GUI.OpenConnection(const databaseName: string;
  onConnectionOpen: TNotify);
      procedure (const Sender: IOmniBackgroundWorker; 
        const workItem: IOmniWorkItem)
        if assigned(onConnectionOpen) then
          onConnectionOpen(Self, workItem.FatalException);

OpenConnection schedules a work request that contains the database name as a parameter. It also sets the executor function (ConnectToDatabase) and an anonymous function that will be executed after the request is processed (OnRequestDone). This anonymous function returns the 'result' of the request to the OpenConnection caller by calling the onConnectionOpen parameter. ['Result' in this case is exposed as an exception that is triggered if the database connection cannot be established. If the connection can be made, the workItem.FatalException function will return nil.]

The important fact to note is that the OnExecute parameter (ConnectToDatabase) is called from the worker thread and the OnRequestDone parameter (the anonymous function) is called from the thread that created the frame (the main thread).

procedure TfrmTwoFishDB_GUI.ConnectToDatabase(
  const workItem: IOmniWorkItem);
  dataModule: TdmTwoFishDB;
  dataModule := (workItem.TaskState.AsObject as TdmTwoFishDB);
  GTwoFishLock.Acquire; //probably only necessary if using InterBase driver
    dataModule.IBDatabase1.DatabaseName := workItem.Data.AsString;
    dataModule.IBDatabase1.Connected := true;
  finally GTwoFishLock.Release; end;

The data module associated with the worker is accessed through the workItem.TaskState property which gives you access to the taskState variable initialized in the InitializeDatabase method. Database name is taken from the work item parameter (workItem.Data). The database name is set in the IBDatabase component and connection is established (Connected := true). If connection fails, an exception will be raised. This exception is caught by the OmniThreadLibrary and stored in the workItem object where it is later processed by the anonymous method in the OpenConnection method.

The weird Acquire/Release pair is here because of bugs in the gds32.dll – the dynamic library that handles connection to the InterBase. It turns out that gds32 handles parallel connections to the database perfectly well – as long as they are not established at the same time. In other words – you can communicate with the database on multiple connections at the same time (get data, put data, execute SQL commands …) but you cannot establish connections in parallel. Sometimes it will work, sometimes it will fail with a mysterious access violation error in the gds32 code. That's why the twoFishDB_GUI unit uses a global critical section to prevent multiple connections to be established at the same time.

  GTwoFishLock: TOmniCS;

Retrieving the Data

To retrieve data from the database, main unit calls the Reload function. This function is also called inside the frame from the click event on the Reload button.

Reload just schedules a work request without any input. To process the request, LoadData will be called.

procedure TfrmTwoFishDB_GUI.Reload;

LoadData executes in the background worker thread. It uses a temporary TDataSetProvider to copy data to a freshly created TClientDataSet[^embd-copy-db]. During this process, a 'Field not found'[^db-copy-exception] exception is raised twice. If you run the program in the debugger, you'll see this exception four times (twice for each frame). You can safely ignore the exception as it is handled internally in the Delphi RTL and is not visible to the end-user.

[^embd-copy-db]: [^db-copy-exception]: “IBTable1: Field 'SPECIES_NO' not found”

At the end, the TClientDataSet that was created inside the LoadData is assigned to the workItem.Result. It will be processed (and eventually destroyed) in the main thread.

procedure TfrmTwoFishDB_GUI.LoadData(const workItem: IOmniWorkItem);
  dataModule  : TdmTwoFishDB;
  resultDS    : TClientDataSet;
  tempProvider: TDataSetProvider;
  dataModule := (workItem.TaskState.AsObject as TdmTwoFishDB);
  if not dataModule.IBTable1.Active then
    dataModule.IBTable1.Active := true
  resultDS := TClientDataSet.Create(nil);
  tempProvider := TDataSetProvider.Create(nil);
    tempProvider.DataSet := dataModule.IBTable1;
    resultDS.Data := tempProvider.Data; 
  finally FreeAndNil(tempProvider); end;
  workItem.Result := resultDS; // receiver will take ownership

The DisplayData method executes in the main thread after the request was processed (i.e., the data was retrieved). If there was an exception inside the work item processing code (LoadData), it is displayed. Otherwise, the TClientDataSet is copied from the workItem.Result into an internal TfrmTwoFishDB_GUI field and assigned to the DataSource1.DataSet. By doing that, all data-aware controls on the frame can access the data.

procedure TfrmTwoFishDB_GUI.DisplayData(
  const Sender: IOmniBackgroundWorker;
  const workItem: IOmniWorkItem);
  if workItem.IsExceptional then
    ShowMessage('Failed to retrieve data. ' + 
  else begin
    FDataSet := workItem.Result.AsObject as TClientDataSet;
    DataSource1.DataSet := FDataSet;

Main Program

The main program is fairly simple. In the OnCreateEvent two frames are created. Frame references are stored in the FFrames form field, declared as array of TfrmTwoFishDB_GUI.

procedure TfrmTwoFish.FormCreate(Sender: TObject);
  iFrame: integer;
  SetLength(FFrames, CNumFrames);
  for iFrame := 0 to CNumFrames-1 do
    FFrames[iFrame] := CreateFrame(
      CFrameWidth * iFrame, 0, CFrameWidth, CFrameHeight,
      Format('Frame%d', [iFrame+1]));
  ClientWidth := CNumFrames * CFrameWidth;
  ClientHeight := CFrameHeight;

Next, the form is resized to twice the frame size and OpenConnections is called to establish database connections in all frames.

procedure TfrmTwoFish.OpenConnections;
  frame: TfrmTwoFishDB_GUI;
  for frame in FFrames do
    frame.OpenConnection(CDatabaseName ,
      procedure (Sender: TObject; FatalException: Exception)
        if assigned(FatalException) then
          ShowMessage('Failed to connect to the database!')
          (Sender as TfrmTwoFishDB_GUI).Reload;

OpenConnections iterates over all frames and calls OpenConnection method in each one. Two parameters are passed to it – the database name and an anonymous method that will be executed after the connection has been established.

If connection fails, the FatalException field will contain the exception object raised inside the background worker's OpenConnection code. In such case, it will be logged. Otherwise, the connection was established successfully and Reload is called to load data into the frame.

Frames are destroyed from OnCloseQuery. It turns out that Delphi (at least XE2) will deadlock if data modules are destroyed in background threads while OnDestroy is running.

procedure TfrmTwoFish.FormCloseQuery(Sender: TObject; 
  var CanClose: boolean);
  frame: TfrmTwoFishDB_GUI;
  for frame in FFrames do

To recapitulate, most important facts about using databases from secondary threads are:

- Always create non-visual database components in the thread that will be using them. - Always create data-aware controls in the main thread. - Never connect data-aware controls to non-visual database components that were created in a secondary thread. - Wrap TIBDatabase.Connected := true in a critical section because of gds32 bugs. - Destroy database tasks from OnCloseQuery, not from OnDestroy if you are using data modules in a secondary thread. - Establish some mechanism of data passing between the database (secondary thread) and the view (main thread).

book/howto/databases.txt · Last modified: 2012/11/14 12:38 by gabr
Recent changes RSS feed Debian Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki