c++ - SQLExecDirect 结果 -1 C 中的状态 24000

Question

你好，我正在学习 C 和 ODBC调用 SQLExecDirect 函数时出现错误。请帮我这是代码

#include <Windows.h>
#include <sql.h>
#include <sqlext.h>

int main( void )
{
    //DB connect example
    SQLRETURN   nResult     = 0;
    SQLHANDLE   handleEnv   = 0;
    nResult = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, ( SQLHANDLE* )&handleEnv );
    //nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC2, SQL_IS_INTEGER );
    nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC3, SQL_IS_INTEGER );
    nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, SQL_IS_INTEGER);

    SQLHANDLE   handleDBC   = 0;
    nResult = SQLAllocHandle( SQL_HANDLE_DBC, handleEnv, ( SQLHANDLE* )&handleDBC );

    SQLCHAR     strConnect[ 256 ]       = "DSN=MY TEST DB; UID=TESTsa; PWD=TESTpw";
    SQLCHAR     strConnectOut[ 1024 ]   = {0};
    SQLSMALLINT nNumOut                 = 0;
    nResult = SQLDriverConnect( handleDBC, NULL, ( SQLCHAR* )strConnect, SQL_NTS, ( SQLCHAR* )strConnectOut, sizeof(strConnectOut),
                                &nNumOut, SQL_DRIVER_NOPROMPT );

    SQLHSTMT    handleStatement = 0;
    //nResult = ::SQLAllocStmt( handleDBC, &handleStatement );
    nResult = SQLAllocHandle( SQL_HANDLE_STMT, handleDBC, ( SQLHANDLE* )&handleStatement );

    SQLHSTMT    hstmt       = handleStatement;
    int         sqlResultOutput = 0;

    SQLINTEGER  cbValue     = 0;
    nResult = SQLBindParameter( hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,    SQL_INTEGER,    0, 0, &sqlResultOutput,  0, &cbValue );

    //here is the problem
    nResult = SQLExecDirect( hstmt, (SQLTCHAR*) "{CALL my_TEST_SP(?)}", SQL_NTS );
    //nResult Value is -1 and SQL State is 24000

    return 0;
}

每次调用结果都很好。返回 SQL_SUCCESS 除了一个以外的所有SQLExecDirect函数返回-1，SQLState为24000

存储过程就这么简单

CREATE PROCEDURE my_TEST_SP
 @retValue int = 0 output
AS
BEGIN
   SET NOCOUNT ON

   SET @retValue = 0

   SELECT myTestCol
   FROM tTestTable

   IF @@ERROR <> 0
   BEGIN
     SET @retValue = -1
   END
END

我的操作系统是windows7。使用 visual studio 2010 sp1，sqlserver 2012

我想念什么？请告诉我，祝你有美好的一天:)

Answer 1

主要区别似乎是 odbc 驱动程序版本。我的版本是 2014.120.2000.08。我不认为这是由于不同的 visual studio 版本 - 我认为它们都在后台使用相同的 odbc32.lib。

以下代码适用于我:我更改了存储过程以返回不为 0 的内容(以验证我实际上可以读取返回的数据):

 @retValue int = 0 output
AS
BEGIN
   SET NOCOUNT ON

   SET @retValue = 3499

   SELECT myTestCol
   FROM tTestTable

   IF @@ERROR <> 0
   BEGIN
     SET @retValue = -1
   END
END

我在 Visual Studio 2013 中使用了以下代码:

void printErrDbc(SQLHDBC hDbc)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}

void printErrStmt(SQLHSTMT hStmt)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}

int _tmain(int argc, _TCHAR* argv[])
{
    //DB connect example
    SQLRETURN   nResult = 0;
    SQLHANDLE   handleEnv = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE*)&handleEnv);
    //nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC2, SQL_IS_INTEGER );
    nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
    nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, SQL_IS_INTEGER);

    SQLHANDLE   handleDBC = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_DBC, handleEnv, (SQLHANDLE*)&handleDBC);

    SQLWCHAR     strConnect[256] = L"DSN=test;UID=sa;PWD=MySecretPassword";
    SQLWCHAR     strConnectOut[1024] = { 0 };
    SQLSMALLINT nNumOut = 0;
    nResult = SQLDriverConnect(handleDBC, NULL, (SQLWCHAR*)strConnect, SQL_NTS, (SQLWCHAR*)strConnectOut, sizeof(strConnectOut),
        &nNumOut, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrDbc(handleDBC);
    }

    SQLHSTMT    handleStatement = 0;
    //nResult = ::SQLAllocStmt( handleDBC, &handleStatement );
    nResult = SQLAllocHandle(SQL_HANDLE_STMT, handleDBC, (SQLHANDLE*)&handleStatement);

    SQLHSTMT    hstmt = handleStatement;
    int         sqlResultOutput = 0;

    SQLINTEGER  cbValue = 0;
    nResult = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &sqlResultOutput, 0, &cbValue);

    //here is the problem
    nResult = SQLExecDirect(hstmt, (SQLWCHAR*)L"{CALL my_TEST_SP(?)}", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErrStmt(hstmt);
    }
    nResult = SQLMoreResults(hstmt);
    if (!(SQL_SUCCEEDED(nResult) || nResult == SQL_NO_DATA))
    {
        printErrStmt(hstmt);
    }
    std::wcout << L"Value of sqlResultOutput is: " << sqlResultOutput << std::endl;

    return 0;
}

在 ODBC 数据源管理器中，我使用版本为 2014.120.2000.08 的 ODBC Driver 11 for SQL Server 创建了一个名为 test 的相应 DSN。最后，DSN 条目显示以下属性(在您可以点击 Test Data Source... 的对话框中):

Microsoft ODBC Driver for SQL Server Version 12.00.2000

Data Source Name: test
Data Source Description: 
Server: .\SQLSERVERINSTANCE
Use Integrated Security: Yes
Database: Test
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server: 
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

如果我运行这个示例应用程序，我会得到预期的输出:

Value of sqlResultOutput is: 3499

所以我建议您尝试使用最新的 SQL Server ODBC 驱动程序。我认为是这里下载:http://www.microsoft.com/de-ch/download/details.aspx?id=36434