Connection and Threads ;)

[Guest]

Hello,

I have a Datamodule.
On this DM, there is several MyQuery comps and one MyConnection comp.
The MyConnection Pooling option is set to true.

I open all the myquery comps into a thread.
For each thread, I create a new MyConnection. The created MyConnection has the same options / properties (except his name) as the one defined in the DM.

Code: Select all

  fMyConnection:=TMyConnection.Create(nil) ;
  with fMyConnection do
  Begin

    loginprompt:=false;
    pooling:= TCustomMyDataSet(FDataset).Connection.pooling;
    PoolingOptions  := TCustomMyDataSet(FDataset).Connection.PoolingOptions;

    Server  := TCustomMyDataSet(FDataset).Connection.Server;
    Username := TCustomMyDataSet(FDataset).Connection.Username;
    Password  := TCustomMyDataSet(FDataset).Connection.Password;
    Database := TCustomMyDataSet(FDataset).Connection.Database;
    IsolationLevel  := TCustomMyDataSet(FDataset).Connection.IsolationLevel;
    Port := TCustomMyDataSet(FDataset).Connection.Port;
    ConnectionTimeout := TCustomMyDataSet(FDataset).Connection.ConnectionTimeout;

    options := TCustomMyDataSet(FDataset).Connection.options;

    fMyDefaultConnection :=TCustomMyDataSet(FDataset).Connection;

  End;
TCustomMyDataSet(FDataset).Connection:=fMyConnection;

Data are correctly loaded.

But when I want to disconnect from the MySQL server, my queries opened via m threads are not closed ! They have they proper Myconnection object !

So I'm no more able to close them. I tried to replace the thread MyConnection object with the DM MyConnection object. This only close my query.

Code: Select all

TCustomMyDataSet(FDataset).Connection:=fMyDefaultConnection;

Is there a solution ???

Thanks

[Ikar]

It is the significant specific of ConnectionPooling work - after finishing working with TMyConnection a connection to the server is not broken but left in the pool till a next query to the server appears.

[Guest]

I have perhaps been missunderstood... or maybe, I was not clear enougth with my problem.

Some of my queries / tables are openend into a thread.I create a thread because of the high records I need to fetch. Alos, by doing this, my software will not "hang" while records are fetched.

Some of my queries (lockup tables) are openend in the main thread. As records are quickly loaded, there is no need to put them into a thread.

After opening Thraded and non threaded tables, queries, making things I wanted to do, I want to :

Datamodule.MyConnection.Disconnect;

In a perfect world, this should :
- close all opened queries and tables
- close the mysql connection

In my case, only the tables and queries linked to the Datamodule .MyConnection are closing.

Queries and tables openened into my thread (where I have created a separate Myconnection object for each thread) don't...

I my thread, when I create my Myconnection Object, I recover all the properties from my "Datamodule" Myconnection Object. Both have the pooling property set to true.

Please notice that I don't create any now MyQuery / MyTable object. I just do that :

PDataset := FDataset;

Where PDataset is a Private Dataset from my thread, and FDataset, is the Dataset from the Datamodule form I choose to open in the thread.

[Ikar]

If you use DataModule.FDataset why you need to create a new instance of TMyConnection? You can use already existent DataModule.FConnection!

[Guest]

Humm, If don't create a MyConnection object, i'm getting an "Command Out of synch" with copmpress option set to false.... and a packet out of order with the compress option set to true.

Here is my unit source code :

Code: Select all

unit MyDataSetThread;

interface

uses
  Classes, DB,cxGridDBTableView,DBAccess, MyAccess,dialogs;

