CODEWITHSUNDEEP
db2odbc
JKC
JKC

Reputation: 109

IBM DB2 ODBC driver returns the wrong value for the ODBC API SQLGetInfo()

IBM DB2 ODBC driver returns the wrong value when the following ODBC API is called:

void Test()
{
   // Allocate environment handle.
   SQLHENV environmentHandle;
   SQLRETURN returnCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environmentHandle);

   // Set ODBC version to 3.8
   returnCode = SQLSetEnvAttr(environmentHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, 0);

   // Allocate connection handle.
   SQLHDBC connectionHandle;
   returnCode = SQLAllocHandle(SQL_HANDLE_DBC, environmentHandle, &connectionHandle);

   // Connect to the database.
   TCHAR dsn[] = _T("DSN=ODBCDB2"), connStringOut[100] = _T("");
   SQLSMALLINT connStringOutSize = 0;
   returnCode = SQLDriverConnect(connectionHandle, NULL, (SQLTCHAR *)dsn, _tcslen(dsn), (SQLTCHAR*)connStringOut, sizeof(connStringOut) / sizeof(TCHAR), &connStringOutSize, SQL_DRIVER_NOPROMPT);
   // connStringOut = L"DSN=ODBCDB2;UID=<userid>;PWD=<password>;DBALIAS=TESTDB"

   // Retrieve the quote character.
   TCHAR quoteValue[15] = _T("");
   SQLSMALLINT infoValue = 0;
   returnCode = SQLGetInfo(connectionHandle, SQL_IDENTIFIER_QUOTE_CHAR, &quoteValue, sizeof(quoteValue), &infoValue);
   // quoteValue = L"03.80"
   // infoValue = 10
}

The API call SQLGetInfo() should return the quote character such as a double quote character ("), a single quote character (') etc. Instead it returns 03.80 which is incorrect. This value looks to be the ODBC driver version.

Update: If the parameter SQL_OV_ODBC3_80 in SQLSetEnvAttr() is changed to either SQL_OV_ODBC3 or SQL_OV_ODBC2, then the quote character is correct ("). Looks to be the version specific problem.

Environment Details:

  • IBM DB2 v11.5.0.1077
  • IBM DB2 ODBC Driver v11.5.0.1077 (32-bit)
  • ODBC v3.8
  • VC++ (Visual Studio 2017)
  • Windows 10 64-bit

Any help is much appreciated. Thanks!

Upvotes: 0

Views: 489

Answers (1)

mao
mao

Reputation: 12287

The Db2 V11.5 "sqlext.h" already has SQL_SPEC_MAJOR (3) and SQL_SPEC_MINOR ("80"), if you compiled with that version, meaning it should not be necessary to explicitly set that ODBC version in your code, as that version may be the default with V11.5 despite what the documentation suggests!

The Db2 documentation is inadequate because it has the unqualified remark:

"If you set the SQL_ATTR_ODBC_VERSION attribute to SQL_OV_ODBC3_80 (value 380), CLI returns "03.80""

It appears that the code does return that value even if you are enquiring about SQL_IDENTIFIER_QUOTE_CHAR. I can reproduce that symptom with VS2019 and V11.5 after minor code changes to initialize the TCHAR arrays correctly. That seems like undesirable behaviour to me, but only IBM can decide whether it is defective.

I suggest you open a ticket with IBM Db2 support for that specific question.

As you observed, the workaround is to compile/link with the V11.5 headers/libraries and don't explicitly set the ODBC version with SQL_ATTR_ODBC_VERSION (i.e. omit the line of code returnCode = SQLSetEnvAttr(environmentHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, 0); ). In that case the SQLGetInfo for the quote character returns the correct value.

Upvotes: 1

Related Questions