ADODataSet.Open bypasses try catch with `ArgumentOutOfRange` exception, hangs application - Delphi 10.2

Question

I maintain an application that runs as a service in a server environment. It is multithreaded, where each thread does work according to a task queue. This task queue is just a list of strings with "job types" as their values. So while several threads may be running, each thread would be a different job, and each thread internally runs just one task at a time.

I'm experiencing an intermittent issue that happens when calling Open on a TADODataSet. Sometimes, not always, and with no discernible pattern, Data.Win.ADODB will throw an EArgumentOutOfRangeException, bypassing my own attempt to catch any exceptions. This exception hangs the entire thread and prevents future execution from being possible until I completely restart the service.

Being relatively new to the world of Delphi, I've been scratching my head at this issue for quite some time, and have struggled to find any answers. My question is: why is this happening, and how do I stop, catch, or fix it?

Here is a snippet of my offending code. This is the method from which the stack trace originates. It is called from another method in the same unit, where I open a different dataset, loop through its records, and on each record call this function to get some info based on the value passed in.

function TFQFoo.DoSomething(IncNo : Int64): string;
var
  ItemList : string;
  MySQL: string;
  ComponentString: string;
begin
  result:='';
  if IncNo<=0 then
    Exit;
  ItemList := '';
  MyQuery.Close;
  MySQL := 'select ID from tbl ' +
    ' where val = ' + IntToStr(IncNo) +
    ' order by col1 DESC, col2, col3';

  try
    try
      MyQuery.CommandText := (MySQL);
      MyQuery.Open;

      while not (MyQuery.EOF) do
      begin
        if (ItemList <> '') then
          ItemList := ItemList + ',';
        ItemList := ItemList +
          MyQuery.FieldbyName('ID').asstring;
        MyQuery.Next;
      end;
    except
      // exception handling code omitted for brevity -- none of it
      // is ever reached, anyway (see below)
    end;
  finally
    MyQuery.Close;
  end;
  Result := ItemList;
end;

The call stack from the exception indicates that it's occurring at Open. No try..catch block will capture the exception and log it for me -- I have to use EurekaLog in order to see any details. The stack trace methods (too big to post here) look like:

1. TFQFoo.DoSomething
2. TDataSet.Open
3. ... internal things
4. TADOConnection.ExecuteComplete
5. CheckForAsyncExecute
6. ...
7. TCustomConnection.GetDataSet
8. TListHelper.GetItemRange

Thinking possibly my TADODataSet component was somehow getting corrupted / its properties altered at runtime, I added some logging to capture that data for me so I could see if something funky was going on there. I didn't see anything, but here it is in case it's pertinent.

object MyQuery: TADODataSet
  AutoCalcFields = False
  CacheSize = 15
  Connection = FGlobals.RIMSDB
  CursorType = ctStatic
  LockType = ltReadOnly
  CommandText = 
    'select ID from tbl  where val = 202005070074 order by col1 ' +
    'DESC, col2, col3'
  ParamCheck = False
  Parameters = <>
  Left = 32
  Top = 216
end

For the curious, this is the method actually throwing the exception, from Data.Win.ADODB. Note the except, which I guess hops over my own try..catch block and sends the exception straight to EurekaLog.

procedure CheckForAsyncExecute;
var
  I: Integer;
begin
  try
    if not Assigned(pError) and Assigned(pRecordset) and
       ((pRecordset.State and adStateOpen) <> 0) then
      for I := 0 to DataSetCount - 1 do
        if (DataSets[I].Recordset = pRecordset) and (eoAsyncExecute in DataSets[I].ExecuteOptions) then
        begin
          DataSets[I].OpenCursorComplete;
          Break;
        end;
  except
    ApplicationHandleException(Self);
  end;
end;

What I have tried:

• Many, many iterations of tweaking component properties on the ADODataSet itself
• Using the CommandText and Parameters from within the designer, and assigning the parameter prior to execution at runtime
• Adding / removing a (NOLOCK) hint to the query itself
• Taken the problem to senior members of my team for input
• Googling (for hours)
• Reading up on Delphi and ADO documentation (not fruitful for this)
• Attempted reproduction - I've never been able to get this to occur on any test system I use. This leads me to think it may be environment-related, but I have absolutely no clue how

My question, restated:

How do I stop this from happening? What am I doing wrong? I don't want to just catch the EArgumentOutOfRangeException; I want to learn why it's happening in the first place and prevent it from happening in the future.

I know that sometimes, the query execution will not return results, but the typical CommandText does not return a result set message is never seen nor encountered due to the lower-level code bypassing my own catch statement. Beyond this, I don't know what else to look for.

I've only found one other occurrence of something similar so far, but it relates just to the exception not getting caught: http://www.delphigroups.info/2/d9/410191.html

Answer 1

The call to ApplicationHandleException(Self) in CheckForAsyncExecute() is swallowing exceptions, which is why your except block is not being triggered:

// in Data.Win.ADODB.pas:

procedure CheckForAsyncExecute;
var
  I: Integer;
begin
  try
    ...
  except
    ApplicationHandleException(Self); // <-- a caught exception is NOT re-raised here!
  end;
end;

Inside of the Data.Win.ADODB unit, caught exceptions will call the unit's own ApplicationHandleException() function, which then calls System.Classes.ApplicationHandleException if assigned, otherwise it simply exits:

// in Data.Win.ADODB.pas:

procedure ApplicationHandleException(Sender: TObject);
begin
  if Assigned(System.Classes.ApplicationHandleException) then
    System.Classes.ApplicationHandleException(Sender);
end;

System.Classes.ApplicationHandleException is initialized to nil.

In both a VCL¹ and FMX app, the TApplication constructor assigns the TApplication.HandleException() method to System.Classes.ApplicationHandleException, where HandleException() ignores EAbort exceptions, and calls the TApplication.OnException event handler (if assigned), the TApplication.ShowException() method, or the System.SyUtils.ShowException() function, depending on the type of exception being handled.

    ^{1: in a VCL TService app, TServiceApplication uses Vcl.Forms.TApplication internally.} TApplication.ShowException() displays the details of the exception to the user in a popup MessageBox and then exits, and System.SysUtils.ShowException() displays the details of the exception to the user in a Console or MessageBox and then exits.

So, at no point does ADO's CheckForAsyncExecute() re-raise a caught exception into user code. And needless to say, displaying a popup MessageBox in a service is not a good idea, as the user will likely not see it so they can dismiss it.

Of course, the best option would be to avoid the EArgumentOutOfRangeException exception from being raised in the first place. But there are other conditions that can also raise exceptions, too.

So, your only option to handle swallowed ADO exceptions yourself, and avoid popup MessageBoxes, is to assign a TApplication.OnException event handler (either directly, or via the TApplicationEvents component).