TMyStoredProc.Open in different thread

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tugrul.helvaci
Posts: 2
Joined: Thu 31 May 2018 09:17

TMyStoredProc.Open in different thread

Post by tugrul.helvaci » Thu 31 May 2018 09:20

Hi, i have one TMyStoredProc on my form. I want to open TMyStoredProc in a different thread rather than main thread. So, i know i should use different connection in that thread but how ?

I tried like this but i think it is not a correct way.

Code: Select all

procedure TForm1.LoadData;
var
  ACloneConnection : TMyConnection;
begin
  TThread.CreateAnonymousThread(
    procedure
    begin
      ACloneConnection := TMyConnection.Create(nil);

      try
        ACloneConnection.LoginPrompt := false;

        ACloneConnection.Server   := MainConnection.Server;
        ACloneConnection.Port     := MainConnection.Port;
        ACloneConnection.Username := MainConnection.UserName;
        ACloneConnection.Password := MainConnection.Password;
        ACloneConnection.Database := MainConnection.Database;

        ACloneConnection.Open;

        MainStoredProc.Close; // This is a TMyStoredProc on my Form.
        MainStoredProc.Connection := ACloneConnection;
        MainStoredProc.Open;
        MainStoredProc.Connection := MainConnection; // This is a TMyConnection on my Form.
      finally
        if Assigned(ACloneConnection) then
          FreeAndNil(ACloneConnection);
      end;
    end
  ).Start;
end;

ViktorV
Devart Team
Posts: 2358
Joined: Wed 30 Jul 2014 07:16

Re: TMyStoredProc.Open in different thread

Post by ViktorV » Thu 31 May 2018 15:36

In your case, you should organize a thread-safe access to the TMyStoredProc component on the form, for example, using critical sections. Then you do not have to create a separate instance of TMyConnection in each thread.

tugrul.helvaci
Posts: 2
Joined: Thu 31 May 2018 09:17

Re: TMyStoredProc.Open in different thread

Post by tugrul.helvaci » Fri 01 Jun 2018 05:55

Hi there,

I had a problem with TMyStoredProc and I had asked for a help in #258619 ticket and you had responsed me in a incorrect way. I will be happy if you share a DETAILED RESPONSE.

I would like to give more details about the situation;

`TMyConnection` connection object is used in all over the form by another components. If i call it 'main connection', that means i will need to create another connection in different thread to keep working on other component, which means to keep application working on background while the connection keeps running.

---

Code: Select all

procedure TForm1.LoadData;
var
  ACloneConnection : TMyConnection;
begin
  TThread.CreateAnonymousThread(
    procedure
    begin
      ACloneConnection := TMyConnection.Create(nil);

      try
        ACloneConnection.LoginPrompt := false;

        ACloneConnection.Server   := MainConnection.Server;
        ACloneConnection.Port     := MainConnection.Port;
        ACloneConnection.Username := MainConnection.UserName;
        ACloneConnection.Password := MainConnection.Password;
        ACloneConnection.Database := MainConnection.Database;

        ACloneConnection.Open;

        MainStoredProc.Close; // This is a TMyStoredProc on my Form.
        MainStoredProc.Connection := ACloneConnection;
        MainStoredProc.Open;
        MainStoredProc.Connection := MainConnection; // This is a TMyConnection on my Form.
      finally
        if Assigned(ACloneConnection) then
          FreeAndNil(ACloneConnection);
      end;
    end
  ).Start;
end;
---
If we back to my question;

I want to "OPEN" TMyStoredProc in a different thread rather than main thread. So, i know i should use different connection in that thread but how ?

Let me explain in more detailed; my procs potentially may have large data and it may take a lot on executing. That will cause the application status to "not responding", if we use 'Application's Main Thread'. That is not the way we want for an user experience.

What i am looking for is Async Executing. I will be happy if you share a DETAILED RESPONSE.

ViktorV
Devart Team
Posts: 2358
Joined: Wed 30 Jul 2014 07:16

Re: TMyStoredProc.Open in different thread

Post by ViktorV » Fri 01 Jun 2018 09:12

We apologize for the insufficiently clarified previous answer.
You can have infinite amount of queries running through one connection in one thread, but you should not run several queries from several threads through one connection.
Therefore, in the case specified by you, you are right - you should use a separate instance of TMyConnection in each thread. Only, for correct operation, you still have to make a thread-safe access to the TMyStoredProc component on the form, for example, using critical sections. You correctly create a separate instance of TMyConnection in each thread and you can use the code you provided to solve your task by adding a thread-safe access to the TMyStoredProc component on the form. Example:

Code: Select all

var
    CriticalSection: TCriticalSection;
...

procedure TForm1.FormCreate(Sender: TObject);
begin
  CriticalSection:=TCriticalSection.Create;
end;

procedure TForm1.LoadData;
var
  ACloneConnection : TMyConnection;
begin
  TThread.CreateAnonymousThread(
    procedure
    begin
      ACloneConnection := TMyConnection.Create(nil);
      CriticalSection.Enter;
      try
        ACloneConnection.LoginPrompt := false;

        ACloneConnection.Server   := MainConnection.Server;
        ACloneConnection.Port     := MainConnection.Port;
        ACloneConnection.Username := MainConnection.UserName;
        ACloneConnection.Password := MainConnection.Password;
        ACloneConnection.Database := MainConnection.Database;

        ACloneConnection.Open;

        MainStoredProc.Close; // This is a TMyStoredProc on my Form.
        MainStoredProc.Connection := ACloneConnection;
        MainStoredProc.Open;
        MainStoredProc.Connection := MainConnection; // This is a TMyConnection on my Form.
      finally
        if Assigned(ACloneConnection) then
          FreeAndNil(ACloneConnection);
        CriticalSection.Leave;
      end;
    end
  ).Start;
end;
Also, to solve your task, you can use a separate instance of TMyStoredProc in each thread.

Also you can use the TMyConnection.Assign method to set the connection properties. Instead of the lines

Code: Select all

ACloneConnection.Server := MainConnection.Server;
  ACloneConnection.Port := MainConnection.Port;
  ACloneConnection.Username := MainConnection.UserName;
  ACloneConnection.Password := MainConnection.Password;
  ACloneConnection.Database := MainConnection.Database;
you can use

Code: Select all

  ACloneConnection.Assign(MainConnection);

Post Reply