odbc 获取列名称 SQLColumnsW

 在dts团队工作，遇到有人报了个bug，说是snowflake在同一个server里， 如果有两个database也就是database1和database2里，同时存在着名称相同schema也相同的表格，SnowflakeDSIIDriver执行sqlcolumns会遇到重复列名的问题。给snowflake团队报了bug他们表示除非你拿出demo不然不信，于是这就是demo

#include <iostream>
#include <vector>
#include <stdio.h>
#include <sqlext.h>
#include <windows.h>  
#include <string>
#define REM_LEN 256 + 1  
#define ARRAYLEN(x)    (sizeof((x)) / sizeof(*(x)))
using namespace std;

int getColumnList(SQLWCHAR* pwszInConnStr, vector<wstring>& supportedColumns) {
    SQLHENV henv;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = 0;
    SQLRETURN retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
        return -1;
    }

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
        return -2;
    }

    SQLSMALLINT nInConnStr = 0;
    SQLWCHAR    pwszOutConnStr[1024];
    SQLSMALLINT nOutConnStr = 0;
    nInConnStr = (SQLSMALLINT)lstrlen(pwszInConnStr);

    retcode = SQLDriverConnect(hdbc, NULL, pwszInConnStr, nInConnStr,
        pwszOutConnStr, ARRAYLEN(pwszOutConnStr), &nOutConnStr, 0);

    // Allocate statement handle  
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        return -3;
    }

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    std::wstring wsSchemaName = L"DBO";
    std::wstring wsTableName = L"TEST1";

    retcode = SQLColumnsW(hstmt, NULL, 0, (SQLWCHAR*)(wsSchemaName.c_str()), (SQLSMALLINT)wsSchemaName.length(),
        (SQLWCHAR*)(wsTableName.c_str()), (SQLSMALLINT)wsTableName.length(), NULL, 0);

    // Allocate statement handle  
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        return -4;
    }

    // Bind columns in result set to buffers  

    SQLWCHAR szColumnName[REM_LEN];
    SQLINTEGER cbColumnName;

    SQLBindCol(hstmt, 4, SQL_C_WCHAR, szColumnName, REM_LEN * 2, &cbColumnName);

    while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) {
        wstring wsTempColName(szColumnName);
        supportedColumns.push_back(wsTempColName);
    }

    // Process data  
    // Allocate statement handle  
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        return -5;
    }

    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    }

    SQLDisconnect(hdbc);
}

void printVector(string cDataInConnStr, vector<wstring> vectors) {
    cout << cDataInConnStr << endl << "Column Lists:   "<<endl;
    for (int i = 0; i < vectors.size(); i++) {
        wcout << vectors[i] << "    ";
    }
    cout << endl << endl;
}

int main() {

    SQLWCHAR* cDataInConnStr = (SQLWCHAR*)L"password=1991Chani@;Driver={CData ODBC Driver for Snowflake};url=https://mi20141.central-india.azure.snowflakecomputing.com;user=chanakyaj;database=DATABASE1;";
    vector<wstring> cDataColumns;
    getColumnList(cDataInConnStr, cDataColumns);
    printVector("Driver={CData ODBC Driver for Snowflake}", cDataColumns);

    SQLWCHAR* dSIIDriverInConnStr = (SQLWCHAR*)L"server=mi20141.central-india.azure.snowflakecomputing.com;uid=chanakyaj;pwd=1991Chani@;Driver={SnowflakeDSIIDriver};database=DATABASE1;";
    vector<wstring> dSIIDrivercolumns;
    getColumnList(dSIIDriverInConnStr, dSIIDrivercolumns);
    printVector("Driver = {SnowflakeDSIIDriver}", dSIIDrivercolumns);

    return 0;
}

 

代码平平无奇，但是其中我遇到了几次问题，最主要的就是string需要加一个L， 这里面涉及到wstring，

wstring是宽char，Unicode编码，一般情况下一个字符占两个字节大小
string是窄char，AscII编码，一个字符占一个字节大小

我的代码一开始错写了第二种情况，其实编译也是有warning的， 这边建议下次直接error好吗！

Warning    C6276    Cast between semantically different string types:  char * to wchar_t *.  Use of invalid string can lead to undefined behavior.   

int main() {
    std::wstring wsSchemaName = L"DBO";
    wcout << endl << "(SQLWCHAR*)(L'DBO') result is:" << (SQLWCHAR*)(L"DBO");
    wcout << endl << "(SQLWCHAR*)('DBO') result is:" << (SQLWCHAR*)("DBO");

    cout << endl << "cout (SQLWCHAR*)(L'DBO') result is:" << (SQLWCHAR*)(L"DBO");
    cout << endl << "cout (SQLWCHAR*)('DBO') result is:" << (SQLWCHAR*)("DBO") << endl;

    return 0;
}