Simultaneous communication issues in DataSnap Delphi server created with RAD Studio 11.2 builder

Question

I'm having trouble with a DataSnap Delphi server that was created using the RAD Studio 11.2 default builder. The server uses HTTP REST communication and connects to an Oracle 11g database. I need multiple devices to communicate with it simultaneously. However, I am having communication errors when more than one device is connected at the same time.

When I only use one device at a time, there are no communication problems. However, when I try to connect two devices simultaneously using the FireDAC component to communicate with the database, errors occur in the communication between FireDAC and the database. If I use the ADODB component to communicate with the database, problems start to occur with three simultaneous devices.

I've already checked the driver and the database, and both work correctly when used by other languages. I've also reviewed the connection close functions and believe they are being handled correctly.

The DataSnap server was created from the RAD Studio 11.2 builder using the default configuration.

In this server I have several functions, all of which communicate with the database to insert, update or select. The functions look like this:

function TMesaControl.acceptMesa(AJSON: TJSONObject): TJSONValue;
Var
  VMesa: TMesaModel;
  VQry: TADOQuery;
  SQL : string;
begin
  try
    VQry := TSistemaControl.GetInstance().Conexao.CriarQuery();
    VMesa := TJson.JsonToObject<TMesaModel>(AJSON);

    SQL := 'INSERT INTO TAB_MOB_MESA ( ';
    SQL := SQL + 'MOB_SEQUENCIA, ';
    SQL := SQL + 'MOB_MESA, ';
    SQL := SQL + 'MOB_STATUS ';
    SQL := SQL + ') VALUES ( ';
    SQL := SQL + Chr(39) + VMesa.MESA_SEQUENCIA + Chr(39) + ', ';
    SQL := SQL + Chr(39) + VMesa.MESA_NUMERO + Chr(39) + ', ';
    SQL := SQL + Chr(39) + VMesa.MESA_STATUS + Chr(39);
    SQL := SQL + ')';

    VQry.SQL.Text := SQL;
    VQry.ExecSQL;
  finally
    Result := TJson.ObjectToJsonObject(VMesa);
    FreeAndNil(VQry);
    FreeAndNil(VMesa);
  end;
end;

The TMesaModel class is just for receiving the JSON data and is simply the data structure I expect to receive.

The TSystemControl class follows below:

unit uSistemaControl;

interface

uses
  uConexao,
  System.SysUtils;

type
  TSistemaControl = class
  private
    FConexao     : TConexao;
    class var FInstance: TSistemaControl;
  public
    constructor Create();
    destructor Destroy; override;
    class function GetInstance: TSistemaControl;
    property Conexao: TConexao read FConexao write FConexao;
  end;

implementation

{ TSistemaControl }

constructor TSistemaControl.Create();
begin
  FConexao := TConexao.Create;
end;

destructor TSistemaControl.Destroy;
begin
  FConexao.Free;
  inherited;
end;

class function TSistemaControl.GetInstance: TSistemaControl;
begin
  if not Assigned(Self.FInstance) then
  begin
    Self.FInstance := TSistemaControl.Create();
  end;
  Result := Self.FInstance;
end;

end.

Finally, this is the TConexao class:

unit uConexao;

interface

uses
   ADODB;

type
  TConexao = class
  private
    ADOConnection: TADOConnection;
    procedure ConfigurarConexao;
  public
    constructor Create;
    destructor Destroy; override;
    function GetConn: TADOConnection;
    function CriarQuery: TADOQuery;
    {
    function CriarStoredProc: TFDStoredProc;
    }
  end;

implementation

uses
  uFormPrincipal;

{ TConexao }

procedure TConexao.ConfigurarConexao;
begin
  ADOConnection.ConnectionString := 'Provider=ORAOLEDB.ORACLE;Data Source=' + database_name + ';User ID=' + user_id + ';Password=' + database_pwd;
  ADOConnection.Open;
end;

constructor TConexao.Create;
begin
  ADOConnection := TADOConnection.Create(nil);

  Self.ConfigurarConexao();
end;

function TConexao.CriarQuery: TADOQuery;
var
  VQuery: TADOQuery;
begin
  VQuery := TADOQuery.Create(nil);
  VQuery.Connection := ADOConnection;
  Result := VQuery;
end;

destructor TConexao.Destroy;
begin
  ADOConnection.Free;
  inherited;
end;

function TConexao.GetConn: TADOConnection;
begin
  Result := ADOConnection;
end;

end.

The variables database_name, user_id and database_pwd are global variables declared when creating the main form.

I would like to know if there is anything I can do to solve this problem.

Answer 1

ADoConnection's (just as FireDac connections) cannot be shared between threads:

Ok to use TADOConnection in threads

This explains that you cannot use the TADOConnection object from multiple threads. Every thread needs to create its own database connection.

FireDac works in a similar way: https://docwiki.embarcadero.com/RADStudio/Sydney/en/Multithreading_(FireDAC)

FireDAC is thread-safe if the following conditions are met: A connection object and all objects associated with it (such as TFDQuery, TFDTransaction, and so on) are used by a single thread at each moment.

Both technologies support connection pooling though to optimize performance.