C++使用ODBC访问SqlServer

傷城~ 2022-06-11 09:19 311阅读 0赞

#include <iostream>
    #include <string>
    #include <list>
    #include <cassert>
    
    #include "windows.h"
    #include "sql.h"
    #include "sqlext.h"
    #include "sqltypes.h"
    
    #pragma comment(lib, "odbc32.lib")
    
    // 1.参考：https://zh.wikipedia.org/zh-cn/ODBC，
    //        http://www.cnblogs.com/kzang/archive/2012/11/29/2792447.html，
    //        http://www.cnblogs.com/kzloser/archive/2012/11/29/2794815.html
    
    // 2.使用odbc连接数据库有两种方法。
    // (A)使用字符串连接。
    // (B)使用odbc管理器连接。在管理器中创建一个DSN，将server名字，所访问的数据库名字等信息都填写正确。
    
    // 3.odbc和sqlite3都提供了两种执行语句的方法，直接执行和prepare之后执行。
    
    static void GetHstmtErrorMsg(SQLHSTMT hstmt) {
      SQLWCHAR error_msg[1024] = { 0 };
      SQLSMALLINT msg_len = 0;
      SQLWCHAR sql_state[6] = { 0 };
      SQLINTEGER error_code = 0;
    
      int i = 1;
      while (true) {
        if (SQLGetDiagRecW(SQL_HANDLE_STMT, hstmt, i, sql_state, &error_code, error_msg, 1024, &msg_len) != SQL_NO_DATA) {
          std::wcout<<error_msg<<std::endl;
        } else {
          break;
        }
        i++;
      }
    }
    
    // 直接执行sql。
    static bool SelectData(SQLHDBC hdbc) {
      SQLHSTMT hstmt = SQL_NULL_HSTMT;
    
      // Allocate execute statement handle.
      SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to allocate execute statement handle!" << std::endl;
        return false;
      }
    
      SQLINTEGER id = 0;
      SQLSMALLINT age = 0;
      SQLCHAR name[50] = { 0 };
      SQLCHAR address[256] = { 0 };
    
      SQLWCHAR select_sql[256] = L"select * from students";
      ret = SQLExecDirect(hstmt, select_sql, SQL_NTS);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to query data!" << std::endl;
      }
    
      // Bind column data.
      SQLBindCol(hstmt, 1, SQL_C_LONG, (void*)&id, sizeof(id), NULL);
      SQLBindCol(hstmt, 2, SQL_C_CHAR, (void*)&name, sizeof(name), NULL);
      SQLBindCol(hstmt, 3, SQL_C_SHORT, (void*)&age, sizeof(age), NULL);
      SQLBindCol(hstmt, 4, SQL_C_CHAR, (void*)&address, sizeof(address), NULL);
    
      while (SQLFetch(hstmt) != SQL_NO_DATA) {
        std::cout << "Id: " << id << "  Name: " << name << " Age: " << age << " Address: " << address << std::endl;
      }
    
      // Release execute statement handle.
      ret = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to release execute statement handle!" << std::endl;
      }
    
      return true;
    }
    
    // Prepare之后执行。
    static bool InsertData(SQLHDBC hdbc) {
      SQLHSTMT hstmt = SQL_NULL_HSTMT;
    
      // Allocate execute statement handle.
      SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to allocate execute statement handle!" << std::endl;
        return false;
      }
    
      SQLSMALLINT age = 0;
      SQLCHAR name[50] = { 0 };  // 不能超过sqlserver中定义的varchar长度。
      SQLCHAR address[256] = { 0 };
    
      SQLWCHAR insert_sql[256] = L"insert into students(name, age, address) values(?,?,?)";
    
      ret = SQLPrepare(hstmt, insert_sql, SQL_NTS);
      if (ret != SQL_SUCCESS) {
        std::cout << "Failed to prepare sql!" << std::endl;
      }
    
      SQLINTEGER name_size = sizeof(name);
      ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, name_size, 0, (void*)name, name_size, &name_size);
      ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_SMALLINT, 0, 0, (void*)&age, 0, 0);
      SQLINTEGER address_size = sizeof(address);
      ret = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, address_size, 0, (void*)address, address_size, &address_size);
    
      age = 100;
      strcpy((char*)name, "Ma");
      strcpy((char*)address, "Maa");
    
      ret = SQLExecute(hstmt);
      if (ret != SQL_SUCCESS) {
        std::cout << "Failed to execute sql!" << std::endl;
      }
    
      age = 102;
      strcpy((char*)name, "Maz");
      strcpy((char*)address, "Maaz");
    
      ret = SQLExecute(hstmt);
      if (ret != SQL_SUCCESS) {
        std::cout << "Failed to execute sql!" << std::endl;
        GetHstmtErrorMsg(hstmt);
      }
    
      // Release execute statement handle.
      ret = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to release execute statement handle!" << std::endl;
      }
    
      return true;
    }
    
    int main() {
      SQLHENV henv = SQL_NULL_HENV;
      SQLHDBC hdbc = SQL_NULL_HDBC;
    
      // Allocate environment handle.
      SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to allocate environment handle!" << std::endl;
        return 1;
      }
    
      // Set the ODBC version environment attribute.
      ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to set the ODBC version environment attribute!" << std::endl;
        return 2;
      }
    
      // Allocate connection handle.
      ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to allocate connection handle!" << std::endl;
        return 3;
      }
    
      // Connect the sqlserver.
      bool use_odbc_management = false;
      
      if (use_odbc_management) {  // (A)使用odbc管理器连接。
        // DSN这个名字是odbc管理器中创建的DSN的名字，不是server或者数据库的名字。
        // 所要访问的server及数据库在管理器中配置。
        SQLWCHAR dsn[64] = L"Test";  // 使用windows认证登陆,所以不用写登录名和密码。
        ret = SQLConnect(hdbc, dsn, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
        if (!SQL_SUCCEEDED(ret)) {
          std::cout << "Failed to connect the sqlserver!" << std::endl;
          return 4;
        }
      } else {  // (B)使用字符串连接。
        SQLWCHAR out_string[1024];
        SQLSMALLINT out_len;
        SQLWCHAR dsn[256] = L"Driver={SQL Server};Server=CNSHWDW22;Database=test;Trusted_Connection=Yes;";
        ret = SQLDriverConnect(hdbc, NULL, dsn, SQL_NTS, out_string, 1024, &out_len, SQL_DRIVER_COMPLETE);;
      }
    
      SelectData(hdbc);
      InsertData(hdbc);
      SelectData(hdbc);
    
      // Disconnect sqlserver.
      ret = SQLDisconnect(hdbc);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to disconnect sqlserver!" << std::endl;
      }
    
      // Release connection handle.
      ret = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to release connection handle!" << std::endl;
      }
    
      // Release environment handle.
      ret = SQLFreeHandle(SQL_HANDLE_ENV, henv);
      if (!SQL_SUCCEEDED(ret)) {
        std::cout << "Failed to release environment handle!" << std::endl;
      }
    
      return 0;
    }