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
In my experience the easier way is to use either:
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.