By design a Delphi application runs in one thread. To speed up some parts of the application you might want to decide to add several simultaneous paths of execution in your Delphi application.
Multithreading in Database ApplicationsIn most scenarios, database applications you create with Delphi are single threaded - a query you run against the database needs to finish (processing of the query results) before you can fetch another set of data.
To speed up data processing, for example, fetching data from the database to create reports, you can add an additional thread to fetch and operate on the result (recordset).
Continue reading to learn about the 3 traps in multithreaded ADO database queries:
- Solve: "CoInitialize was not called".
- Solve: "Canvas does not allow drawing".
- Main TADoConnection cannot be used!
Customer - Orders - ItemsIn the well known scenario where a customer places orders containing items, you might need to display all the orders for a particual customer along the total number of items per each order.
In a "normal" single threaded application you would need to run the query to fetch the data then iterate over the recordset to display the data.
If you want to run this operation for more than one customer, you need to sequentially run the procedure for each of the selected customers.
In a multithreaded scenario you can run the database query for every selected customer in a separate thread - and thus have the code execute several times faster.
Multithreading in dbGO (ADO)Let's say you want to display orders for 3 selected customers in a Delphi list box control.
This is the interface part of a custom thread class we are going to use to fetch and operate on all the orders for a selected customer.
type TCalcThread = class(TThread) private procedure RefreshCount; protected procedure Execute; override; public ConnStr : widestring; SQLString : widestring; ListBox : TListBox; Priority: TThreadPriority; TicksLabel : TLabel; Ticks : Cardinal; end;
Every order gets displayed as an item in a list box control (ListBox field). The ConnStr field holds the ADO connection string. The TicksLabel holds a reference to a TLabel control that will be used to display thread executing times in a synchronized procedure.
The RunThread procedure creates and runs an instance of the TCalcThread thread class.
When the 3 customers are selected from the drop down box, we create 3 instances of the CalcThread:
function TADOThreadedForm.RunThread(SQLString: widestring; LB:TListBox; Priority: TThreadPriority; lbl : TLabel): TCalcThread; var CalcThread : TCalcThread; begin CalcThread := TCalcThread.Create(true) ; CalcThread.FreeOnTerminate := true; CalcThread.ConnStr := ADOConnection1.ConnectionString; CalcThread.SQLString := SQLString; CalcThread.ListBox := LB; CalcThread.Priority := Priority; CalcThread.TicksLabel := lbl; CalcThread.OnTerminate := ThreadTerminated; CalcThread.Resume; Result := CalcThread; end;
var s, sg: widestring; c1, c2, c3 : integer; begin s := ' SELECT O.SaleDate, MAX(I.ItemNo) AS ItemCount ' + ' FROM Customer C, Orders O, Items I ' + ' WHERE C.CustNo = O.CustNo AND I.OrderNo = O.OrderNo ' ; sg := ' GROUP BY O.SaleDate '; c1 := Integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]) ; c2 := Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]) ; c3 := Integer(ComboBox3.Items.Objects[ComboBox3.ItemIndex]) ; Caption := ''; ct1 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c1, sg]), lbCustomer1, tpTimeCritical, lblCustomer1) ; ct2 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c2, sg]), lbCustomer2, tpNormal,lblCustomer2) ; ct3 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c3, sg]), lbCustomer3, tpLowest, lblCustomer3) ; end;
Traps and Tricks - Multithreaded ADO QueriesThe main code goes in the thread's Execute method:
There are 3 traps you need to know how to solve when creating multithreaded Delphi ADO database applications:
procedure TCalcThread.Execute; var Qry : TADOQuery; k : integer; begin inherited; CoInitialize(nil) ; //CoInitialize was not called Qry := TADOQuery.Create(nil) ; try // MUST USE OWN CONNECTION // Qry.Connection := Form1.ADOConnection1; Qry.ConnectionString := ConnStr; Qry.CursorLocation := clUseServer; Qry.LockType := ltReadOnly; Qry.CursorType := ctOpenForwardOnly; Qry.SQL.Text := SQLString; Qry.Open; while NOT Qry.Eof and NOT Terminated do begin ListBox.Items.Insert(0, Format('%s - %d', [Qry.Fields.asString,Qry.Fields.AsInteger])) ; //Canvas Does NOT Allow Drawing if not called through Synchronize Synchronize(RefreshCount) ; Qry.Next; end; finally Qry.Free; end; CoUninitialize() ; end;
- CoInitialize and CoUninitialize must be called manually before using any of the dbGo objects. Failing to call CoInitialize will result in the "CoInitialize was not called" exception. The CoInitialize method initializes the COM library on the current thread. ADO is COM.
- You *cannot* use the TADOConnection object from the main thread (application). Every thread needs to create its own database connection.
- You must use the Synchronize procedure to "talk" to the main thread and access any controls on the main form.
Be sure to download the demo application to explore and learn more about multithreaded queries with ADO and Delphi.