Not getting all InfoMessage Events with Python and win32com

Question

I am currently trying to get the percentage complete messages that are returned by the InfoMessage event from ADO (and a SQL server) when running the BACKUP command. (See my previous question for more details).

I have managed to connect to the SQL server and issue it SQL commands, and event get events back. However when I execute the the BACKUP command the cmd.Execute method blocks until the backup is complete.

But during this time I will get a single InfoMessage event call (which will have a message like "1 Percent Complete") and after that I won't receive any more events.

I have tried this using a stored procedure, where the stored procedure prints 3 messages, and even here I will get the first message and nothing else.

I suspect that I need to call pythoncom.PumpWaitingMessages(), but because the cmd.Execute() call blocks I never get anything of any use.

Can anyone work out how to get more that just a single InfoMessage event.

Below is the code that I'm currently using:

import win32com
import pythoncom
import adodbapi
import time
import win32gui
from win32com.client import gencache
gencache.EnsureModule('{2A75196C-D9EB-4129-B803-931327F72D5C}', 0, 2, 8)

defaultNamedOptArg=pythoncom.Empty
defaultNamedNotOptArg=pythoncom.Empty
defaultUnnamedArg=pythoncom.Empty

global connected
connected = False

class events():
    def OnInfoMessage(self, pError, adStatus, pConnection):
        print 'Info Message'
        a = pError.QueryInterface(pythoncom.IID_IDispatch)
        a = win32com.client.Dispatch(a)
        print a.Description
        print a.Number
        print a.Source
        #print 'B', adStatus
        c = pConnection.QueryInterface(pythoncom.IID_IDispatch)
        c = win32com.client.Dispatch(c)
        print c.Errors.Count
        print c.Errors.Item(0).Description
        return 1

    def OnCommitTransComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
    def OnWillExecute(self, Source=defaultNamedNotOptArg, CursorType=defaultNamedNotOptArg, LockType=defaultNamedNotOptArg, Options=defaultNamedNotOptArg
            , adStatus=defaultNamedNotOptArg, pCommand=defaultNamedNotOptArg, pRecordset=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Execute Event'
        return Source
    def OnDisconnect(self, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): 
        print 'Disconnected'
    def OnExecuteComplete(self, RecordsAffected=defaultNamedNotOptArg, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pCommand=defaultNamedNotOptArg
            , pRecordset=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Execute complete'
    def OnWillConnect(self, ConnectionString=defaultNamedNotOptArg, UserID=defaultNamedNotOptArg, Password=defaultNamedNotOptArg, Options=defaultNamedNotOptArg
            , adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'About to connect'
    def OnConnectComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Connected'
        global connected
        connected = True
    def OnBeginTransComplete(self, TransactionLevel=defaultNamedNotOptArg, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):pass
    def OnRollbackTransComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass

if __name__ == '__main__':

    pythoncom.CoInitialize()
    conn = win32com.client.DispatchWithEvents("ADODB.Connection", events)

    conn.ConnectionString = 'Data Source=HPDX2250RAAZ\\SQLEXPRESS; Provider=SQLOLEDB; Integrated Security=SSPI'
    conn.CommandTimeout = 30
    conn.CursorLocation = 2
    conn.Open(pythoncom.Empty,pythoncom.Empty,pythoncom.Empty,0x10)

    while not connected:
        #pythoncom.PumpWaitingMessages()
        win32gui.PumpWaitingMessages()
        time.sleep(0.1)

    conn.BeginTrans()
    conn.Errors.Clear()
    cmd=win32com.client.Dispatch("ADODB.Command")
    cmd.ActiveConnection=conn
    cmd.CommandTimeout = 30  #v2.1 Simons
    cmd.CommandText="EXECUTE [test].[dbo].[Test] "
    print 'Execute'
    cmd.Execute()

    pythoncom.PumpWaitingMessages()
    print 'Called'
    print ''
    print conn.Errors.Count
    conn.RollbackTrans()
    conn.Close()

score 1 · Answer 1 · answered Jun 30 '11 at 17:39

1

I was having the same issue and what the issue is, if you are experiencing the same problem is the messages are basically being held up by the SQL Server engine itself. To get arround this you need to tell SQL not to wait till the end of processing to send the messages but to send them as they occur. Try this on for size:

SET @message = 'My message...'
RAISERROR (@message, 10, 1) WITH NOWAIT

This should send the message and your front end should pick these up as the system goes along.

Hope this helps

answered Jun 30 '11 at 17:39

Peter Ferguson

11
1

Wow I had all but given up on this problem. I'm not sure how to make this work, I am not running a stored procedure on the SQL sever as such. Instead I'm trying to run the backup command, and thus don't know how I would tell the server to push its messages back at me. – thomas Jul 01 '11 at 10:14
I have also tried your suggestion with my test stored procedure. I still only get the first message back, the rest get dropped somewhere. – thomas Jul 01 '11 at 10:29
Yeah another comment, after trying this again with some ``WAITFOR DELAY '00:00:10`` in there, I noticed that I get the ``OnExecuteComplete`` callback just before the first message, but before the actual command completes (I timed it, also the ``cmd.Execute()`` function had not returned). – thomas Jul 01 '11 at 10:36
@mamcx No sorry I never managed to solve this problem. If you find a solution during your travels then I would love to know. – thomas Dec 20 '12 at 22:07

score 0 · Accepted Answer · edited May 23 '17 at 12:18

I found a workaround that is compatible with pymssql and other drivers. I use the SQL from Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process? plus a background thread that each X seconds run that query. Now, for notification I use http://pydispatcher.sourceforge.net/ to get back the progress.

#This is rough extract from my actual code. Probably not work as is, but outline the idea
import dispatch #Decoupled send of messages, identical to django signals

def monitorBackup(self):
    return self.selectSql(SQL_MONITOR)

def backup(sql):
    con = self.getCon() #Get new connection, we are in another thread!
    con.execute_query("HERE THE BACKUP SQL")

result = threading.Thread(target=partial(backup, sql))

result.start()

while result.isAlive():
    time.sleep(5) # with the monitor SQL result, is possible to get a estimated time to complete and adjust this...
    rows = self.monitorBackup()

    if len(rows) > 0:
        percentage = rows[0].Percent

        self.send(
            msg="%d %%" % percentage,
            action="progress",
            progress=percentage
        )

Not getting all InfoMessage Events with Python and win32com

2 Answers2