Multiple requests to xdata server to run a regular query

[Zohair_Hussain]

I have a regular FireDAC query in my xdata server. Server can receive multiple requests at the same time from different clients to run the query and return data. What would be the best way of handling this situation? Aurelius has a connection pool but what can I do with the regular query?
Can you advise please?
The only thing I could do is to close the query at the end and check when the request comes in if it is active or not. If active just do not do anything. But I am sure this not a good solution.

[wlandgraf (Wagner Landgraf)]

You have three options.

1. Use the connection pool normally, and execute queries using the Aurelius interfaces, this way:

For UPDATE/INSERT/DELETE:

uses {...}, Aurelius.Drivers.Interfaces;  
  
var  
  Statement: IDBStatement;  
  
Statement := Manager.Connection.CreateStatement;  
Statement.SetSQLCommand(TheSQLStatement);  
Statement.Execute;  

for SELECT:

var  
  Statement: IDBStatement;  
  ResultSet: IDBResultSet;  
  
Statement := Manager.Connection.CreateStatement;  
Statement.SetSQLCommand(TheSQLStatement);  
ResultSet := Statement.ExecuteQuery;  
  
while ResultSet.Next do  
  Value := ResultSet.GetFieldValue(SomeFieldName);  
  
Finally, for both types of commands you can set parameter values:  
  
{...}  
Params := TObjectList<TDBParam>.Create;  
try  
  Statement := Manager.Connection.CreateStatement;   
  Params.Add(TDBParam.Create('id', ftInteger, 15));  
  Statement.SetSQLCommand('Delete from Customer Where Id = :id');  
  Statement.SetParams(Params);  
  Statement.Execute;  
finally  
  Params.Free;  
end;  

2. Use the connection pool normally, then get the underlying TFDConnection component:

Component Adapters

var
  MyConnection: IDBConnection;
  FDConnection: TFDConnection;
{...}
  FDConnection := (MyConnection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;

With the TFDConnection in hands, create a new TFDQuery in each request, associated with the TFDConnection

3. Enable FireDac pooling and simply create a new TFDConnection and TFDQuery in each request.

Do not reuse global connections or queries as it's not thread-safe.

[Zohair_Hussain]

Thank you very much.