我有一个基于Excel的应用程序依赖于几个不同的数据库,我使用ADO(Microsoft ActiveX Data Objects 6.1库)连接到这些数据库.数据库驻留在区域服务器上,并且建立初始连接存在开销,因此我将连接缓存在Scripting.Dictionary对象中以供重用.
Private moConnCacheDict As Scripting.Dictionary
当我使用kdb + qodbc.dll数据库驱动程序时,我得到意外的行为.我可以按预期连接和重用多个数据源(Oracle,Sybase,Access)和一个KDB数据库实例.但是,如果我创建第二个KDB数据库连接并对新数据集执行查询,则尽管查询是合法的,但不会返回任何数据.
Recordset.BOF = TRUE and Recordset.EOF = TRUE
它似乎执行正常,字段可见.与前一个区域服务器的连接似乎仍然存在,我可以成功检索驻留在原始服务器上的数据,尽管如果我看到,
Recordset.ActiveCommand.ActiveConnection.Properties.Item("Extended Properties")
,是新的连接字符串.
KDB +连接字符串使用以下语法:
DRIVER=kdb+;DBQ=XXXXX;UID=XXXXX;PWD=XXXXX;
我已经将核心VBA函数作为示例包含在内:
Private Function ExecuteQuery(sDBName As String, ByRef oRst As ADODB.Recordset, Optional bDeleteConnection As Boolean) As Boolean Dim oDBConn As ADODB.Connection Dim sSql As String 'delete connection If bDeleteConnection Then Call DropConnection(sDBName) 'get cached or new connection Call GetConnection(sDBName, oDBConn) Select Case sDBName Case "MAIN_US" sSql = mSQL_MAIN Case "MD_ASIA" sSql = mSQL_MDASIA End Select Set oRst = New Recordset oRst.Open sSql, oDBConn, adOpenKeyset, adLockPessimistic If Not oDBConn.State = adStateOpen Then Err.Raise vbObjectError + 1024, "ExecuteQuery", sDBName & ": Recordset Closed. Unable to execute query ->" & sSql ExecuteQuery = True End Function Private Function GetConnection(sDBName As String, ByRef oDBConn As ADODB.Connection) As Boolean If moConnCacheDict Is Nothing Then Set moConnCacheDict = New Dictionary If moConnCacheDict.Exists(sDBName) Then 'get existing connection Set oDBConn = moConnCacheDict.Item(sDBName) Else 'create connection Set oDBConn = New Connection With oDBConn .Mode = adModeRead Select Case sDBName Case "MAIN_US" .Mode = adModeReadWrite .ConnectionString = mCONN_MAIN Case "MD_ASIA" .Mode = adModeRead .ConnectionString = mCONN_MDASIA End Select .CursorLocation = adUseServer .Open End With moConnCacheDict.Add sDBName, oDBConn End If GetConnection = True End Function Private Function DropConnection(Optional sDBName As String) As Boolean Dim oDBConn As ADODB.Connection Dim i As Integer 'delete object directly from cache If Not moConnCacheDict Is Nothing Then If sDBName = vbNullString Then 'close all connections For i = 0 To moConnCacheDict.Count - 1 If Not IsEmpty(moConnCacheDict.Items(i)) Then Set oDBConn = moConnCacheDict.Items(i) If Not oDBConn Is Nothing Then If oDBConn.State = adStateOpen Then oDBConn.Close Set oDBConn = Nothing Debug.Print Now, "Dropping Database Connection - " & moConnCacheDict.Keys(i) End If End If Next i Set moConnCacheDict = Nothing Else If moConnCacheDict.Exists(sDBName) Then If Not IsEmpty(moConnCacheDict.Item(sDBName)) Then Set oDBConn = moConnCacheDict.Item(sDBName) If Not oDBConn Is Nothing Then If oDBConn.State = adStateOpen Then oDBConn.Close Set oDBConn = Nothing Debug.Print Now, "Dropping Database Connection - " & "Dropping Database Connection - " & sDBName End If End If moConnCacheDict.Remove (sDBName) End If End If End If DropConnection = True End Function
(注意ADO.Recordset始终关闭,并由调用者设置为空).
解决此问题的唯一方法是关闭所有数据库连接(无论提供程序如何),然后重新连接到所需的区域服务器.这是非常低效的,因为我必须重新打开所有现有的连接.另请注意,仅在当前工作簿中完成此操作是不够的. 这必须在应用程序级别完成.如果任何与任何数据库的ADO连接仍然打开,我可以创建一个新的KDB + ADO连接,但它仍然指向前一个实例.
我查看了KDB +连接对象的错误属性,有两个错误:
多步OLE DB操作生成错误.检查每个OLE DB状态值(如果可用).没有工作.
提供商不支持该财产.
这似乎在http://support.microsoft.com/kb/269495中有记录,但我无法在注册表中找到任何CLSID,因此无法尝试建议的更改.
如果我打开ODBC日志记录,我会看到以下消息:
EXCEL 8dc-22d0 EXIT SQLGetInfoW with return code -1 (SQL_ERROR) HDBC 0x02131EA8 UWORD 151PTR 0x003C4FB0 SWORD 4 SWORD * 0x00000000 DIAG [S1096] [Microsoft][ODBC Driver Manager] Information type out of range (0)
这会对错误负责吗?
与往常一样,任何帮助和建议将不胜感激.