[python-win32] Win32com events, multithreading, and re-entrancy

[Moore, Paul]

From: Mark Hammond [mailto:mhammond@skippinet.com.au]

> > This is an ADO program, trying to catch the ADO connection
> > events. The program works fine, *as long as* the print
> > statement in OnConnectComplete is commented out. Any
> > attempt to access the connection object from within the
> > event handler causes Python to jump back into the main
> > busy wait loop, pumping messages and printing dots.
>=20
> It's not clear what the problem is. Accessing the connection
> object may well cause the main loop to trigger, but it
> should still work. What exactly is the problem?

I can't follow the logic for the flow of control though my code. Worse, =
in this particular (toy) example, the whole thing locks up, when it =
should run to completion. I'll try to explain better below.

>=20
> > I assume that this is somehow related to the internal
> > multithreading and serialisation that goes on in COM,
> > but I don't know enough about that to understand it.
> > Trying to make the code free-threaded (assigning to
> > sys.coinit_flags - BTW, there's a bug in the documentation
> > for pythoncom.CoInitializeEx, which gives 2 variations on
> > this name) didn't seem to help.
>=20
> OVe fixed the doc. ADO is probably single or apartment
> threaded, thus it will not depend on your sys.coinit_flags.

OK, that's one possibility cleared up.

> > How do I do this? Is there any sample code which shows
> > how to do this? Mark Hammond's book doesn't cover ADO
> > events, and I've read the threading appendix and am still
> > no wiser...
>=20
> I'm not exactly clear what you are trying to do!

Sorry, I'll try to clarify. This specific code is "toy" code where I am =
trying to understand how to use the ADO event model, specifically to try =
to open a number of database connections in parallel (to minimise =
application startup time - I'm ultimately looking at writing something =
which opens connections to tens, if not hundreds, of separate databases, =
and I'd rather the startup time didn't scale *too* linearly :-) I don't =
know if this is a practical solution to my requirements, but I want to =
understand how it works before giving up on it...

OK, Given that, I'm trying to write some code which:

1. Initialises an ADO asynchronous connection.
2. Has an event handler which fires when the connection
   is made, and does "something" with the connection.
   In the long run, "something" could include firing off
   a separate thread to run some tasks[1] or setting an
   asynchronous query going, or whatever. For now, I'm
   just printing off the connection's data source.
3. Wait in some sort of loop until the work on the connection
   is complete (ultimately, this is my "wait for all the
   asynchronous tasks to finish" loop, but let's keep it
   simple for now).

[1] I'm aware that when I start firing off threads, I have to worry a =
lot harder about marshalling, apartments and the like. Save that until I =
understand the basics (or find the whole thing so complex that I give up =
:-))

So, my code goes something like this:

--- cut here ---

from win32com.client import DispatchWithEvents, constants
import pythoncom

finished =3D 0 # Flag for the wait loop from (3) to test

class ADOEvents: # event handler class
   def OnWillConnect(self, str, user, pw, opt, sts, cn):
       # Must have this event, as if it is not handled, ADO assumes the
       # operation is cancelled, and raises an error (Operation =
cancelled
       # by the user)
       pass
   def OnConnectComplete(self, error, status, connection):
       # Assume no errors, until we have the basic stuff
       # working. Now, "connection" should be an open
       # connection to my data source
       # Do the "something" from (2). For now, just
       # print the connection data source
       print "Connected to", connection.Properties("Data Source")
       # OK, our work is done. Let the main loop know
       global finished
       finished =3D 1

# Create the ADO connection object, and link the event
# handlers into it
c =3D DispatchWithEvents("ADODB.Connection", ADOEvents)

# Initiate the asynchronous open
str =3D "Provider=3Dmsdaora;Data Source=3DXXXX"
user =3D "system"
pw =3D "manager"
c.Open(str, user, pw, constants.adAsyncConnect)

# Sit in a loop, until our event handler (above) sets the
# "finished" flag
while not finished:
    # Pump messages so that COM gets a look in
    pythoncom.PumpWaitingMessages()
    # Print something on the screen, so I can see progress
    print ".",

--- cut here ---

Now, when I run this, I would expect to see a string of dots, =
terminating with "Connected to XXXX". What I actually get is a string of =
dots, then "Connected to", then the string of dots resumes indefinitely.

The only explanation that I can see for this is that the access to =
connection.Properties in the event, causes PumpWaitingMessages() in the =
main loop to return, and restart the main loop (with "finished" not =
set). Such flow of control makes no sense unless multiple threads are =
present behind the scenes (which could easily be true with ADO/COM), but =
if they are, I have no idea what the call to connection.Properties is =
waiting on, or how to free it up.

Ultimately, my event handler function appears not to be acting as an =
atomic operation, but is somehow interfering with the main processing =
loop.

I am assuming that what is missing is in my understanding of how to code =
this, rather than it being any sort of bug or problem. But I don't have =
any idea where to go next. I've experimented in VB and VBScript. VB does =
too much magic "behind the scenes" and I can't deduce why it works when =
the Python code above does not. And VBScript appears to single-thread =
everything, so that the whole event architecture becomes useless :-(

As a last resort, I could try coding it in Perl, but I'm not sure what I =
would learn from that (beyond that I don't like coding this sort of =
thing in Perl :-))

At the moment, I'm close to thinking that I should just give up on COM, =
ADO and events as a solution, and go with threads and the DB API, as if =
I can't even get a simple test program working, I'm never going to get a =
full application doing what I want, but that feels defeatist :-)

Thanks for any help,
Paul.