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.
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.
To create the TwoFish, I have copied Fish Facts components
IBTable1 into a data module
twoFishDB. This data module contains no code, only these three components. I have also set
Then I created the frame
twoFishDB_GUI which uses the data module
twoFishDB. This frame contains an unconnected
DataSource1 and all data-aware components that are placed on the Fish Facts form –
TDBMemo. They are all connected to the
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.
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
AfterConstruction creates a background worker and specifies task initializer and finalizer (
.Finalize). Delegates provided to these two functions (
FinalizeDatabase) are called when background worker task is created and before it is destroyed.
procedure TfrmTwoFishDB_GUI.AfterConstruction; begin inherited; FWorker := Parallel.BackgroundWorker .Initialize(InitializeDatabase) .Finalize(FinalizeDatabase) .Execute; end; procedure TfrmTwoFishDB_GUI.BeforeDestruction; begin CloseConnection; inherited; end; procedure TfrmTwoFishDB_GUI.CloseConnection; begin if assigned(FWorker) then begin FWorker.Terminate(INFINITE); FWorker := nil; end; FreeAndNil(FDataSet); end;
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
procedure TfrmTwoFishDB_GUI.FinalizeDatabase(const taskState: TOmniValue); begin FreeAndNil(FDataModule) end; procedure TfrmTwoFishDB_GUI.InitializeDatabase(var taskState: TOmniValue); begin FDataModule := TdmTwoFishDB.Create(nil); taskState := FDataModule; end;
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); begin FWorker.Schedule( FWorker.CreateWorkItem(databaseName), FWorker.Config.OnExecute(ConnectToDatabase).OnRequestDone( procedure (const Sender: IOmniBackgroundWorker; const workItem: IOmniWorkItem) begin if assigned(onConnectionOpen) then onConnectionOpen(Self, workItem.FatalException); end )); end;
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
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); var dataModule: TdmTwoFishDB; begin dataModule := (workItem.TaskState.AsObject as TdmTwoFishDB); GTwoFishLock.Acquire; //probably only necessary if using InterBase driver try dataModule.IBDatabase1.DatabaseName := workItem.Data.AsString; dataModule.IBDatabase1.Connected := true; finally GTwoFishLock.Release; end; 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
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.
var GTwoFishLock: TOmniCS;
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; begin FWorker.Schedule( FWorker.CreateWorkItem(TOmniValue.Null), FWorker.Config.OnExecute(LoadData).OnRequestDone(DisplayData) ); end;
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]: http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/devwin32/fhxr18643_xml.html [^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); var dataModule : TdmTwoFishDB; resultDS : TClientDataSet; tempProvider: TDataSetProvider; begin dataModule := (workItem.TaskState.AsObject as TdmTwoFishDB); if not dataModule.IBTable1.Active then dataModule.IBTable1.Active := true else dataModule.IBTable1.Refresh; resultDS := TClientDataSet.Create(nil); tempProvider := TDataSetProvider.Create(nil); try tempProvider.DataSet := dataModule.IBTable1; resultDS.Data := tempProvider.Data; finally FreeAndNil(tempProvider); end; workItem.Result := resultDS; // receiver will take ownership end;
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); begin FreeAndNil(FDataSet); if workItem.IsExceptional then ShowMessage('Failed to retrieve data. ' + workItem.FatalException.Message) else begin FDataSet := workItem.Result.AsObject as TClientDataSet; DataSource1.DataSet := FDataSet; end; end;
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); var iFrame: integer; begin 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; OpenConnections; end;
Next, the form is resized to twice the frame size and
OpenConnections is called to
establish database connections in all frames.
procedure TfrmTwoFish.OpenConnections; var frame: TfrmTwoFishDB_GUI; begin for frame in FFrames do frame.OpenConnection(CDatabaseName , procedure (Sender: TObject; FatalException: Exception) begin if assigned(FatalException) then ShowMessage('Failed to connect to the database!') else (Sender as TfrmTwoFishDB_GUI).Reload; end); end;
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); var frame: TfrmTwoFishDB_GUI; begin for frame in FFrames do frame.CloseConnection; end;
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.
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).