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.
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;
}