C++ connect to SQL Server not working, trouble identifying error

Question

I'm trying to connect to a database and write an insert statement to it with C++. I'm using Visual Studio 2022. I can't tell why it's not connecting and the function I pieced together to see the error doesn't show the error. Any help would be appreciated:

#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:
}

The output is

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

I think `cout` is failing to print `SQLWCHAR` and is just printing the pointers instead https://stackoverflow.com/questions/2493785/how-i-can-print-the-wchar-t-values-to-console — Alan Birtles, May 18 '23 at 17:54
using std::wcout does output the error message. I'm having a bit of trouble resolving it though and could use more help here. The output is :SQL driver message: [Microsot][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. SQL state: 08001. I found a post: https://stackoverflow.com/questions/63864427/visual-studio-sqldriverconnect-is-failing-returning-sql-error that is similar but I'm pretty sure I have tcp/ip enabled as I can connect to the db through nodejs code and ssms — rawmud, May 18 '23 at 18:12

score 1 · Accepted Answer · answered May 18 '23 at 19:13

I had to change 2 things to get it to work. In the error handling function, In needed to change it to std::wcout. In the connection string, I needed to remove the quotation marks. Below code worked:

#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::wcout << "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:
}

C++ connect to SQL Server not working, trouble identifying error

1 Answers1