当调用ODBC时，如果存储SQLMoreResults进行更新或选择，则ODBC会崩溃？

Question

我正在用VB.NET编写代码，并在我的SQL中有以下测试存储过程。

CREATE PROCEDURE ACOCMP1.test1 (@a numeric(28,0) output, @b numeric(28,0), @c numeric(28,0), @d numeric(28,0), @e numeric(28,0))
WITH ENCRYPTION
AS
    BEGIN TRANSACTION
    UPDATE ACOCMP1.TB1 SET TRANREF = TRANREF + 1
    SELECT @a = TRANREF FROM ACOCMP1.TB1 
    COMMIT TRANSACTION
    RETURN (@a + @b + @c + @d + @e)
GO

我已经调用SQLAllocHandle和SQLBindParameter来绑定输出和@a

sqlReturn = DB_ODBC_3X.SQLAllocHandle(SQLAllocHandle.SQL_HANDLE_STMT, dbinf.hdbcv, hstmt)
retValPtr = Marshal.AllocHGlobal(8)
sqlReturn = DB_ODBC_3X.SQLBindParameter64(hstmt, 1, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, SQLDataTypes.SQL_FLOAT, 8, 0, retValPtr, 8, NULL_POINTER)
retArgsPtr(0) = Marshal.AllocHGlobal(8)
sqlReturn = DB_ODBC_3X.SQLBindParameter64(hstmt, 2, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, SQLDataTypes.SQL_FLOAT, 8, 0, retArgsPtr(0), BUFSIZE, NULL_POINTER)

然后sql为"{?=call acocmp1.test1 (?,2,3,4,5)}“。

然后：

Dim sqlStr As StringBuilder = New StringBuilder(sql)
sqlReturn = DB_ODBC_3X.SQLExecDirect(hstmt, sqlStr, sqlStr.Length)

如果我在调用SQLMoreResults之前尝试与SQLMoreResults进行复制，则会得到随机数据(可忽略的右)，但是每当我调用SQLMoreResults程序时，程序就会崩溃，然而，如果我在监视窗口中调用它，它不会掉下来，返回100，从那时起，我可以在程序中任意调用它，并返回由以下内容返回的数据：

ReDim retVals(0)
Marshal.Copy(retValPtr, retVals, 0, 1)

才有意义。我正在连接一个64位odbc和64位项目.它与32位odbc (32位odbc)很好地工作。下面是dll调用的声明：

#Region "SQLBindParameter"
#Region "x64"
    <DllImport("ODBC32.DLL", EntryPoint:="SQLBindParameter", SetLastError:=True, ExactSpelling:=True, CallingConvention:=CallingConvention.Winapi)> _
    Public Shared Function SQLBindParameter64(ByVal StatementHandle As Integer, ByVal ParameterNumber As UShort, ByVal InputOutputType As Short, ByVal ValueType As SQLCDataTypes, ByVal ParameterType As Short, ByVal ColumnSize As ULong, ByVal DecimalDigits As Short, ByVal ParameterValuePtr As IntPtr, ByVal BufferLength As Long, ByRef StrLen_or_IndPtr As Long) As Short
        ' Leave the body of the function empty.
    End Function
#End Region
#Region "x86"
    <DllImport("ODBC32.DLL", EntryPoint:="SQLBindParameter", SetLastError:=True, ExactSpelling:=True, CallingConvention:=CallingConvention.Winapi)> _
    Public Shared Function SQLBindParameter32(ByVal StatementHandle As Integer, ByVal ParameterNumber As UShort, ByVal InputOutputType As Short, ByVal ValueType As SQLCDataTypes, ByVal ParameterType As Short, ByVal ColumnSize As UInteger, ByVal DecimalDigits As Short, ByRef ParameterValuePtr As Double, ByVal BufferLength As Integer, ByVal StrLen_or_IndPtr As Integer) As Short
        ' Leave the body of the function empty.
    End Function
#End Region
#End Region

任何关于这件事为什么会结束的想法都会很好。

添加更多代码：

dbinf.host_bdouble = 6 (SQLDataTypes.SQL_FLOAT)

