c++ - SQL Server 连接 C++

Question

我在连接到我的本地 SQL Server Express 数据库时遇到问题。我是 c++ 的新手，想尝试摆弄“简单”的服务器连接来摆弄 dbs。但是我有两个主要的错误，我不断得到并且还没有弄清楚我相信后端中的所有内容都设置正确(即服务器用户/登录和可访问性)这是我有 atm 的代码:

#include "pch.h"
#include <iostream>
#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>

using namespace std;

void showSQLError(SQLCHAR desc[1024], unsigned int handleType, const     SQLHANDLE& handle)
{
SQLCHAR SQLState[1024];
SQLCHAR message[1024];
if (SQL_SUCCESS == SQLGetDiagRec(handleType, handle, 1, SQLState, NULL, message, 1024, NULL))
    // Returns the current values of multiple fields of a diagnostic record that contains error, warning, and status information
    cout << "At Point:" << desc << endl;
    cout << "SQL driver message: " << message << "\nSQL state: " << SQLState << "." << endl;
}

int main()
{

cout << "Hello world" << endl;
SQLHANDLE SQLEnvHandle = NULL;
SQLHANDLE SQLConnectionHandle = NULL;
SQLHANDLE SQLStatementHandle = NULL;
SQLRETURN retCode = 0;
//desc
SQLCHAR noData[1024] = "SQL_NO_DATA\n";
SQLCHAR invalidHandle[1024] = "SQL_INVALID_HANDLE\n";
SQLCHAR error[1024] = "SQL_ERROR\n";
SQLCHAR success[1024] = "Connection succesful";

char SQLQuery[] = "SELECT * FROM roster";

do {
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &SQLEnvHandle))
        // Allocates the environment
        break;

    if (SQL_SUCCESS != SQLSetEnvAttr(SQLEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
        // Sets attributes that govern aspects of environments
        break;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, SQLEnvHandle, &SQLConnectionHandle))
        // Allocates the connection
        break;

    if (SQL_SUCCESS != SQLSetConnectAttr(SQLConnectionHandle, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0))
        // Sets attributes that govern aspects of connections
        break;

    SQLCHAR retConString[1024]; // Conection string
    switch (SQLDriverConnect(SQLConnectionHandle, NULL, (SQLCHAR*)"DRIVER={SQL Server Native Client 11.0}; SERVER=MYPC\SQLEXPRESS, 1433; DATABASE=MYdb; UID=myID; PWD=myPWD ", SQL_NTS, retConString, 1024, NULL, SQL_DRIVER_NOPROMPT)) {
        // Establishes connections to a driver and a data source
    case SQL_SUCCESS:
        break;
    case SQL_SUCCESS_WITH_INFO:
        break;
    case SQL_NO_DATA_FOUND:
        showSQLError(noData , SQL_HANDLE_DBC, SQLConnectionHandle);
        retCode = -1;
        break;
    case SQL_INVALID_HANDLE:
        showSQLError(invalidHandle, SQL_HANDLE_DBC, SQLConnectionHandle);
        retCode = -1;
        break;
    case SQL_ERROR:

        showSQLError(error, SQL_HANDLE_DBC, SQLConnectionHandle);
        retCode = -1;
        break;
    default:
        break;
    }

    if (retCode == -1)
        break;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, SQLConnectionHandle, &SQLStatementHandle))
        // Allocates the statement
        break;

    if (SQL_SUCCESS != SQLExecDirect(SQLStatementHandle, (SQLCHAR*)SQLQuery, SQL_NTS)) {
        // Executes a preparable statement
        showSQLError(success, SQL_HANDLE_STMT, SQLStatementHandle);
        break;
    }
    else {
        char name[256];
        int age;
        while (SQLFetch(SQLStatementHandle) == SQL_SUCCESS) {
            // Fetches the next rowset of data from the result
            SQLGetData(SQLStatementHandle, 1, SQL_C_DEFAULT, &name, sizeof(name), NULL);
            SQLGetData(SQLStatementHandle, 2, SQL_C_DEFAULT, &age, sizeof(age), NULL);
            // Retrieves data for a single column in the result set
            cout << name << " " << age << endl;
        }
    }
} while (FALSE);

SQLFreeHandle(SQL_HANDLE_STMT, SQLStatementHandle);
SQLDisconnect(SQLConnectionHandle);
SQLFreeHandle(SQL_HANDLE_DBC, SQLConnectionHandle);
SQLFreeHandle(SQL_HANDLE_ENV, SQLEnvHandle);
// Frees the resources and disconnects

getchar();
}

所以我上面代码的初始错误是:在点:SQL_ERROR

SQL 驱动程序消息:[Microsoft][SQL Server Native Client 11.0]TCP 提供程序:未知此类主机。

SQL 状态:08001。

如果我将连接字符串更改为:

SQLDriverConnect(SQLConnectionHandle, NULL, (SQLCHAR*)"DRIVER={SQL Server         
 Native Client 11.0}; SERVER=MYPC\\SQLEXPRESS, 1433; DATABASE=MYdb; 
UID=myID; PWD=myPWD ", SQL_NTS, retConString, 1024, NULL, SQL_DRIVER_NOPROMPT)) 

注意:在SERVER字符串中添加'\'我得到:在点:SQL_ERROR

SQL 驱动程序消息:[Microsoft][SQL Server Native Client 11.0]TCP 提供程序:无法建立连接，因为目标机器主动拒绝它。

SQL 状态:08001。

我相信\是正确的，但不清楚为什么会被拒绝。我已经正确(假设)设置了服务器的所有权限和帐户，除非我的方法完全不正确。我的本地服务器会在哪里拒绝我的连接尝试？

Answer 1

我从未见过像“MYPC\SQLEXPRESS, 14.0”这样的实例名称。它几乎可以肯定只是“MYPC\SQLEXPRESS”，如果它实际上是“MYPC\SQLEXPRESS，14.0”，它需要按照驱动程序名称的方式包含在 {} 中。

我建议您使用“sqlcmd -L”命令(如果您还没有从 https://www.microsoft.com/en-us/download/details.aspx?id=53591 安装它)并使用返回的实例名称之一。

sqlcmd 优于我所知道的其他实例列表方法的好处是 sqlcmd 使用 ODBC，因此您将获得 SQL Server ODBC 驱动程序可以使用的名称。