pythoncsql-serverodbc

Issues using ODBC in C - sqlState returns IM008 (possible issues with connection string?)


I am having issues with the following implementation in C. (Of course I have obscured the sensitive info in the connection string).

#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include <stdio.h>

int main() {
    SQLHENV hEnv;
    SQLHDBC hDbc;
    SQLRETURN ret; /* ODBC API return status */

    SQLCHAR* connStr = (SQLCHAR*)"DRIVER={ODBC Driver 17 for SQL Server}; SERVER={tcp:xxx.xxx.xxx.xxx,xxxx}; UID=****; PWD=*****; DATABASE={SDC};\0";

    printf("%s\n", connStr);


    // Allocate an environment handle
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        printf("Error allocating environment handle\n");
        return -1;
    }

    // Set the ODBC version environment attribute
    ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        printf("Error setting environment attribute\n");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return -1;
    }

    // Allocate a connection handle
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        printf("Error allocating connection handle\n");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return -1;
    }

    // Set login timeout to 5 seconds
    SQLSetConnectAttr(hDbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

    // Connect to the SQL Server
    ret = SQLDriverConnect(hDbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

    if (ret == SQL_ERROR) {
        SQLCHAR sqlState[12] = { 0 };  // 5 characters + null terminator
        SQLINTEGER nativeError;
        SQLCHAR messageText[2048] = { 0 };  // Message buffer
        SQLSMALLINT messageLength;

        // Fetch diagnostic information
        ret = SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, 1, sqlState, &nativeError, messageText, sizeof(messageText), &messageLength);

        // Null-terminate the message if it exceeds the buffer size
        if (messageLength > sizeof(messageText) - 1) {
            messageText[sizeof(messageText) - 1] = '\0';
        }

        printf("SQLSTATE: %s\n", sqlState);
        printf("Native Error Code: %d\n", nativeError);
        printf("Message: %s\n", messageText);
    }
    else if (ret == SQL_SUCCESS_WITH_INFO) {
        printf("Connection successful with info!\n");
    }
    else if (ret == SQL_SUCCESS) {
        printf("Connection successful!\n");
    }
    else {
        printf("Error connecting to the database\n");
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return -1;
    }

    // Close the connection and free handles
    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv); 

    return 0;
}

Working Python implementation:

import pyodbc as sql
import pandas as pd
import sys
import warnings
from time import (strftime, strptime)


server = 'tcp:xxx.xxx.xxx.xxx,xxxx'
db = 'SDC'

string = "DRIVER={ODBC Driver 17 for SQL Server}; SERVER={"+server+"}; UID=****; PWD=*****; DATABASE={"+db+"}; "

print(string)

try:
    db = sql.connect(string)
    print('It worked!')
except Exception as ex:
    print(ex)
    


It returns a Dialog failed message text and sqlState IM008. From what I see online it might be an error with the login credentials. However the string is exactly formatted as in a working Python implementation (below the C code). Does anyone have an idea what am I doing wrong? The printf statement shows the string exactly as I want it.


Solution

  • As correctly pointed out in the comments by @pmg the issue was that I did not use wchar (windows wide strings) in my code.

    #include <windows.h>
    #include <sqlext.h>
    #include <sqltypes.h>
    #include <sql.h>
    #include <stdio.h>
    #include <wchar.h>
    
    int main() {
        SQLHENV hEnv;
        SQLHDBC hDbc;
        SQLRETURN ret; /* ODBC API return status */
    
        wchar_t connStr[] = L"DRIVER={ODBC Driver 17 for SQL Server}; SERVER={tcp:xxx.xxx.xxx.xxx,xxxx}; UID=****; PWD=*****; DATABASE={SDC};";
    
        // Allocate an environment handle
        ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
        if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
            wprintf(L"Error allocating environment handle\n");
            return -1;
        }
    
        // Set the ODBC version environment attribute
        ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
        if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
            wprintf(L"Error setting environment attribute\n");
            SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
            return -1;
        }
    
        // Allocate a connection handle
        ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
        if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
            wprintf(L"Error allocating connection handle\n");
            SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
            return -1;
        }
    
        // Set login timeout to 5 seconds
        SQLSetConnectAttr(hDbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    
        // Connect to the SQL Server
        ret = SQLDriverConnectW(hDbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    
        if (ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO) {
            SQLWCHAR sqlState[6] = { 0 };  // 5 characters + null terminator
            SQLINTEGER nativeError;
            SQLWCHAR messageText[512] = { 0 };  // Message buffer
            SQLSMALLINT messageLength;
            int i = 1;
    
            // Fetch diagnostic information
            while (SQLGetDiagRecW(SQL_HANDLE_DBC, hDbc, i, sqlState, &nativeError, messageText, sizeof(messageText) / sizeof(SQLWCHAR), &messageLength) != SQL_NO_DATA) {
                // Null-terminate the message if it exceeds the buffer size
                if (messageLength > sizeof(messageText) / sizeof(SQLWCHAR) - 1) {
                    messageText[sizeof(messageText) / sizeof(SQLWCHAR) - 1] = L'\0';
                }
    
                wprintf(L"SQLSTATE: %ls\n", sqlState);
                wprintf(L"Native Error Code: %d\n", nativeError);
                wprintf(L"Message: %ls\n", messageText);
                i++;
            }
        }
    
        if (ret == SQL_SUCCESS_WITH_INFO) {
            wprintf(L"Connection successful with info!\n");
        }
        else if (ret == SQL_SUCCESS) {
            wprintf(L"Connection successful!\n");
        }
        else {
            wprintf(L"Error connecting to the database\n");
            SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
            SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
            return -1;
        }
    
        // Close the connection and free handles
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    
        return 0;
    }