2011/07/05

Multithreaded Delphi Database Queries with dbExpress (DBX)

This article is extended the content of Multithreaded Delphi Database Queries, which is mainly dbGO(ADO) change to Dbexpress(DBX).

The same opinion is as below mentioned:
1.      Solve: “Canvas does not allow drawing”.
2.      Main TSQLConnection Cannot be used!

And the superiority of DBX is:
1.      DBX is read only and unidirectional.
2.      Device is very flexible.
See also: http://delphi.about.com/od/database/a/dbexpressguide.htm

Because SQLDataSet’s property always ‘string’ and need add SQLConnection, so we can modify this code:
Multithreading in Dbexpress (DBX)

Let's say you want to display orders for 3 selected customers in a Delphi list box control.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
  TCalcThread = class(TThread)
  private
    procedure RefreshCount;
  protected
    procedure Execute; override;
  public
    ConnStr : string;
    SQLString : string;
    cnThread: TSQLConnection;
    ListBox : TListBox;
    Priority: TThreadPriority;
    TicksLabel : TLabel;

    Ticks : Cardinal;
  end;

Well, DBX is not support ACCESS in Delphi default setting. So we used “Open Source ODBC Dbexpress driver” in the project. And add it’s demo: dbx_access_connect. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
procedure TDbxThreadedForm.ReConnection();
begin
    //
    // MSACCESS
    //
    dbx_access_connect.AccessConnect(SQLConnection,
      // mdb_file_name:
        'C:\Program Files\Common Files\CodeGear Shared\Data\dbdemos.mdb',
      // DNS_NAME
        '',
      // DirectOdbc
        True,
      // LoginPrompt
        False,
      // UserName
        '',
      // Password
        '',
      // AdditionalOptions
        'coEnableBCD=0',
      // bUnicodeOdbcApi
        True,
      // bAnsiStringField
        False,
      // bUnicodeDriver
        True
    );
end;

When the 3 customers are selected from the drop down box, we create 3 instances of the CalcThread:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
procedure TDbxThreadedForm.Button1Click(Sender: TObject);
var
  s, sg: string;
  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 DBX Queries

The main code goes in the thread's Execute method:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
procedure TCalcThread.Execute;
  var
    Qry : TSQLDataSet;
    i : integer;
  begin
    inherited;
 
    ticks := GetTickCount();
    //CoInitialize(nil); //CoInitialize was not called
    cnThread := DbxThreadedForm.SQLConnection.CloneConnection;
    Qry := TSQLDataSet.Create(nil);
    try
      Qry.SQLConnection := cnThread;
      Qry.CommandText := SQLString;
 
      ListBox.Clear;
      Qry.Open;
      for i:= 0 to 100 do
      begin
        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 Synhronize
          Synchronize(RefreshCount);
 
          Qry.Next;
        end; //while
        if Terminated then break;
        Qry.First;
        ListBox.Items.Add('*---------*');
      end; // for
    finally
      Qry.Free;
          FreeAndNil(cnThread);
    end;
    //CoUninitialize();
    ticks := GetTickCount - ticks;
    TicksLabel.Caption := 'Ticks: ' + IntToStr(ticks);
  end; //TCalcThread.Execute;

Conclusion
DBX will auto be called CoInitialize and CoUninitialize. So we can to comment it. :P

Attention: Similarly, you cannot use the TSQLConnection object from the main thread (application). (Remember free the TSQLConnection object)

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 DBX and Delphi.

Source:
Download Demo Application

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)