C++连接SQL Server失败，无法识别错误请求技术帮助

使用Visual Studio 2022，通过C++（ODBC）连接SQL Server并准备插入数据时连接失败，且自定义的错误查看函数无法输出可读的错误信息，程序输出了类似内存地址的乱码内容。

用户提供的代码如下：

#include <iostream>
#include <string>
#include <fstream>
#include <sstream>
#include <vector>

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


void showSQLError(unsigned int handleType, const SQLHANDLE& handle)
{
    SQLWCHAR SQLState[1024];
    SQLWCHAR message[1024];
    if (SQL_SUCCESS == SQLGetDiagRec(handleType, handle, 1, SQLState, NULL, message, 1024, NULL))
        std::cout << "SQL driver message: " << message << "\nSQL state: " << SQLState << "." << std::endl;
}

int main() {
    //Connect to DB code:
#define SQL_RESULT_LEN 240
#define SQL_RETURN_CODE_LEN 1000

    SQLHANDLE sqlConnHandle;
    SQLHANDLE sqlStmtHandle;
    SQLHANDLE sqlEnvHandle;
    SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];
    //initializations
    sqlConnHandle = NULL;
    sqlStmtHandle = NULL;

    std::wstring email = L"test@gmail.com";
    std::wstring strategy = L"TestStrategy1";
    std::wstring CP = L"C";
    std::wstring watchListDate = L"2023-04-08";

    std::wstring watchListString = L"test1,930,test2,1345,test3,1500";
    std::wstring create_date = L"2023-05-10";

    std::wstringstream result1(L"Insert into watchList(emailID,strategy,CP,watchList_Date,watchList,create_date) values(");
    result1 << "\'" << email << "\',\'" << strategy << "\',\'" << CP << "\',\'" << watchListDate << "\',\'" << watchListString << "\',\'" << create_date << "\')";
    std::wstring sqlString = result1.str();


    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
        goto COMPLETED;

    if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
        goto COMPLETED;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
        goto COMPLETED;

    std::cout << "Attempting connection to SQL Server...";
    std::cout << "\n";
    
    switch (SQLDriverConnect(sqlConnHandle,
        NULL,
        (SQLWCHAR*)L"DRIVER={SQL Server};SERVER='ec2-xxx-xx-xxx-xxx.compute-x.amazonaws.com', 1433;DATABASE=master;UID='sa';PWD='testPassword1';Trusted=true ",
        
        SQL_NTS,
        retconstring,
        1024,
        NULL,
        SQL_DRIVER_NOPROMPT)) {
    case SQL_SUCCESS:
        std::cout << "Successfully connected to SQL Server";
        std::cout << "\n";
        break;
    case SQL_SUCCESS_WITH_INFO:
        std::cout << "Successfully connected to SQL Server";
        std::cout << "\n";
        break;
    case SQL_INVALID_HANDLE:
        std::cout << "Could not connect to SQL Server1";
        std::cout << "\n";
        goto COMPLETED;
    case SQL_ERROR:
        std::cout << "Could not connect to SQL Server2";
        std::cout << "\n";
        std::cout << SQL_ERROR;
        showSQLError(SQL_HANDLE_DBC, sqlConnHandle);
        goto COMPLETED;
    default:
        break;
    }
COMPLETED:
}

程序输出：

Could not connect to SQL Server2
-1SQL driver message:000000E2750FD180
SQL state:000000E2750FC960

1. 修复错误显示函数（核心问题：宽字符输出不兼容） #

SQLWCHAR是宽字符类型（对应wchar_t），直接用std::cout输出会打印内存地址而非字符串内容，必须改用std::wcout，同时调用宽字符版本的SQLGetDiagRecW确保类型匹配：

void showSQLError(unsigned int handleType, const SQLHANDLE& handle)
{
    SQLWCHAR SQLState[1024];
    SQLWCHAR message[1024];
    if (SQL_SUCCESS == SQLGetDiagRecW(handleType, handle, 1, SQLState, NULL, message, 1024, NULL))
    {
        std::wcout << L"SQL驱动信息: " << message << L"\nSQL状态码: " << SQLState << L"." << std::endl;
    }
}

2. 修正连接字符串的格式错误 #

原连接字符串存在3个关键问题：

