Article #16231: Performing database queries in a background thread

 Technical Information Database

TI1231D.txt - Performing database queries in a background thread

Category   :Database Programming
Platform   :All-32Bit
Product    :All32Bit,   

Description:
This document explains how to perform queries in a 
background thread by using the TThread class.  For 
information on the general usage of the TThread class, 
please refer to the Borland documentation and to the online 
help.  You should be aware of how to use Delphi's 
database components to understand the this TI's contents. 

Two requirements must be met in order to perform a threaded 
query.  First, the query to be threaded must be contained 
within its own session by using a separate TSession 
component.  Therefore, you would place a TSession component 
on your form and assign it's name to the SessonName property 
of the TQuery component to be used in the thread.  You must 
use a separate TSession component for each TQuery component 
to be used in a thread. If you are also using a TDataBase 
component, a separate TDataBase must be used for each 
threaded query as well. The second requirement is that the 
threaded TQuery component must not be connected to a 
TDataSource in the context of the thread in which it will be 
executed.  This must be done in the context of the primary 
thread. 

The code example below illustrates this process.  This unit 
shows a form which contains two each of the following 
comopnents: TSession, TDatabase, TQuery, TDataSource and 
TDBGrid.  These components have the following property 
settings:

Session1 
	Active	True;
	SessionName	"Ses1"

DataBase1
	AliasName	"IBLOCAL"
	DatabaseName	"DB1"
	SessionName	"Ses1"

Query1
	DataBaseName	"DB1"
	SessionName	"Ses1"
	SQL.Strings	"Select * from employee"

DataSource1
	DataSet	""

DBGrid1
	DataSource	DataSource1

Session2
	Active	True;
	SessionName	"Ses2"

DataBase2
	AliasName	"IBLOCAL"
	DatabaseName	"DB2"
	SessionName	"Ses2"

Query2
	DataBaseName	"DB2"
	SessionName	"Ses2"
	SQL.Strings	"Select * from customer"

DataSource2
	DataSet	""

DBGrid1
	DataSource	DataSource2


Notice that the DataSet property for both TDataSource 
components do not refer to anything initially. This will be 
set at run-time as illustrated in the code.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, 
Forms, Dialogs,
  StdCtrls, Grids, DBGrids, DB, DBTables;

type

  TForm1 = class(TForm)
    Session1: TSession;
    Session2: TSession;
    Database1: TDatabase;
    Database2: TDatabase;
    Query1: TQuery;
    Query2: TQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    GoBtn1: TButton;
    procedure GoBtn1Click(Sender: TObject);
  end;

  TQueryThread = class(TThread)
  private
    FSession: TSession;
    FDatabase: TDataBase;
    FQuery: TQuery;
    FDatasource: TDatasource;
    FQueryException: Exception;
    procedure ConnectDataSource; 
    procedure ShowQryError;
  protected
    procedure Execute; override;
  public
    constructor Create(Session: TSession; DataBase:
      TDatabase; Query: TQuery; DataSource: TDataSource); 
      virtual;
  end;


var
  Form1: TForm1;

implementation

constructor TQueryThread.Create(Session: TSession; DataBase: 
  TDatabase; Query: TQuery; Datasource: TDataSource);
begin
  inherited Create(True);    // Create thread in a 
suspendend state
  FSession := Session;       // connect all private fields
  FDatabase := DataBase;
  FQuery := Query;
  FDataSource := Datasource;
  FreeOnTerminate := True;   // Have thread object free 
itself when terminated
  Resume;                    // Resume thread execution
end;

procedure TQueryThread.Execute;
begin
  try
    { Run the query and connect the datasource to the TQuery 
      component by calling ConnectDataSource from main 
      thread (Synchronize used for this purpose)}
    FQuery.Open;
    Synchronize(ConnectDataSource);
  except
    { Capture exception, if one occurs, and handle it in the 
      context of the main thread (Synchonize used for this 
      purpose. }
    FQueryException := ExceptObject as Exception;
    Synchronize(ShowQryError);
  end;
end;

procedure TQueryThread.ConnectDataSource;
begin
  FDataSource.DataSet := FQuery;  // Connect the DataSource 
to the TQuery
end;

procedure TQueryThread.ShowQryError;
begin
  Application.ShowException(FQueryException); // Handle the 
exception
end;

procedure RunBackgroundQuery(Session: TSession; DataBase: 
TDataBase; Query: TQuery; DataSource: TDataSource);
begin
  { Create a TThread instance with the various parameters. }
  TQueryThread.Create(Session, Database, Query, DataSource);
end;


{$R *.DFM}

procedure TForm1.GoBtn1Click(Sender: TObject);
begin
  { Run two separate queries, each in their own thread }
  RunBackgroundQuery(Session1, DataBase1, Query1, 
Datasource1);
  RunBackgroundQuery(Session2, DataBase2, Query2, 
Datasource2);
end;

end.


The TForm1.GoBtn1Click method is an event handle for a 
button click event.  This event handler calls the 
RunBackgroundQuery procedure twice, each time passing a 
different set of database components. RunBackgroundQuery 
creates a separate instance of the TQueryThread class, 
passing the various database components to its constructor 
which in turn assigns them to the appropriate TQueryThread 
private data fields.  

The TQueryThread contains two user-defined procedures: 
ConnectDataSource and ShowQryError. ConnectDataSource 
connects FDataSource.DataSet to FQuery. However, it does 
this in the primary thread by using the TThread.Synchronize 
method. ShowQryError handles the exception in the context of 
the primary thread, again by using the Synchronize method.   
The Create constructor and Execute method are explained in 
the code's comments. 


Reference:
 

3/30/99 11:18:31 AM
 

Last Modified: 01-SEP-99