Problem using TADOConnection in thread

[David Clegg]

I have a really weird problem with using a TADOConnection within a thread
(using Delphi 5). If I create the TADOConnection in the threads Execute
method (ensuring that CoInitialize and CoUninitialize is called), I
cannot start up Windows Explorer straight away (takes about 20-30
seconds). The Windows Start Bar also stops responding until Explorer
comes up. As soon as the thread has stopped, Explorer pops up.

If I create the TADOConnection component outside of the Execute method,
all works as expected. Also, I can get it to work correctly if I create
the TADOConnection in the Execute method and then call its Open method
(which shows the Login Prompt dialog). Has anyone else encountered this
oddity? Here is my test threads code, and I can post a sample app if
anyone is interested in playing with it.

unit TestThread;

interface

uses
Classes, ADODB;

type
TTestThread = class(TThread)
private
{ Private declarations }
FConnection: TADOConnection;
protected
procedure Execute; override;
public
destructor Destroy; override;
constructor Create;
end;

implementation

uses
Windows, SysUtils, ActiveX;

{ TTestThread }

constructor TTestThread.Create;
begin
inherited Create(True);
//Creating here fixes the problem
//FConnection := TADOConnection.Create(nil);
Resume;
end;

destructor TTestThread.Destroy;
begin
if Assigned(FConnection) then
FreeAndNil(FConnection);
inherited;
end;

procedure TTestThread.Execute;
begin
CoInitialize(nil);
try
FConnection := TADOConnection.Create(nil);
//Uncommenting this fixes the problem
//FConnection.Open;
try
while not Terminated do
Sleep(10);
finally
FConnection.Close;
end;
finally
CoUninitialize;
end;
end;

[Finn Tolderlund]

Your comments sounds a bit weird to me.
It shouldn't matter where you create the TADOConnection.
And you do have to call Open, there is no point in having a TADOConnection
if you don't open the connection.
And why do you call resume *inside* the contructor?
There is no point in creating the thread suspended if you want to it to run
immediately?
--
Finn Tolderlund

Post by David Clegg
If I create the TADOConnection component outside of the Execute method,
all works as expected. Also, I can get it to work correctly if I create
the TADOConnection in the Execute method and then call its Open method
constructor TTestThread.Create;
begin
inherited Create(True);
//Creating here fixes the problem
//FConnection := TADOConnection.Create(nil);
Resume;
end;

[David Clegg]

Post by Finn Tolderlund
Your comments sounds a bit weird to me.

Tell me about it, its a weird problem :-)

Post by Finn Tolderlund
It shouldn't matter where you create the TADOConnection.

Trust me it does (in conjunction with where CoInitialize is called, if
called at all). Bear in mind that if it is created in the constructor, it
is created in the main VCL thread. If its created in the Execute method,
it is created in the Threads thread.

Post by Finn Tolderlund
And you do have to call Open, there is no point in having a
TADOConnection if you don't open the connection.

Sure, but the code I posted was an example only to illustrate my problem.
My production code certainly does call Open.

Post by Finn Tolderlund
And why do you call resume *inside* the contructor?

Once again, as this is a Test thread to diagnose the problem, the point
of that was so I could easily switch from creating the connection in the
constructor or in the threads Execute method.

If you are interested in seeing the problem for yourself, try using my
Thread code as posted, or I can even upload a sample app to .Attachments
or send it directly to you.

[Alexander Zuev]

I do not understand completely: for what reason to create TADOConnection
instance inside of Thread instead of main process at all?

Post by David Clegg

Post by Finn Tolderlund
Your comments sounds a bit weird to me.

Tell me about it, its a weird problem :-)

Post by Finn Tolderlund
It shouldn't matter where you create the TADOConnection.

Trust me it does (in conjunction with where CoInitialize is called, if
called at all). Bear in mind that if it is created in the constructor, it
is created in the main VCL thread. If its created in the Execute method,
it is created in the Threads thread.

Post by Finn Tolderlund
And you do have to call Open, there is no point in having a
TADOConnection if you don't open the connection.

Sure, but the code I posted was an example only to illustrate my problem.
My production code certainly does call Open.

Post by Finn Tolderlund
And why do you call resume *inside* the contructor?

Once again, as this is a Test thread to diagnose the problem, the point
of that was so I could easily switch from creating the connection in the
constructor or in the threads Execute method.
If you are interested in seeing the problem for yourself, try using my
Thread code as posted, or I can even upload a sample app to .Attachments
or send it directly to you.
--
Cheers,
David Clegg
dclegg_at_ebetonline_dot_com
{$IFDEF Alessandro}Italian{$ELSE}French{$ENDIF} is the language of love.
For everything else there's Delphi^h^h^h^h^h^h C#.

[Finn Tolderlund]

Just make it small and make sure it can compile without any third-party
components.

[David Clegg]

Post by Finn Tolderlund
Just make it small and make sure it can compile without any third-party
components.

Done. In b.p.attachments with same Subject as original post. File is 3KB.

It has a form with a button to start/stop the thread. Try starting the
thread then attempt to launch Windows Explorer. Stop the thread and
Explorer will pop up immediately. Alternatively wait approx 30 seconds and
Explorer will pop up. I would be interested to see if you can shed more
light on this.

