SQL Server 2017 C++ ODBC 连接在 Linux 上不起作用

Question

我们正在使用Azure SQL Server 2017数据库，并希望通过c ++连接到它。我们已经在 Windows 上实现了这一令人难以置信的壮举，但有必要在 ubuntu 上运行它。

我们使用本教程安装了 Linux ODBC 13 驱动程序，我们的/etc/odbcinst.ini文件包含以下内容

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
UsageCount=1
[ODBC]
Trace = Yes
TraceFile = /dev/stdout

我们的代码如下：

#include <iostream>
#include <string>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include <stdio.h>
using namespace std;
int main() {
    #define SQL_RESULT_LEN 240
    #define SQL_RETURN_CODE_LEN 1024
    //define handles and variables
    SQLHDBC      hdbc = SQL_NULL_HDBC;   // Connection handle
    SQLHANDLE    sqlConnHandle = NULL;
    SQLHANDLE    sqlStmtHandle = NULL;
    SQLHANDLE    sqlEnvHandle;
    SQLCHAR      outstr[SQL_RETURN_CODE_LEN];
    SQLRETURN    retcode;
    SQLSMALLINT  outstrlen;
    //allocations
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
        cout << "SQLAllocHandle err (ENV)" << endl;
    if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
        cout << "SQLSetEnvAttr err" << endl;
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
        cout << "SQLAllocHandle (DBC) err" << endl;
    //output
    cout << "Attempting connection to SQL Server..." << endl;
    //Connect to server
    retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)"Driver={ODBC Driver 13 for SQL Server};Server=tcp:cppdatabase.database.windows.net,1433;Database=stockData;Uid=admin@cppdatabase;Pwd=password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;", SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
    switch (retcode) {
    case SQL_SUCCESS:
        cout << "Successfully connected to SQL Server" << endl;
        break;
    case SQL_SUCCESS_WITH_INFO:
        cout << "Successfully connected to SQL Server"<< endl;
        break;
    case SQL_INVALID_HANDLE:
        cout << "Could not connect to SQL Server" << endl;
        break;
    case SQL_ERROR:
        cout << "Could not connect to SQL Server ERR" << endl;
        break;
    default:
        break;
    }
}

我们用 g++ 编译如下： g++ -g mssql.cpp -fpermissive -lodbc -o mssql

但是，此代码无法连接，我们得到以下输出(没有编译错误(：

[ODBC][17915][1559000402.543184][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0xf04d10
[ODBC][17915][1559000402.543204][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0xf04d10
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 0
[ODBC][17915][1559000402.543216][SQLSetEnvAttr.c][363]
        Exit:[SQL_SUCCESS]
[ODBC][17915][1559000402.543223][SQLAllocHandle.c][375]
        Entry:
            Handle Type = 2
            Input Handle = 0xf04d10
[ODBC][17915][1559000402.543232][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0xf05610
Attempting connection to SQL Server...
[ODBC][17915][1559000402.543250][SQLDriverConnect.c][686]Error: SQL_INVALID_HANDLE
Could not connect to SQL Server
Could not connect to SQL Server ERR

我们使用的连接字符串直接来自 Azure 门户(而敏感数据已替换为通用变体(，因此我们怀疑这是否存在任何问题。我们怀疑这是一个驱动程序问题，因为 ODBC 已成功登录到标准输出。所以我们想象这一定是一个编码问题。

任何指针(没有双关语(？

Answer 1

我们使用域名 (DNS( 解决了这个问题。在我们的/etc/odbc.ini 文件中，我们写道：

[mssql]
Description = "Test for Microsoft SQL Server 2017 using ODBC 2013 driver"
Driver = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
Database = stockData
Server = cppdatabase.database.windows.net
Port = 1433

然后我们使用以下代码进行连接：

#include <iostream>
#include <string>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include <stdio.h>
#include <cstdlib>
using namespace std;
int main() {  
   SQLHENV henv;  
   SQLHDBC hdbc;  
   SQLHSTMT hstmt;  
   SQLRETURN retcode;  
   SQLCHAR * OutConnStr = (SQLCHAR * )malloc(255);  
   SQLSMALLINT * OutConnStrLen = (SQLSMALLINT *)malloc(255);  
   // Allocate environment handle  
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
   // Set the ODBC version environment attribute  
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
      retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);   
      // Allocate connection handle  
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
         retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);   
         // Set login timeout to 5 seconds  
         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
            SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);  
            // Connect to data source  
            retcode = SQLConnect(hdbc, (SQLCHAR*) "mssql", SQL_NTS, (SQLCHAR*) "QuantitateAdmin", 15, "SomeRandomPassword1", 19);  
            // Allocate statement handle  
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
                cout << "Success!" << endl;
               retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);   
               // Process data  
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);  
               }  
               SQLDisconnect(hdbc);  
            } else {
                cout << "Error" << endl;
            }  
            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);  
         }  
      }  
      SQLFreeHandle(SQL_HANDLE_ENV, henv);  
   }  
}