Delphi Multi-Threaded database query (ADO)

Delphi Multi-Threaded database query (ADO)

There are 3 common problems with multithreaded database queries:
1. CoInitialize is not called (CoInitialize is not called), so you must call CoInitialize and CoUninitialize before using any Dbgo object. Failure to invoke CoInitialize results in an exception of "CoInitialize is not called".

2. Canvas is not allowed to paint (canvas does not allow drawing); Therefore, the main thread must be notified through the synchronize process to access any control on the master form.

3. You cannot use the main ADO connection (main tadoconnection cannot be used!); Therefore, threads cannot use tadoconnection objects in the main thread, and each thread must create its own database connection.

Delphi2007 after installation there is a Shared/data file in the X:/program files/common files/codegear Dbdemos.mdb directory, which is used as an example of testing. The Customer table in Dbdemos.mdb holds the client information, and the order information is saved in the Orders table.

The test program flow is basically this: put the tadoconnection and Tquery controls on the main form, and at startup this tquery isolate the customer code CUSTNO and company name from the Customers table, put it in three combox boxes, Select the customer company name in three list boxes, establish three threads according to the customer code corresponding to the company name, and query the sales date in the Orders table Saledate respectively into the listbox.

{main form code}
Unit Main;

Interface

Uses
Windows, Messages, sysutils, variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, Stdctrls;

Type
TForm2 = Class (Tform)
Combobox1:tcombobox;
Combobox2:tcombobox;
Combobox3:tcombobox;
Listbox1:tlistbox;
Listbox2:tlistbox;
Listbox3:tlistbox;
Button1:tbutton;
Adoconnection1:tadoconnection;
Adoquery1:tadoquery;
Label1:tlabel;
Label2:tlabel;
Label3:tlabel;
Procedure Formcreate (Sender:tobject);
Procedure Button1Click (Sender:tobject);
Private
{Private declarations}
Public
{Public declarations}
End

Var
Form2:tform2;

Implementation

Uses adothread;

{$R *.DFM}

Procedure Tform2.button1click (Sender:tobject);
Const
Sql_const= ' Select saledate from orders where Custno =%d ';
Var
C1,c2,c3:integer;
s1,s2,s3:string;
Begin
Get three selection box customer's code
C1:=integer (Combobox1.items.objects[combobox1.itemindex]);
C2:=integer (Combobox2.items.objects[combobox2.itemindex]);
C3:=integer (Combobox3.items.objects[combobox3.itemindex]);
Generating SQL query statements
S1:=format (Sql_const,[c1]);
S2:=format (SQL_CONST,[C2]);
S3:=format (SQL_CONST,[C3]);
Three threads querying at the same time
Tadothread.create (S1,LISTBOX1,LABEL1);
Tadothread.create (S2,LISTBOX2,LABEL2);
Tadothread.create (S3,LISTBOX3,LABEL3);
End

Procedure Tform2.formcreate (Sender:tobject);
Var
strsql:string;
Begin
Strsql:= ' SELECT custno,company from customer ';
Adoquery1.close;
ADOQuery1.SQL.Clear;
ADOQUERY1.SQL.ADD (strSQL);
Adoquery1.open;
Combobox1.clear;
Combobox2.clear;
Combobox3.clear;
Fill in the ComboBox with customer company and related Custno
While not adoquery1.eof do
Begin
ComboBox1.AddItem (Adoquery1.fields[1].asstring,
TObject (Adoquery1.fields[0]. Asinteger));
Adoquery1.next;
End
ComboBox2.Items.Assign (Combobox1.items);
ComboBox3.Items.Assign (Combobox1.items);
The first one is selected by default
Combobox1.itemindex: = 0;
Combobox2.itemindex: = 0;
Combobox3.itemindex: = 0;
End

End.
{ADO Querying multithreaded units}
Unit Adothread;

Interface

Uses
Classes,stdctrls,adodb;

Type
Tadothread = Class (TThread)
Private
{Private declarations}
Flistbox:tlistbox;
Flabel:tlabel;
connstring:widestring;
fsqlstring:string;
Procedure Updatecount;
Protected
Procedure Execute; Override
Public
Constructor Create (Sql:string;lb:tlistbox; Lab:tlabel);
End

Implementation

Uses Main,sysutils,activex;

{Tadothread}

Constructor Tadothread.create (sql:string; Lb:tlistbox; Lab:tlabel);
Begin
connstring:=form2.adoconnection1.connectionstring;
flistbox:=lb;
Flabel:=lab;
Fsqlstring:=sql;
Inherited Create (False);
End

Procedure Tadothread.execute;
Var
Qry:tadoquery;
I:integer;
Begin
{Place thread code here}
Freeonterminate:=true;
CoInitialize (nil); Must be called (requires uses ActiveX)
Qry:=tadoquery.create (nil);
Try
qry.connectionstring:=connstring; Must have a connection of their own
Qry.close;
Qry.SQL.Clear;
QRY.SQL.ADD (fsqlstring);
Qry.open;
Flistbox.clear;
For I: = 0 to + do//in order to perform a long repetition of the data set 101 times
Begin
While not qry.eof and not Terminated do
Begin
Flistbox.additem (Qry.fields[0].asstring,nil);
If you do not call synchronize, the canvas Does not allow Drawing
Synchronize (Updatecount);
Qry.next;
End
Qry.first;
Flistbox.additem (' ******* ', nil);
End
Finally
Qry.free;
End
CoUninitialize;
End

Procedure Tadothread.updatecount;
Begin
Flabel.caption:=inttostr (FListBox.Items.Count);
End

End.

The results of the program run can see three threads executing simultaneously. The first 32 thread conditions are the same as the results of a query.

http://blog.csdn.net/tercel99/article/details/5783593

Delphi Multi-Threaded database query (ADO)