ADO Events with win32com
Paul Moore
anywhere. The following is my test code:
-------------------------------------------------------------
import win32com.client
finished = 0
class ADOEvents:
def OnConnectComplete(*args):
print `args`
finished = 1
def Test():
# Conn = win32com.client.Dispatch("ADODB.Connection")
Conn = win32com.client.DispatchWithEvents("ADODB.Connection", ADOEvents)
ConnStr = "Provider=msdaora;Prompt=complete"
Conn.Open(ConnStr, Options=16)
while not finished:
pass
if __name__ == '__main__':
Test()
-------------------------------------------------------------
If I understand things correctly, this is about what is needed.
The problem is that this code immediately returns an error - 'Operation has been
cancelled by the user'. But I didn't! I didn't even get the chance, as the login
box did not appear.
If I comment out the DispatchWithEvents call, and replace it with a straight
Dispatch call, as on the line above, everything works as I want - the login box
appears (although clearly the event doesn't get fired, so I get an infinite
loop...)
Is there some reason that DispatchWithEvents isn't working correctly here?
Thanks for any insight,
Paul.
angusm
the event handlers for the interface in your class:
"All the event handlers in the ConnectionEvent ... must be
implemented, regardless of whether you actually use events. " MSDN
that is, if you use one you have to define the rest (though presumably
you can bet by with a "pass" implementation). The same goes for
Recordset events.
The ADO type library generated by makepy has the prototypes you need.
Angus
Paul Moore <gus...@morpheus.demon.co.uk> wrote in message news:<vv7hlt8dqss6e3vv2...@4ax.com>...
Paul Moore
>I think the problem is that COM (or ADO?)requires you to define all
>the event handlers for the interface in your class:
>
>"All the event handlers in the ConnectionEvent ... must be
>implemented, regardless of whether you actually use events. " MSDN
I'll try it, but I'm surprised. I didn't need to do this for a different object
I tested the event code on. That has many events, and I only implemented the one
I used. I assumes that the win32com stuff did its magic, and made it work...
Nevertheless, thanks for the suggestion - I'll try it.
Paul.
Paul Moore
wrote:
It did get rid of the "User has cancelled the operation" error - on a bit more
investigation, it looks like I needed to implement the OnWillConnect event as
well as the OnConnectComplete event. This is odd... The ADO docs state that
the WillConnect event gets passed an "adStatus" parameter set to adStatusOK -
to cancel the connection, the parameter should be set to adStatusCancel. For
some reason, if I implement
def OnWillConnect(self, ConnectionString, UserID,
Password, Options, adStatus,
pConnection):
pass
this works, but if I *don't* implement this, whatever win32com does behind the
scenes doesn't work.
However, I now don't ever seem to receive the OnConnectComplete event. This
may be an ADO issue, but I've tested it in VB and it works as documented, so I
suspect something with the Python side...
Any suggestions?
Paul.
angusm
> However, I now don't ever seem to receive the OnConnectComplete event. This
> may be an ADO issue, but I've tested it in VB and it works as documented, so I
> suspect something with the Python side...
>
> Any suggestions?
> Paul.
You might want to wait for a more convincing explanation from someone
else but I was able to duplicate the problem and the code below worked
for me...it ain't
pretty :-).
(the code may not work for you exactly as is since I'm doing something
weird with the ADO import etc.)
I encountered two problems based on your code:
1)It appears the event class sees a different 'finished' variable from
what you define in your module. You might try this by opening normally
(without adAsyncConnect) and the thing will hang even though it gets
the OnConnectMessage. I couldn't even get it to work when I declared
'finished' global - I had to resort to something like
globals()['finished']=1. Not too sure on this one.
2)adAsyncConnect doesn't seem to work because the infinite loop that
gets created to wait for the events hogs the entire thread. There is
no way for the dispatched event to break in asynchronously... Windows
does some event dispatching via PostMessage(?) hence the code below
which creates a temporary message pump via win32gui.PumpMessages()
which seems to be the correct way to idle in this instance. The event
handler sends a PostQuitMessage() to break the idle loop in the main
program.
hope this helps,
Angus
import win32com.client
from win32com.gen_py.ADO21 import *
import time
global finished
finished = 0
import win32ui
from win32con import *
import win32gui
class onevent:
def __init__(self,val):
self.v = val
def OnInfoMessage(self, pError=defaultNamedNotOptArg,
adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
pass
def OnWillConnect(self, ConnectionString=defaultNamedNotOptArg,
UserID=defaultNamedNotOptArg, Password=defaultNamedNotOptArg,
Options=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg,
pConnection=defaultNamedNotOptArg):
print 'willconnect'
def OnExecuteComplete(self, RecordsAffected=defaultNamedNotOptArg,
pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg,
pCommand=defaultNamedNotOptArg, pRecordset=defaultNamedNotOptArg,
pConnection=defaultNamedNotOptArg):
pass
def OnRollbackTransComplete(self, pError=defaultNamedNotOptArg,
adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
pass
def OnBeginTransComplete(self,
TransactionLevel=defaultNamedNotOptArg, pError=defaultNamedNotOptArg,
adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
pass
def OnDisconnect(self, adStatus=defaultNamedNotOptArg,
pConnection=defaultNamedNotOptArg):
pass
def OnWillExecute(self, Source=defaultNamedNotOptArg,
CursorType=defaultNamedNotOptArg, LockType=defaultNamedNotOptArg,
Options=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg,
pCommand=defaultNamedNotOptArg, pRecordset=defaultNamedNotOptArg,
pConnection=defaultNamedNotOptArg):
pass
def OnCommitTransComplete(self, pError=defaultNamedNotOptArg,
adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
pass
def OnConnectComplete(self, pError=defaultNamedNotOptArg,
adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
print 'hooray'
win32gui.PostQuitMessage(0)
o = win32com.client.DispatchWithEvents('ADODB.Connection',onevent)
print constants.adAsyncConnect
o.Open('Provider=MSDASQL;DSN=oladb;UID=FOO;PWD=BAR;','','',constants.adAsyncConnect)
win32gui.PumpMessages()
Paul Moore
>You might want to wait for a more convincing explanation from someone
>else but I was able to duplicate the problem and the code below worked
>for me...it ain't pretty :-).
Thank you, thank you, thank you! It's perfectly clear now. Your code may not be
pretty, but it gave me all the pointers I needed...
>I encountered two problems based on your code:
>1)It appears the event class sees a different 'finished' variable from
>what you define in your module.
Doh! Yes, Python treats any variable which gets assigned to in a function as
local unless it's explicitly declared using 'global'. If I could actually write
code in Python, I'd be dangerous :-)
>2)adAsyncConnect doesn't seem to work because the infinite loop that
>gets created to wait for the events hogs the entire thread. There is
>no way for the dispatched event to break in asynchronously...
Again, I probably should have known this. In order to get COM events, the
application must pump the Windows message loop. Your solution works fine for me,
but there's also a win32gui.PumpWaitingMessages() call which just services any
waiting messages. You can just put a call to this inside the wait loop (which,
of course, would be doing real work if this was a proper application...)
>hope this helps,
>Angus
It does. Thanks for your help. FWIW, the following is a working version of my
code:
from win32com.client import DispatchWithEvents, constants
import win32gui
finished = 0
class ADOEvents:
def OnWillConnect(*args):
pass
def OnConnectComplete(*args):
print `args`
global finished
finished = 1
def Test():
Conn = DispatchWithEvents("ADODB.Connection", ADOEvents)
ConnStr = "Provider=msdaora;Prompt=complete"
Conn.Open(ConnStr, Options=constants.adAsyncConnect)
while not finished:
win32gui.PumpWaitingMessages()
print '...'
pass
if __name__ == '__main__':
Test()
Paul