We have a C++ application which uses ODBC to connect to SQL Server database. It is running on CentOS 7 application server. It is working flawlessly with msodbcsql17 version 17.4.2.1-1, however it breaks with version 17.6.1.1-1. Can you please advise why? Here is the relevant code:
#include "DBODBC.h"
#include "ODBCResultSet.h"
#include <sqlext.h>
#include <sqlncli.h>
/* ----------------------------------------------------------------------------- */
/** Creates environment handle and sets ODBC version
* /returns True if intialization succeeds, false otherwise
*/
/* ----------------------------------------------------------------------------- */
bool DBODBC::Init()
{
DB::Init();
if (!Check(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_Env), m_Env,SQL_HANDLE_ENV ))
{
LOG_E("DBODBC::Init - Failed to create environment handle");
return false;
};
if (!Check(SQLSetEnvAttr(m_Env,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0), m_Env,SQL_HANDLE_ENV))
{
LOG_E("DBODBC::Init - Failed to set ODBC version");
return false;
};
return true;
}
/* ----------------------------------------------------------------------------- */
/** Creates a connection to the database based on the parameters passed
* /param user Null terminated string containing username to be used to connect to the DB
* /param password Null terminated string containing password to be used to connect to the DB
* /param DBName Null terminated string containing ODBC datasource name of the DB to connect to
* /returns True if succeeded to connect to the DB, false otherwise
*/
/* ----------------------------------------------------------------------------- */
bool DBODBC::Connect(const char * user, const char * password, const char * DBName)
{
LOG_I(2, "DBODBC::Connect - Started");
if (IsConnected())
{
LOG_I(5, "DBODBC::Connect - DB connection is already up");
return false;
}
//allocate connection handle
if (SQLAllocHandle(SQL_HANDLE_DBC, m_Env, &m_Conn)==SQL_ERROR)
{
LOG_W("DBODBC::Connect - Failed to allocate connection handle");
return false;
}
SQLSetConnectAttr(m_Conn, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
LOG_I(2, "DBODBC::Connect - Calling SQLConnect");
if (!Check(SQLConnect(m_Conn, (SQLCHAR*)DBName, SQL_NTS,(SQLCHAR*)user,SQL_NTS, (SQLCHAR*)password, SQL_NTS), m_Conn, SQL_HANDLE_DBC))
{
LOG_W("DBODBC::Connect - Failed to open connection to datasource \"%s\"", DBName);
return false;
}
LOG_I(5, "DBODBC::Connect - Successfully connected to %s", DBName);
m_bConnectionOk=true;
m_bIsConnected=true;
return true;
}
/* ----------------------------------------------------------------------------- */
/** Checks value returned by ODBC and collects diagnostic info if failed
*/
/* ----------------------------------------------------------------------------- */
bool DBODBC::Check(SQLRETURN RetCode, SQLHANDLE hHandle, SQLSMALLINT hType)
{
LOG_I(2, "DBODBC::Check - Started");
SQLSMALLINT iRec = 0;
SQLINTEGER iError;
SQLCHAR wszMessage[1000];
SQLCHAR wszState[SQL_SQLSTATE_SIZE+1];
bool retval;
switch (RetCode)
{
case SQL_SUCCESS_WITH_INFO:
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
LOG_W("DBODBC::Check - [%5.5s] %s (%d)\n", wszState, wszMessage, iError);
}
case SQL_SUCCESS:
return true;
case SQL_INVALID_HANDLE:
LOG_W("DBODBC::Check - SQL handle is invalid");
return false;
break;
case SQL_ERROR:
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
LOG_W("DBODBC::Check - [%5.5s] %s (%d)\n", wszState, wszMessage, iError);
}
return false;
break;
case SQL_NO_DATA:
//No data found, no need to log it, just return true;
return true;
break;
default:
LOG_W("DBODBC::Check - Unknown result %d", RetCode);
return false;
}
}
In logs I can see that it hangs on the SQLConnect method call:
2073163264 - INFO (2) - [2020.12.11 15:13:10.728] - DBODBC::Check - Started
2073163264 - INFO (2) - [2020.12.11 15:13:10.728] - DBODBC::Check - Started
2073163264 - INFO (2) - [2020.12.11 15:13:10.729] - DBODBC::Init - Done
2073163264 - INFO (2) - [2020.12.11 15:13:10.729] - DBODBC::Connect - Started
2073163264 - INFO (2) - [2020.12.11 15:13:10.729] - DBODBC::Connect - Calling SQLConnect
No more logs after that, even the "Started" message in the Check method is not logged. Based on msodbcsql documentation (https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlconnect-function?view=sql-server-ver15), we are using the library correctly. Also I could not find any details in the release notes that would suggest that something was changed and I should upgrade my application also.
EDIT:
I removed current version of msodbcsql17 and installed the earlier version, even tried multiple versions, it is not working at all. We have two servers, one of them still on 17.4.2.1-1 version of msodbcsql17, application works fine. Other server had the new 17.6.1.1-1 version installed by default, application was not working, and it is not working after downgrading to 17.4.2.1-1 version.
Okay, it seems I missed one of the checks I made somehow: odbcinst.ini is actually changed! During the 17.6 msodbc install, one line was removed. After adding it again, it started working immediately:
Why was this line removed, I don't know. Or, in case of fresh install, this line was never added to odbcinst.ini. Anyway, it works now.