[Finn Tolderlund]

I can see the same behaviour, but I can't see the reason.
The funny thing is, if you copy the shorcut to Windows Explorer from the
start menu to the desktop, and doubleclicks it Windows Explorer starts
immediately, but using the keyboard to start WE doesn't work.
That tells me that it's something in Windows that is preventing WE from
starting, but I don't know what.
Anyway, since the workaround is easy, create the TADConnection in the
thread's constructor, just do that, and be happy. It also makes a nice
symmetry that the connection is freed in the destructor.
And btw remove Application.ProcessMessages, it has nothing to do in a
thread.
--
Finn Tolderlund

Post by David Clegg
It has a form with a button to start/stop the thread. Try starting the
thread then attempt to launch Windows Explorer. Stop the thread and
Explorer will pop up immediately. Alternatively wait approx 30 seconds and
Explorer will pop up. I would be interested to see if you can shed more
light on this.

[Constantine Yannakopoulos]

Author := "Finn Tolderlund";

| I can see the same behaviour, but I can't see the reason.
| The funny thing is, if you copy the shorcut to Windows Explorer from
| the start menu to the desktop, and doubleclicks it Windows Explorer
| starts immediately, but using the keyboard to start WE doesn't work.
| That tells me that it's something in Windows that is preventing WE
| from starting, but I don't know what.
| Anyway, since the workaround is easy, create the TADConnection in the
| thread's constructor, just do that, and be happy. It also makes a nice
| symmetry that the connection is freed in the destructor.
| And btw remove Application.ProcessMessages, it has nothing to do in a
| thread.

I think this quirk may have something to do with which thread creates,
destroys and uses the TADOConnection. Keep in mind that TADOConnection
creates a COM object internally and COM interface pointers should not
be used from different threads without marshalling. I would suggest
that David should create, destroy and use the TADOConnection inside
Execute and not in the constructor/destructor. This may make the
problem go away.

[David Clegg]

Post by Constantine Yannakopoulos
I would suggest
that David should create, destroy and use the TADOConnection inside
Execute and not in the constructor/destructor. This may make the
problem go away.

Thats what I was doing originally. I moved the destruction of the
TADOConnection into the destructor as part of my testing (when creating it
in the constructor). Anyway, as the problem is encountered before the
TADOConnection is destroyed, moving that will not fix the problem.

[David Clegg]

Post by Finn Tolderlund
The funny thing is, if you copy the shorcut to Windows Explorer from
the start menu to the desktop, and doubleclicks it Windows Explorer
starts immediately, but using the keyboard to start WE doesn't work.

Interesting, I didn't try that.

Post by Finn Tolderlund
Anyway, since the workaround is easy, create the TADConnection in the
thread's constructor, just do that, and be happy.

I probably will, but I wanted to really understand the problem before
'hacking' a solution. There should be no reason why a thread cannot create
a connection. And as this is a technique I use frequently, there are many
instances of threads that do this throughout my apps.

Post by Finn Tolderlund
And btw remove Application.ProcessMessages, it has nothing to do in a
thread.

That was part of a theory I was working on & wasn't meant to still be
included in the demo code I posted.

Thanks for taking the time to look.

[Matt Jacobs]

Post by David Clegg
I have a really weird problem with using a TADOConnection within a thread
(using Delphi 5). If I create the TADOConnection in the threads Execute
method (ensuring that CoInitialize and CoUninitialize is called), I
cannot start up Windows Explorer straight away (takes about 20-30
seconds). The Windows Start Bar also stops responding until Explorer
comes up. As soon as the thread has stopped, Explorer pops up.
If I create the TADOConnection component outside of the Execute method,
all works as expected. Also, I can get it to work correctly if I create
the TADOConnection in the Execute method and then call its Open method
(which shows the Login Prompt dialog). Has anyone else encountered this
oddity? Here is my test threads code, and I can post a sample app if
anyone is interested in playing with it.

I have seen strange things myself, especially with tray icon
applications. However, I believe some of the advice you are getting
here is incorrect and will get you in trouble. You are, in fact, doing
everything correctly. You must call CoInitialize(), create the ADO
connection, do your work, destroy the connection, and call
CoUnitialize() all within TThread.Execute. You cannot create the
connection in the ctor, you cannot share the connection among threads.
It will not work.

Also, if this is a server application, it is a good idea to create one
connection in the primary thread and hold it open for the duration of
the server. This will allow the worker threads to participate in
session (connection) pooling.

[David Clegg]

Post by Matt Jacobs
However, I believe some of the advice you are getting
here is incorrect and will get you in trouble. You are, in fact, doing
everything correctly. You must call CoInitialize(), create the ADO
connection, do your work, destroy the connection, and call
CoUnitialize() all within TThread.Execute. You cannot create the
connection in the ctor, you cannot share the connection among threads.
It will not work.

Thats my thoughts as well. The 'solution' for now is to acknowledge the
problem in a whopping great comment in the thread, and keep the existing
behaviour. It only seems to be a problem if you attempt to access Windows
Explorer via the Keyboard shortcut, within the first 30 seconds of the
service starting. Hardly a showstopper, in any case.