Translate

ebook【Delphi跨平台資料庫程式設計火速上手】電子書出版 (CHT)

Delphi 跨平台資料庫程式設計火速上手,是本關於整合 Delphi 的跨平台技術打造 2-Tier 架構的跨平台 APP 的入門技術書。 全書沒有需要理解的技術知識,只講套路。 力求短時間把製作 APP 的工法熟悉,未來要開發其它的應用程式也能舉一反三。 底下...

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:

沒有留言:

張貼留言