type

  // ---------------------------------------------------------------------------
  // Class that encapsulate a TDataset into a thread
  //   Remark :   TDataSet must be correctly set.
  // ---------------------------------------------------------------------------
  TDataSetThread = class(TThread)
  protected
    FcxGridDBTableView : TcxGridDBTableView ; // cxGridView auquel est branché le DataSet
    fMyConnection,fMyDefaultConnection : TMyConnection;
    fFieldsCaptions: TStrings;
    FDataSet : TDataSet;       // DataSet encapsulé
    FDataSource : TDataSource; // DataSource auquel est branché le DataSet
    FMessages : TStrings;      // Pour stocker les messages éventuels
    FMessage : string;         // Message à écrire
    procedure initialisation(pDataSet : TDataSet; pDataSource : TDataSource; pcxGridDBTableView : TcxGridDBTableView;pFieldsCaptions,pMessages : TStrings);
    procedure WriteMessage;
    procedure ProcessEnd;
    procedure Execute; override;
    procedure ThreadOnTerminate(Sender: TObject);
  public
    // Creation with a Dataset only
    constructor Create(pDataSet : TDataSet;  pFieldsCaptions : TStrings = nil;pMessages : TStrings = nil); overload;
    // Creation with a TDataSource : in this case, his DataSet is  encapsulated
    constructor Create(pDataSource : TDataSource;  pFieldsCaptions : TStrings = nil;pMessages : TStrings = nil); overload;
    // Creation with a TcxGridDBTableView : in this case, his DataSet is  encapsulated
    constructor Create(pcxGridDBTableView : TcxGridDBTableView;  pFieldsCaptions : TStrings = nil;pMessages : TStrings = nil); overload;
  end;

  TDataSetExportThread = class(TDataSetThread)
  private
    FFileName : string;
  protected
    procedure Execute; override;
  public
    constructor Create(pDataSet : TDataSet; pFileName : string; pFieldsCaptions : TStrings = nil;pMessages : TStrings = nil); overload;
    constructor Create(pDataSource : TDataSource; pFileName : string; pFieldsCaptions : TStrings = nil;pMessages : TStrings = nil); overload;
    property FileName : string read FFileName write FFileName;
  end;

implementation

uses
  SysUtils;

{ TDataSetThread }

procedure TDataSetThread.initialisation(pDataSet: TDataSet; pDataSource: TDataSource;pcxGridDBTableView : TcxGridDBTableView; pFieldsCaptions,pMessages: TStrings);
begin
  FDataSource := pDataSource;
  FDataSet := pDataSet;
  FcxGridDBTableView:= pcxGridDBTableView;



  if assigned(FDataSource) then
    FDataSource.DataSet := nil;      // Unlink the Dataset from the Datasource

  fMyConnection:=TMyConnection.Create(nil) ;
  with fMyConnection do
  Begin

    loginprompt:=false;
    pooling:= TCustomMyDataSet(FDataset).Connection.pooling;

    PoolingOptions  := TCustomMyDataSet(FDataset).Connection.PoolingOptions;

    Server  := TCustomMyDataSet(FDataset).Connection.Server;
    Username := TCustomMyDataSet(FDataset).Connection.Username;
    Password  := TCustomMyDataSet(FDataset).Connection.Password;
    Database := TCustomMyDataSet(FDataset).Connection.Database;
    IsolationLevel  := TCustomMyDataSet(FDataset).Connection.IsolationLevel;
    Port := TCustomMyDataSet(FDataset).Connection.Port;
    ConnectionTimeout := TCustomMyDataSet(FDataset).Connection.ConnectionTimeout;

    options := TCustomMyDataSet(FDataset).Connection.options;


    fMyDefaultConnection :=TCustomMyDataSet(FDataset).Connection;



    connect;
  End;
  TCustomMyDataSet(FDataset).Connection:=fMyConnection;
  FMessages := pMessages;
  FreeOnTerminate := True;           // Thread destructs himsefl when finished
  FFieldsCaptions:=pFieldsCaptions;
  OnTerminate := ThreadOnTerminate;

end;

procedure TDatasetThread.ThreadOnTerminate(Sender: TObject);
Begin
    sender:=nil;
End;

constructor TDataSetThread.Create(pcxGridDBTableView : TcxGridDBTableView;pFieldsCaptions: TStrings ; pMessages: TStrings);
begin
  inherited Create(true);          // thread is halted when created
  initialisation(pcxGridDBTableView.DataController.DataSet, pcxGridDBTableView.DataController.DataSource,pcxGridDBTableView, pFieldsCaptions,pMessages);
  resume;
end;

constructor TDataSetThread.Create(pDataSource: TDataSource; pFieldsCaptions: TStrings ;pMessages: TStrings);
begin
  inherited Create(true);          // thread is halted when created
  initialisation(pDataSource.DataSet, pDataSource,nil, pFieldsCaptions,pMessages);
  resume;
end;


constructor TDataSetThread.Create(pDataSet: TDataSet; pFieldsCaptions,pMessages: TStrings);
begin
  inherited Create(true);          // thread is halted when created
  initialisation(pDataSet, nil,nil, pFieldsCaptions,pMessages);
  resume;
end;

procedure TDataSetThread.WriteMessage;
begin
  if assigned(FMessages) then
    FMessages.Add(FMessage);
