I want to limit the time that my queries take to retrieve their data, this is supposed to be done through the ResourceOptions.CmdExecTimeout property, that controls Execute & Open commands.

But when I try it, it gets ignored and my query remains unresponsive instead of triggering an exception. See this example:

procedure TfrmMainForm.btnSynchronousOpenClick(Sender: TObject);
begin
  var Query := TFDQuery.Create(Self);
  Query.Connection := cnConnection;
  Query.SQL.Text := '''
     DECLARE @X int
     WHILE 1=1   -- Infinite Loop
       SET @X = 1
     ''';
    
  Query.ResourceOptions.CmdExecTimeout := 1000;
  try
    Query.Open;
    ShowMessage('Query Opened');
  except
    on E: EFDDBEngineException do
      if E.Kind = ekCmdAborted then
        ShowMessage('Query Aborted');
  end;
end;

The SQL is an infinite loop and I expect the Query.Open to trigger an exception after the 1000ms timeout, but nothing happens.

The cnConnection connects to a SQL Server through the Microsoft ODBC Driver 17 for SQL Server.

  object cnConnection: TFDConnection
    Params.Strings = (
      'Database=agilITy_0002'
      'Server=10.0.0.48'
      'OSAuthent=Yes'
      'MARS=yes'
      'ODBCAdvanced=TrustServerCertificate=Yes'
      'DriverID=MSSQL')
    Connected = True
    LoginPrompt = False
    Left = 60
    Top = 152
  end

I've also tried to open that query asynchronously, but again no exception is triggered, nothing happens.

procedure TfrmMainForm.QueryOpened(Dataset: TDataset);
begin
  ShowMessage('Query Opened');
end;
    
procedure TfrmMainForm.btnAynchronousOpenClick(Sender: TObject);
begin
  var Query := TFDQuery.Create(Self);
  Query.Connection := cnConnection;
  Query.SQL.Text := '''
    DECLARE @X int
    WHILE 1=1   -- Infinite Loop
      SET @X = 1
    ''';
    
  Query.ResourceOptions.CmdExecMode := amAsync;
  Query.ResourceOptions.CmdExecTimeout := 1000;
  Query.AfterOpen := QueryOpened;
  try
    Query.Open;
  except
    on E: EFDDBEngineException do
      if E.Kind = ekCmdAborted then
        ShowMessage('Query Aborted');
  end;
end;

Do you see what I'm doing wrong? How can I set a timeout for the opening of my queries?

I want to limit the time that my queries take to retrieve their data, this is supposed to be done through the ResourceOptions.CmdExecTimeout property, that controls Execute & Open commands.

But when I try it, it gets ignored and my query remains unresponsive instead of triggering an exception. See this example:

procedure TfrmMainForm.btnSynchronousOpenClick(Sender: TObject);
begin
  var Query := TFDQuery.Create(Self);
  Query.Connection := cnConnection;
  Query.SQL.Text := '''
     DECLARE @X int
     WHILE 1=1   -- Infinite Loop
       SET @X = 1
     ''';
    
  Query.ResourceOptions.CmdExecTimeout := 1000;
  try
    Query.Open;
    ShowMessage('Query Opened');
  except
    on E: EFDDBEngineException do
      if E.Kind = ekCmdAborted then
        ShowMessage('Query Aborted');
  end;
end;

The SQL is an infinite loop and I expect the Query.Open to trigger an exception after the 1000ms timeout, but nothing happens.

The cnConnection connects to a SQL Server through the Microsoft ODBC Driver 17 for SQL Server.

  object cnConnection: TFDConnection
    Params.Strings = (
      'Database=agilITy_0002'
      'Server=10.0.0.48'
      'OSAuthent=Yes'
      'MARS=yes'
      'ODBCAdvanced=TrustServerCertificate=Yes'
      'DriverID=MSSQL')
    Connected = True
    LoginPrompt = False
    Left = 60
    Top = 152
  end

I've also tried to open that query asynchronously, but again no exception is triggered, nothing happens.

procedure TfrmMainForm.QueryOpened(Dataset: TDataset);
begin
  ShowMessage('Query Opened');
end;
    
procedure TfrmMainForm.btnAynchronousOpenClick(Sender: TObject);
begin
  var Query := TFDQuery.Create(Self);
  Query.Connection := cnConnection;
  Query.SQL.Text := '''
    DECLARE @X int
    WHILE 1=1   -- Infinite Loop
      SET @X = 1
    ''';
    
  Query.ResourceOptions.CmdExecMode := amAsync;
  Query.ResourceOptions.CmdExecTimeout := 1000;
  Query.AfterOpen := QueryOpened;
  try
    Query.Open;
  except
    on E: EFDDBEngineException do
      if E.Kind = ekCmdAborted then
        ShowMessage('Query Aborted');
  end;
end;

Do you see what I'm doing wrong? How can I set a timeout for the opening of my queries?

• sql-server
• delphi
• sql-server-2019
• firedac
• delphi-12-athens

• 1 I suspect that Query.Open is not one of the documented situations where the timeout is implemented. As TFDQuery is derived from TDataSet, then only fetching data will be covered. I would step through the sources of FireDac to see where it hangs, is it in the opening stage or during fetching. – mjn Commented Mar 31 at 17:12

1 Answer 1

The problem was my query. Once I changed that simple test loop for a real SELECT statement then the timeout property already worked as expected.