Sunday, October 20, 2019
Multithreaded Delphi Database Queries With dbGo (ADO)
Multithreaded Delphi Database Queries With dbGo (ADO) 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 Applications In 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 Order Scenario In the well-known scenario where a customer places orders containing items, you might need to display all the orders for a particular 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) Lets say you want to display orders for 3 selected customers in a Delphi list box control. 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; 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. 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. 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; When the 3 customers are selected from the drop down box, we create 3 instances of the CalcThread: 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 With Multithreaded ADO Queries The main code goes in the threads Execute method: 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[0].asString,Qry.Fields[1].AsInteger])) ;       //Canvas Does NOT Allow Drawing if not called through Synchronize       Synchronize(RefreshCount) ;       Qry.Next;     end;  finally     Qry.Free;   end;   CoUninitialize() ; end; There are 3 traps you need to know how to solve when creating multithreaded Delphi ADO database applications: 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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.