Asynchronous TADOQuery's OnFetchComplete not synchronized to main thread

2k views Asked by At

When using TADOQuery with [eoAsyncFetchNonBlocking] and attaching to OnFetchComplete event I found that OnFetchComplete is not executing in the main thread (tested in XE4 and XE8). I assume this a bug*, since most of us will do work in the UI on these type of event. I believe this to be the source of some problems in a larger project and I need a workaround.

[EDIT] *After reading the ADO documentation I know concede that this may not be a bug, but the multithreading problem remains.

Is there an elegant way to force to have code in this handler to execute on the main thread? I don't want to use a timer (but if that's the only solution I'll take it). Alternatively, is there an ADO Synchronization object I can wait for here or some other form of signaling to the ADO provider?

Here is a simplified sample that shows that the problem. My project is more complex with a factory creating and filling these datasets, but it would be analogous here to attaching the dataset to a grid inside ADOQuery1FetchComplete.

unit Unit4;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Data.Win.ADODB, Vcl.StdCtrls;

type
  TForm4 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure ADOQuery1FetchComplete(DataSet: TCustomADODataSet;
      const Error: Error; var EventStatus: TEventStatus);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    FMainThreadID : DWORD;
  public
    { Public declarations }
  end;

var
  Form4: TForm4;

implementation

{$R *.dfm}

