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.

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.
type
  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.
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:
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:
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:

沒有留言:

張貼留言

How to use URLEncode in Delphi

URL編碼,也稱【百分號編碼(Percent-encoding)】 由於 URL 不能直接接受 UTF8 文字,所以要先轉成百分號編碼後,如此網址才能順利送給 HTTP Server 從 Indy 加入 Delphi 後,就可以用 IdURI.TIdURI 單元進行編碼 ...