使用空值更新唯一标识符列时，出现ODBC错误'String data，right truncation‘

Question

我正在尝试用null更新唯一标识符类型的列。我的query看起来像这样：

UPDATE table_name SET column_name = ?

SQLLEN _nullLen(SQL_NULL_DATA);

_rc = SQLBindParameter(_hstmt,          
    static_cast<SQLUSMALLINT>(1),   
    SQL_PARAM_INPUT,                    
    SQL_C_CHAR,                         
    SQL_VARCHAR,                        
    37,                         
    NULL,                               
    NULL,                               
    0,                                  
    &_nullLen);                         

执行query会产生一个ODBC error 'String data, right truncation'.，它使用的SQLBindParameter与我能够成功插入包含null数据的新行的SQLBindParameter完全相同。为什么这不适用于更新行？

Answer 1

请阅读

彻底的https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindparameter-function?view=sql-server-ver15。

根据它，SQLBindParameter的第6个参数是ColumnSize，您将其设置为37。为什么是这个值？

第8个参数是ParameterValuePtr，但您将其设置为NULL。NULL是您试图设置的值吗？

第10个参数是StrLen_or_IndPtr，您将其设置为&_nullLen where SQLLEN _nullLen(SQL_NULL_DATA)，但这不是它应该指向的类型。

请确保您理解传递给SQLBindParameter()的每个参数。

Answer 2

我怀疑您更新了多个列，并且截断不是在UNIQUEIDENTIFIER列上，而是在其他列上。我启动了我的旧VS，并编写了以下示例程序，它更新为NULL很好。您可能需要进行跟踪，以查看实际执行的是什么。

#include "stdafx.h"
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <conio.h>
#include <tchar.h>
#include <stdlib.h>
#include <sal.h>

#define TRYODBC(h, ht, x)   {   RETCODE rc = x;\
if (rc != SQL_SUCCESS) \
                                { \
                                HandleDiagnosticRecord(h, ht, rc); \
                                } \
if (rc == SQL_ERROR) \
                                { \
                                fwprintf(stderr, L"Error in " L#x L"\n"); \
                                goto Exit;  \
                                }  \
}

void HandleDiagnosticRecord(SQLHANDLE      hHandle,
    SQLSMALLINT    hType,
    RETCODE        RetCode);

int __cdecl wmain(int argc, _In_reads_(argc) WCHAR **argv)
{
    SQLHENV     hEnv = NULL;
    SQLHDBC     hDbc = NULL;
    SQLHSTMT    hStmt = NULL;
    WCHAR*      pwszConnStr;

    // Allocate an environment

    if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv) == SQL_ERROR)
    {
        fwprintf(stderr, L"Unable to allocate an environment handle\n");
        exit(-1);
    }

    TRYODBC(hEnv,
        SQL_HANDLE_ENV,
        SQLSetEnvAttr(hEnv,
        SQL_ATTR_ODBC_VERSION,
        (SQLPOINTER)SQL_OV_ODBC3,
        0));

    // Allocate a connection
    TRYODBC(hEnv,
        SQL_HANDLE_ENV,
        SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc));

    pwszConnStr = L"";
    
    TRYODBC(hDbc,
        SQL_HANDLE_DBC,
        SQLDriverConnect(hDbc,
        GetDesktopWindow(),
        pwszConnStr,
        SQL_NTS,
        NULL,
        0,
        NULL,
        SQL_DRIVER_COMPLETE));

    fwprintf(stderr, L"Connected!\n");

    TRYODBC(hDbc,
        SQL_HANDLE_DBC,
        SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt));
        RETCODE     RetCode = NULL;
        SQLSMALLINT sNumResults;

        //Here be dragons
        SQLLEN _nullLen(SQL_NULL_DATA);
        SQLRETURN  _retcode = SQLBindParameter(hStmt, 1,
            SQL_PARAM_INPUT,
            SQL_C_CHAR,
            SQL_VARCHAR,
            37,
            NULL,
            NULL,
            0,
            &_nullLen);
        if (_retcode == -1)
        {
            HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, _retcode);
            return 1;
        }
        _retcode = SQLPrepare(hStmt, L"UPDATE zz SET v = ? ", SQL_NTS);
        if (_retcode == -1)
        {
            HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, _retcode);
            return 1;
        }
        RetCode= SQLExecute(hStmt);

        switch (RetCode)
        {
        case SQL_SUCCESS_WITH_INFO:
        {
            HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, RetCode);
            // fall through
                                      
        }
        case SQL_SUCCESS:
        {
            // If this is a row-returning query, display
            // results
            TRYODBC(hStmt,
                SQL_HANDLE_STMT,
                SQLNumResultCols(hStmt, &sNumResults));

            {
                SQLLEN cRowCount;

                TRYODBC(hStmt,
                    SQL_HANDLE_STMT,
                    SQLRowCount(hStmt, &cRowCount));

                if (cRowCount >= 0)
                {
                    wprintf(L"%Id %s affected\n",
                        cRowCount,
                        cRowCount == 1 ? L"row" : L"rows");
                }
            }
            break;
        }

        case SQL_ERROR:
        {
            HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, RetCode);
            break;
        }

        default:
            fwprintf(stderr, L"Unexpected return code %hd!\n", RetCode);

        }
        TRYODBC(hStmt,
            SQL_HANDLE_STMT,
            SQLFreeStmt(hStmt, SQL_CLOSE));
        wprintf(L"Thanks for playing, type Enter to exit");
        getchar();

Exit:

    // Free ODBC handles and exit

    if (hStmt)
    {
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    }

    if (hDbc)
    {
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    }

    if (hEnv)
    {
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    }

    wprintf(L"\nDisconnected.");

    return 0;

}

void HandleDiagnosticRecord(SQLHANDLE      hHandle,
    SQLSMALLINT    hType,
    RETCODE        RetCode)
{
    SQLSMALLINT iRec = 0;
    SQLINTEGER  iError;
    WCHAR       wszMessage[1000];
    WCHAR       wszState[SQL_SQLSTATE_SIZE + 1];


    if (RetCode == SQL_INVALID_HANDLE)
    {
        fwprintf(stderr, L"Invalid handle!\n");
        return;
    }

    while (SQLGetDiagRec(hType,
        hHandle,
        ++iRec,
        wszState,
        &iError,
        wszMessage,
        (SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
        (SQLSMALLINT *)NULL) == SQL_SUCCESS)
    {
        // Hide data truncated..
        if (wcsncmp(wszState, L"01004", 5))
        {
            fwprintf(stderr, L"[%5.5s] %s (%d)\n", wszState, wszMessage, iError);
        }
    }
}