使用ADO和win32com获取SQL Server消息 [英] Getting SQL Server messages using ADO and win32com

问题描述

我目前正在尝试编写一个工具，使非计算机知识的用户可以非常轻松地备份SQL Server数据库。

I am currently trying to write a tool that will make it very easy for a non-computer literate user to backup a SQL Server database.

为此，我希望使用ADO，win32com和adodbapi的有趣组合。目前，我可以轻松地连接到服务器并发出 BACKUP DATABASE T-SQL命令。

To do this I am hoping to use an interesting mix of ADO, win32com and adodbapi. Currently I can easily connect to the server and issues a BACKUP DATABASE T-SQL command.

这可行，但是该命令通常需要很长时间才能执行（特别是在大型数据库上）。为此，我希望捕获并解析 InfoMessage 事件（ MSDN ），并使用它显示百分比条/计数器。

This works, however it often takes a long time for the command to execute (especially on very large databases). To this end I was hoping to capture and parse the InfoMessage event (MSDN) and use it to show a percentage bar/counter.

我在管理方面，我现在仍然陷入最后的障碍，无法解析事件。 MSDN 文档说我应该传递错误或< <$ c $中的a href = http://msdn.microsoft.com/zh-cn/library/ms675299(v=vs.85).aspx rel = nofollow noreferrer>错误对象c> pError 参数。但是win32com向我传递了一个我不知道如何处理的 PyIUnknown 对象。

This I have also managed, I now I'm stuck at the final hurdle, parsing the event. The MSDN docs say that I should be passed either an Error or Errors object in the pError parameter. However win32com passes me a PyIUnknown object which I don't know how to deal with.

下面是代码到目前为止我已经写过：

Below is the code that I have written so far:

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

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

class events():
    def OnInfoMessage(self, pError, adStatus, pConnection):
        print 'A', pError
        #print 'B', adStatus
        #print 'C', pConnection

# This is taken from the makepy file
#    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):
        return Source
#    def OnDisconnect(self, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
    def OnExecuteComplete(self, RecordsAffected=defaultNamedNotOptArg, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pCommand=defaultNamedNotOptArg
            , pRecordset=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
        #print pError
    def OnWillConnect(self, ConnectionString=defaultNamedNotOptArg, UserID=defaultNamedNotOptArg, Password=defaultNamedNotOptArg, Options=defaultNamedNotOptArg
            , adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
#    def OnConnectComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
#    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)
    print dir(conn)
    conn.ConnectionString = 'Initial Catalog=test; Data Source=HPDX2250RAAZ\\SQLEXPRESS; Provider=SQLOLEDB.1; Integrated Security=SSPI'
    conn.CommandTimeout = 30
    print conn.ConnectionString
    conn.Open()

    con = adodbapi.Connection(conn)

    c = con.cursor()
    import time
    print 'Execute'
    time.sleep(1)
    c.execute(u"BACKUP DATABASE [test] TO DISK = N'c:/test/test2' WITH STATS = 1")
    print 'Done Execute'

任何人都可以从事件中提取参考消息吗？

Can anyone extract the Informational messages from the events?

这是在 VB （我认为）

以下示例之一这些消息将启动SQL Server Management Studio，并使用脚本运行备份（您可以使用备份对话框和左上角的脚本按钮生成脚本）。您会注意到，在运行脚本时，消息框将填充完成消息的百分比。这些就是我想要的。

For an example of one of these messages start up SQL Server Management Studio and run a backup using a script (you can generate the script using the backup dialogue and the script button in the top left). You will notice that when you run the script the messages box will populate with percentage complete messages. These are what I want.

下面是我正在使用的新代码传递给 InfoMessage 的COM对象。

Below is the new code that I'm using to interrogate the COM objects that get passed to the InfoMessage. This is based on the answer below, I'm putting it here in case anyone else needs it.

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
    print c.Errors.Clear()
    print 'c', adStatus

推荐答案

正在阅读MSDN，看来只有 Error 对象应该传递给事件处理程序。如果存在多个错误，则可以从 Connection 对象 Errors 集合中获取它们。 >。因此，您只应期望将Error对象传递给 InfoMessage（）。如果改为获取PyIUnknown，也许您可​​以尝试在其上调用 QueryInterface（）并请求IDispatch？您也可以尝试请求特定的自定义接口 Error 使用，但是我不记得Pythoncom是否支持自定义（即非IDispatch）接口，并且我的互联网正在正确爬行现在，我无法检查，因此您必须自己检查一下。无论如何，IDispatch无论如何都应该起作用，因为这就是VB6所使用的。

Reading MSDN, it seems only Error objects should get passed to the event handlers. If there are multiple errors, you can get them from the Errors collection of your Connection object. So you should only expect Error objects to get passed to InfoMessage(). If you get PyIUnknown instead, maybe you can try to call QueryInterface() on it and request IDispatch? You can also try to request the specific custom interface Error uses, but I don't remember if Pythoncom supports custom (i.e. non-IDispatch) interfaces, and my internet is crawling right now so I can't check so you'll have to check that yourself. Anyway, IDispatch should work no matter what, since that's what VB6 uses.

这篇关于使用ADO和win32com获取SQL Server消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！