end;

procedure TDataSetThread.Execute;
begin
  inherited;
  try
    FDataSet.Open;
  except
    on e: Exception do
    begin
      FMessage := e.Message;
      // We write the message in FMessages
      // as FMessages could be shared with other threads (a
      //  TMemo for example), we Synchronize   it
      Synchronize(WriteMessage);
    end;
  end;
  Synchronize(ProcessEnd);
end;

procedure TDataSetThread.ProcessEnd;
var i,j : integer;
begin
  //disable controls
  if assigned(FFieldsCaptions) then
  Begin
    FDataSet.disablecontrols;
    FDataSet.FieldDefs.Clear;
    FDataSet.FieldDefs.Updated:=false;
    FDataSet.FieldDefs.Update;
  End;

  // we link the Dataset to the datasource
  if assigned(FDataSource) then
    FDataSource.DataSet := FDataSet;

  if assigned(FcxGridDBTableView) then
  Begin
    FcxGridDBTableView.BeginUpdate;
    FcxGridDBTableView.ClearItems ;
    FcxGridDBTableView.DataController.CreateAllItems ;
    FcxGridDBTableView.EndUpdate;
  End;
  //enable controls
  FDataSet.enablecontrols;

end;

end.

[Guest]

In the code above, remove the obsolete "TDataSetExportThread = class(TDataSetThread)"

[Ikar]

> i'm getting an "Command Out of synch" with copmpress option set to false....
> and a packet out of order with the compress option set to true.


Most likely you use the old version of MyDAC. Try to update MyDAC to version 3.50.0.18 and repeat the test.

[Guest]

This is the latest one !, in the about box, I can see version 3.50.0.18 !

I guess you can easyly reproduce my Error :

Just create a new poject
Add a datamodule

On this Datamodule put :
1 MyConnection comp
2 MyQuery com


Associate datasources to the Dataset.

Now on the main form

Put either (if you have it) 2 cxGrids or 2 DBGRid
Link the grids to the corresponding Datasources.

Add the MyThreadedDAtaset unit to the main form

create a button and in the Onclick event write this :

Code: Select all

  try

    DM.myconnection.LoginPrompt:=true;
    DM.myconnection.connect;
   //Let's create the new thread....
    TDataSetThread.Create(fmMain.cxGridDBTableView1);
    TDataSetThread.Create(fmMain.cxGridDBTableView2);
  except
    on e: exception do
    begin
       //I'm doing this because when you use a custom dialog box, 
      //Clicking the 'Cancel' button wil raise an silent exception
       raise Exception.Create(e.message) ;
    end;

TDataSetThread.Create

[Ikar]

Did I understand you correctly that you use a single connection simultaneously from the different threads? In this case any errors are possible - starting from "Command out of sync" to "Access Violation". If the reason of the problem is another please send us small complete sample to mydac support address (mydac*crlab*com).

[Guest]

I'm opening some queries on my main thread.
This queries are linked to a single Connection.

If I open queries into a thread, and use my single connection, i'm getting errors (commands out of synch, net packet out of orders and so on).

To avoid this errors, in the thread, before opening the query, I create a new connection. When doing that, it is working But....

When I close my software, I'm doing a
Datamodule.Myconnection.close;

Only the queries opened in the main thread are closed.
All queries opened in a thread are not closed.

I understand that Queries opened in a thread are linked to another connection component. How can I change this property (without closing the query and losing my fetched records) and tells the Query to use the single connection ?

I'm I doing something so meaningless ???

[GEswin]

I think that what this user want to do is just load data in background so that appz doesn't look freezed when loading huge tables. I have the same problem too , i would like to load big amount of data without having the application freezed for a time. Just doing this by a thread or at some point having an application.processmessages called from time to time. I didn't find a solution right now.

[Guest]

Yes , that's it !

[Ikar]

Using threads in this case is quite reasonable. The only you have to take care is not to apply for this MyQuery and MyConnection till completing fetch. Time spent on opening an additional connection in comparison with fetch time will be insignificant.

[kenny]

Hi,
I'm actually also finding a way to open the huge table in background silently to avoid freeze screen...

I'd tried to apply the way in this topic but also get the same errors on it.

Hopefully it's a way to achieve it....

[Guest]

Hi all,

Ikar, could you provide us a way for doing this ?, opening dataset in the background interrest a lot of people.

Thank you for your precious help