procedure TForm4.ADOQuery1FetchComplete(DataSet: TCustomADODataSet;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Assert(FMainThreadID = GetCurrentThreadId); //this assertion fails!
  // I need UI code here to run  FMainThreadID
end;

procedure TForm4.Button1Click(Sender: TObject);
begin
   ADOQuery1.Open;
end;


procedure TForm4.FormCreate(Sender: TObject);
begin
    FMainThreadID := GetCurrentThreadId;
end;

end.

And the dfm simply has the query set with ExecuteOptions = [eoAsyncFetchNonBlocking] and OnFetchComplete handled.

object Form4: TForm4
  Left = 0
  Top = 0
  Caption = 'Form4'
  ClientHeight = 186
  ClientWidth = 258
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 24
    Top = 88
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    ExecuteOptions = [eoAsyncFetchNonBlocking]
    OnFetchComplete = ADOQuery1FetchComplete
    Parameters = <>
    SQL.Strings = (
      'SELECT * FROM TABLENAME')
    Left = 144
    Top = 16
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString = 
      'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
      'fo=False;Initial Catalog=DBNAME;Data Source=.\INSTANCENAME'
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    Left = 40
    Top = 16
  end
end

[EDIT] A suggestion was made to use TThread.Sychronize, but this is not a Delphi Thread.

If the GetCurrentThreadId is not sufficient evidence that the handler is called from another thread here are the callstacks of the main and problematic thread (I added a sleep in the main thread for good measure)

Main thread sleeping

:77d0c7bc ntdll.ZwDelayExecution + 0xc
:7745104f KERNELBASE.Sleep + 0xf
Unit6.TForm6.btnQueryClick($32BC00)
Vcl.Controls.TControl.Click
Vcl.StdCtrls.TCustomButton.Click
Vcl.StdCtrls.TCustomButton.CNCommand(???)
Vcl.Controls.TControl.WndProc((48401, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((48401, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.StdCtrls.TButtonControl.WndProc((48401, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.Controls.TControl.Perform(???,???,7275840)
Vcl.Controls.DoControlMsg(???,(no value))
Vcl.Controls.TWinControl.WMCommand((273, (), 1344, 0, (), 7275840, 0))
Vcl.Forms.TCustomForm.WMCommand((273, (), 1344, 0, (), 7275840, 0))
Vcl.Controls.TControl.WndProc((273, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((273, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.Forms.TCustomForm.WndProc((273, 1344, 7275840, 0, 1344, 0, (), 1344, 111, (), 0, 0, ()))
Vcl.Controls.TWinControl.MainWndProc(???)
System.Classes.StdWndProc(2829362,273,1344,7275840)
:759b8e71 user32.CallNextHookEx + 0xb1
:759b90d1 ; C:\windows\SysWOW64\user32.dll
:759b932c ; C:\windows\SysWOW64\user32.dll
:759b9529 ; C:\windows\SysWOW64\user32.dll
:77d107d6 ntdll.KiUserCallbackDispatcher + 0x36
:759be4a9 ; C:\windows\SysWOW64\user32.dll
:711f19e4 ; C:\windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.9600.17810_none_a9edf09f013934e0\comctl32.dll
:711f1a7b ; C:\windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.9600.17810_none_a9edf09f013934e0\comctl32.dll
:759b8e71 user32.CallNextHookEx + 0xb1
:759b90d1 ; C:\windows\SysWOW64\user32.dll
:759bddd5 user32.CallWindowProcW + 0x95
Vcl.Controls.TWinControl.DefaultHandler(???)
:00532947 TWinControl.DefaultHandler + $EB
:00532836 TWinControl.WndProc + $5CA
:00544cdd TButtonControl.WndProc + $71
:004c9162 StdWndProc + $16
:759b8e71 user32.CallNextHookEx + 0xb1
:759b90d1 ; C:\windows\SysWOW64\user32.dll
:759ba66f ; C:\windows\SysWOW64\user32.dll
:759ba6e0 user32.DispatchMessageW + 0x10
:005bb158 TApplication.ProcessMessage + $F8
:00040000

Other thread calling the handler

Unit6.TForm6.QueryFetchComplete($288B3E0,nil,esOK)
Data.Win.ADODB.TCustomADODataSet.FetchComplete(nil,89849068,Pointer($3299D8) as _Recordset)
:6b7ab81d ; C:\Program Files (x86)\Common Files\System\ado\msado15.dll
:6b7ab4b6 ; C:\Program Files (x86)\Common Files\System\ado\msado15.dll
:6b7a17c8 ; C:\Program Files (x86)\Common Files\System\ado\msado15.dll
:6b7b616f ; C:\Program Files (x86)\Common Files\System\ado\msado15.dll
:69038991 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:69038bd6 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:69038d54 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:69037a02 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:69021205 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:69038034 ; C:\Program Files (x86)\Common Files\System\msadc\msadce.dll
:77a07c04 KERNEL32.BaseThreadInitThunk + 0x24
:77d2ad1f ntdll.RtlInitializeExceptionChain + 0x8f
:77d2acea ntdll.RtlInitializeExceptionChain + 0x5a
1

There are 1 answers

8
EProgrammerNotFound On BEST ANSWER

In my experience the easier way is to use either:

Synchronize or TThread.Queue

This is not a bug or at least not a VCL bug. This behavior is handled by the provider and we cannot say it is not following the specification because there is no specification about how to manage the asynchrony of those events. All the spec says is the following:

adAsyncFetchNonBlocking

Indicates that the main thread never blocks while retrieving. If the requested row has not been retrieved, the current row automatically moves to the end of the file.

This is an example of code warning the main thread that the execution is completed:

procedure TForm1.ADOQuery1FetchComplete(DataSet: TCustomADODataSet;
  const Error: Error; var EventStatus: TEventStatus);
begin
  TThread.Synchronize(nil,
    procedure
    begin
      ShowMessage('FetchData Completed');
    end
    );
end;

Update:

I confirmed this. It will work for versions 6, 7, XE4 and XE7 (I don't have other version here). There is nothing wrong with using Synchronize to inject your code in order to execute into the main thread context. Also, I want to get your attention to the fact that DataSet is merely a pointer (actually a reference) to your ADOQuery Object, so you don't necessarily have to reference it on your anonymous method, this is an important fact for older versions like 6 or 7, because anonymous methods does not exists.

BONUS READING: EVENTS