TMyStoredProc.Open in different thread

[tugrul.helvaci]

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]

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]

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]

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);