• 服务器地址格式错误：SERVER='xxx', 1433应改为SERVER=xxx,1433（去掉引号，逗号后无空格）
• 认证方式冲突：同时指定UID/PWD（SQL Server认证）和Trusted=true（Windows集成认证），二选一即可
• 驱动版本过旧：{SQL Server}是旧驱动，推荐使用官方最新的{ODBC Driver 17 for SQL Server}

修正后的连接字符串（SQL Server认证示例）：

(SQLWCHAR*)L"DRIVER={ODBC Driver 17 for SQL Server};SERVER=ec2-xxx-xx-xxx-xxx.compute-x.amazonaws.com,1433;DATABASE=master;UID=sa;PWD=testPassword1;"

3. 其他必要优化 #

• 添加资源释放逻辑：避免内存泄漏，在程序结束前释放ODBC句柄
• 替换字符串拼接为参数化查询：防止SQL注入风险，示例代码如下：

// 准备参数化插入语句
SQLWCHAR insertSql[] = L"INSERT INTO watchList(emailID, strategy, CP, watchList_Date, watchList, create_date) VALUES (?, ?, ?, ?, ?, ?)";
SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle);
SQLPrepare(sqlStmtHandle, insertSql, SQL_NTS);

// 绑定参数（需根据字段实际类型调整）
SQLBindParameter(sqlStmtHandle, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (SQLPOINTER)email.c_str(), 0, NULL);
SQLBindParameter(sqlStmtHandle, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (SQLPOINTER)strategy.c_str(), 0, NULL);
// 其余参数同理绑定...

// 执行插入
SQLExecute(sqlStmtHandle);

#include <iostream>
#include <string>
#include <fstream>
#include <sstream>
#include <vector>

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


void showSQLError(unsigned int handleType, const SQLHANDLE& handle)
{
    SQLWCHAR SQLState[1024];
    SQLWCHAR message[1024];
    if (SQL_SUCCESS == SQLGetDiagRecW(handleType, handle, 1, SQLState, NULL, message, 1024, NULL))
    {
        std::wcout << L"SQL驱动信息: " << message << L"\nSQL状态码: " << SQLState << L"." << std::endl;
    }
}

int main() {
#define SQL_RESULT_LEN 240
#define SQL_RETURN_CODE_LEN 1000

    SQLHANDLE sqlConnHandle = NULL;
    SQLHANDLE sqlStmtHandle = NULL;
    SQLHANDLE sqlEnvHandle = NULL;
    SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];

    std::wstring email = L"test@gmail.com";
    std::wstring strategy = L"TestStrategy1";
    std::wstring CP = L"C";
    std::wstring watchListDate = L"2023-04-08";
    std::wstring watchListString = L"test1,930,test2,1345,test3,1500";
    std::wstring create_date = L"2023-05-10";

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
        goto COMPLETED;

    if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
        goto COMPLETED;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
        goto COMPLETED;

    std::cout << "正在尝试连接SQL Server...\n";
    
    switch (SQLDriverConnectW(sqlConnHandle,
        NULL,
        (SQLWCHAR*)L"DRIVER={ODBC Driver 17 for SQL Server};SERVER=ec2-xxx-xx-xxx-xxx.compute-x.amazonaws.com,1433;DATABASE=master;UID=sa;PWD=testPassword1;",
        SQL_NTS,
        retconstring,
        SQL_RETURN_CODE_LEN,
        NULL,
        SQL_DRIVER_NOPROMPT)) {
    case SQL_SUCCESS:
        std::cout << "成功连接SQL Server\n";
        // 此处可添加参数化插入逻辑
        break;
    case SQL_SUCCESS_WITH_INFO:
        std::cout << "成功连接SQL Server（附带提示信息）\n";
        break;
    case SQL_INVALID_HANDLE:
        std::cout << "连接失败：无效句柄\n";
        goto COMPLETED;
    case SQL_ERROR:
        std::cout << "连接失败：发生错误\n";
        showSQLError(SQL_HANDLE_DBC, sqlConnHandle);
        goto COMPLETED;
    default:
        std::cout << "连接结果异常\n";
        goto COMPLETED;
    }

COMPLETED:
    // 释放ODBC资源
    if (sqlStmtHandle != NULL) SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle);
    if (sqlConnHandle != NULL) SQLDisconnect(sqlConnHandle);
    if (sqlConnHandle != NULL) SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle);
    if (sqlEnvHandle != NULL) SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle);

    return 0;
}

内容的提问来源于stack exchange，提问作者rawmud