Using Firedac in a multi-threaded application

Question

I am currently working on a multi-threaded server application, and I plan to use Firedac to access data. From the docs here: http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Multithreading_(FireDAC) , it seems that the same TFDConnectionand / or TFDQueryshould not be accessed from multiple threads at the same time (instead these objects must be created based on threads).

Therefore, the example shown in the previous link, centralizes TFDConnectionand TFDQueryin the object TThread. However, in my case, I have no control over the creation of threads (which is controlled by the server environment). Therefore, I limit the life cycle of objects TFDConnectionand the TFDQuerylifetime of the procedure, which can potentially be called from several threads:

procedure TAPMFiredacTemplate.executeSQL(sql:string);
  var
  oConn: TFDConnection;
  oQuery: TFDQuery;
  begin
  oConn := TFDConnection.Create(nil);
  oQuery := TFDQuery.Create(nil);
  try
    oConn.ConnectionDefName := self.ConnectionDefinitionName;
    oConn.Connected := True;
    oQuery.Connection := oConn;
    oQuery.Open(sql);
    while not oQuery.Eof do
    begin
      // process query
      oQuery.Next;
    end;

  finally
    if assigned(oQuery) then
    begin
      oQuery.Close;
      oQuery.Free;
    end;
    if assigned (oConn) then
    begin
      oConn.Connected := False;
      oConn.Free;
    end;

  end;

Is this approach valid? Does performance arise during the systematic creation of an object TFDQuery?

Note . To improve performance, I plan to use the definition of a private federated joint (which is used TFDConnection). Therefore, from my understanding, even when I release TFDConnection, the physical connection is not destroyed, but returned instead of the pool:

oParams := TStringList.Create;
oParams.Add('Database=localhost:c:\apm\databases\mydb.FDB');
oParams.Add('User_Name=xxxxx');
oParams.Add('Password=xxxxxx');
oParams.Add('Pooled=True');
FDManager.AddConnectionDef('Firebird_Pooled','FB',oParams);
FDManager.Active := True;

Answer 1

, ( , - Indy).

, ( ).

, . , , . .

, TIdTCPServer :

type
  { server context in the meaning of a "task" class }
  TMyContext = class(TIdServerContext)
  private
    FQuery: TFDQuery;
  public
    constructor Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil); override;
    destructor Destroy; override;
    property Query: TFDQuery read FQuery;
  end;

  TForm1 = class(TForm)
    IdTCPServer1: TIdTCPServer;
    FDConnection1: TFDConnection;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure IdTCPServer1Connect(AContext: TIdContext);
    procedure IdTCPServer1Disconnect(AContext: TIdContext);
    procedure IdTCPServer1Execute(AContext: TIdContext);
    procedure IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
  end;

implementation

constructor TMyContext.Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil);
begin
  inherited;
  FQuery := TFDQuery.Create(nil);
end;

destructor TMyContext.Destroy;
begin
  FQuery.Free;
  inherited;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  Params: TStrings;
begin
  Params := TStringList.Create;
  try
    Params.Add('Database=localhost:C:\MyDatabase.fdb');
    Params.Add('User_Name=xxxxx');
    Params.Add('Password=xxxxx');
    Params.Add('Pooled=True');
    { add the definition to the global connection manager singleton }
    FDManager.AddConnectionDef('FirebirdPooled', 'FB', Params);
  finally
    Params.Free;
  end;

  { use the added definition and establish the connection to the DB }
  FDConnection1.ConnectionDefName := 'FirebirdPooled';
  FDConnection1.Connected := True;

  { setup the context class, add a port binding and start the TCP server }
  IdTCPServer1.ContextClass := TMyContext;
  IdTCPServer1.Bindings.Add.Port := 6000;
  IdTCPServer1.Active := True;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  { stop the TCP server and destroy all pooled physical connections }
  IdTCPServer1.Active := False;
  FDManager.CloseConnectionDef('FirebirdPooled');
end;

procedure TForm1.IdTCPServer1Connect(AContext: TIdContext);
begin
  { client just connected, assign to the context query object the pooled
    connection and a command text }
  TMyContext(AContext).Query.Connection := FDConnection1;
  TMyContext(AContext).Query.SQL.Text := 'SELECT * FROM MyTable WHERE ID=:ID';
  { preparing the query will acquire one physical connection from the pool
    as this method internally opens the connection }
  TMyContext(AContext).Query.Prepare;
end;

procedure TForm1.IdTCPServer1Disconnect(AContext: TIdContext);
begin
  { client just disconnected, return the physical connection to the pool }
  TMyContext(AContext).Query.Connection.Close;
end;

procedure TForm1.IdTCPServer1Execute(AContext: TIdContext);
var
  ID: Integer;
  Query: TFDQuery;
begin
  { read an integer from socket }
  ID := AContext.Connection.IOHandler.ReadInteger;
  { just a reference helper }
  Query := TMyContext(AContext).Query;

  { fill the parameter and refresh the prepared query object dataset }
  Query.Params[0].AsInteger := ID;
  Query.Refresh;

  while not Query.Eof do
  begin
    { process the dataset somehow }
    Query.Next;
  end;

  { do not close the dataset, keep it prepared for the next possible request }
end;

procedure TForm1.IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
begin
  if AException is EFDException then
  begin
    { something bad happened with the DB, this is a base FireDAC exception
      class but you can be more specific of course }
  end;
end;