Public Function callMasterFunction(ByVal functionName As String, ByVal numReturn As Short, ByVal ArgumentsAsString As String, ParamArray Arguments() As Object) As Decimal
        Dim sqlReturn As Short = 0
        Dim hstmt As Integer = 0
        Dim retArgsPtr() As IntPtr
        Dim retValPtr As IntPtr
        Try
            ChkPrf(functionName)
            sqlReturn = DB_ODBC_3X.SQLAllocHandle(SQLAllocHandle.SQL_HANDLE_STMT, dbinf.hdbcv, hstmt)
            If sqlReturn <> SQL_SUCCESS And sqlReturn <> SQL_SUCCESS_WITH_INFO Then
                Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, sqlReturn, hstmt, pvErrorNumber))
            End If

            Const BUFSIZE As Integer = 8
            Dim retVal As Double = 0
            If IsWin64() Then
                retValPtr = Marshal.AllocHGlobal(BUFSIZE)
                sqlReturn = DB_ODBC_3X.SQLBindParameter64(hstmt, 1, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, dbinf.host_bdouble, BUFSIZE, 0, retValPtr, BUFSIZE, NULL_POINTER)
            Else
                sqlReturn = DB_ODBC_3X.SQLBindParameter32(hstmt, 1, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, dbinf.host_bdouble, BUFSIZE, 0, retVal, BUFSIZE, NULL_POINTER)
            End If
            If sqlReturn <> SQL_SUCCESS And sqlReturn <> SQL_SUCCESS_WITH_INFO Then
                Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, sqlReturn, hstmt, pvErrorNumber))
            End If

            Dim retVals() As Double
            If numReturn > 0 Then
                If IsWin64() Then
                    ReDim retArgsPtr(numReturn - 1)
                Else
                    ReDim retVals(numReturn - 1)
                End If
                For i As UShort = 0 To numReturn - 1
                    If IsWin64() Then
                        retArgsPtr(i) = Marshal.AllocHGlobal(BUFSIZE)
                        sqlReturn = DB_ODBC_3X.SQLBindParameter64(hstmt, i + 2, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, dbinf.host_bdouble, BUFSIZE, 0, retArgsPtr(i), BUFSIZE, NULL_POINTER)
                    Else
                        sqlReturn = DB_ODBC_3X.SQLBindParameter32(hstmt, i + 2, SQL_PARAM_OUTPUT, SQLCDataTypes.SQL_C_DOUBLE, dbinf.host_bdouble, BUFSIZE, 0, retVals(i), BUFSIZE, NULL_POINTER)
                    End If
                    If sqlReturn <> SQL_SUCCESS And sqlReturn <> SQL_SUCCESS_WITH_INFO Then
                        Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, sqlReturn, hstmt, pvErrorNumber))
                    End If
                Next
            End If

            Dim sql As String = "{?=call " & functionName
            If String.IsNullOrEmpty(ArgumentsAsString) And (Arguments IsNot Nothing AndAlso (Arguments.Length = 0)) Then
                sql &= "}"
            Else
                sql &= "("
                If numReturn > 0 Then
                    For i As Short = 0 To numReturn - 1
                        sql &= "?,"
                    Next
                End If
                If Not String.IsNullOrEmpty(ArgumentsAsString) Then
                    sql &= ArgumentsAsString
                ElseIf Arguments IsNot Nothing AndAlso (Arguments.Length > numReturn) Then
                    For i As Short = numReturn To Arguments.Length - 1
                        Select Case VarType(Arguments(i))
                            Case VariantType.Null
                                sql &= "NULL,"
                            Case VariantType.Short, VariantType.Integer, VariantType.Single, VariantType.Double, VariantType.Decimal
                                sql &= VB6.Format(Arguments(i)) & ","
                            Case VariantType.Date
                                sql &= Fdatetime(Arguments(i)) & ","
                            Case Else
                                sql &= fnds(CStr(Arguments(i))) & ","
                        End Select
                    Next
                End If
                If sql.EndsWith(",") Then
                    sql = sql.Substring(0, sql.LastIndexOf(","))
                End If
                sql = sql & ")}"
            End If

            Dim sqlStr As StringBuilder = New StringBuilder(sql)
            sqlReturn = DB_ODBC_3X.SQLExecDirect(hstmt, sqlStr, sqlStr.Length)
            If sqlReturn <> SQL_SUCCESS And sqlReturn <> SQL_SUCCESS_WITH_INFO And sqlReturn <> SQL_NO_DATA_FOUND Then
                Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, sqlReturn, hstmt, pvErrorNumber))
            End If

            Try

            Catch ex As Exception

            End Try
            Dim more As Object = SQLMoreResults(hstmt)
            If more <> SQL_SUCCESS And more <> SQL_SUCCESS_WITH_INFO And more <> SQL_NO_DATA_FOUND Then
                Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, more, hstmt, pvErrorNumber))
            End If
            Do While more <> SQL_NO_DATA_FOUND
                more = SQLMoreResults(hstmt)
                If more <> SQL_SUCCESS And more <> SQL_SUCCESS_WITH_INFO And more <> SQL_NO_DATA_FOUND Then
                    Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, more, hstmt, pvErrorNumber))
                End If
            Loop

            If IsWin64() Then
                ReDim retVals(0)
                Marshal.Copy(retValPtr, retVals, 0, 1)
                retVal = retVals(0)
                If numReturn > 0 Then
                    For i As Integer = 0 To numReturn - 1
                        Marshal.Copy(retArgsPtr(i), retVals, 0, 1)
                        Arguments(i) = retVals(0)
                    Next
                End If
            ElseIf retVals IsNot Nothing AndAlso retVals.Length > 0 Then
                For i As Integer = 0 To numReturn - 1
                    Arguments(i) = retVals(i)
                Next
            End If

            Return retVal
        Catch ex As Exception
            Throw ex
        Finally
            If hstmt > 0 Then
                sqlReturn = DB_ODBC_3X.SQLFreeHandle(SQLAllocHandle.SQL_HANDLE_STMT, hstmt)
                If sqlReturn <> SQL_SUCCESS And sqlReturn <> SQL_SUCCESS_WITH_INFO And sqlReturn <> SQL_NO_DATA_FOUND Then
                    Throw New Exception(GetdberrorEx(dbinf.henv, dbinf.hdbcv, sqlReturn, hstmt, pvErrorNumber))
                End If
            End If
            If IsWin64() Then
                If retValPtr <> 0 Then
                    Marshal.FreeHGlobal(retValPtr)
                End If
                If retArgsPtr IsNot Nothing AndAlso retArgsPtr.Length > 0 Then
                    For i As Integer = 0 To numReturn - 1
                        Marshal.FreeHGlobal(retArgsPtr(i))
                    Next
                End If
            End If
        End Try
    End Function

Answer 1

好的，我已经找到了我的答案。当SP正在更新、删除、插入并且有行计数信息时，我的代码就会掉下来(如果我在visual studio中的调试监视窗口中运行它，不确定原因)。但是，由于我们不使用行计数信息，我将简单地在每个SP的开头添加set nocount，这允许它运行。如果有人能回答为什么这会很棒，否则直到下次.