SQLColumns() gives incorrect data types for char and varchar.

Question

Summary:

In version 8.0.28, ODBC for MySQL switched from using the deprecated COM_FIELDS_LIST by default to the INFORMATION_SCHEMA table for answering SQLColumns and other table metadata queries. This can be disabled using the driver's NO_I_IS metadata option in ODBC Administrator, but that option is slated for removal in 8.0.31.

Using the INFORMATION_SCHEMA for metadata queries causes the Microsoft SQL API to return incorrect values for char and varchar data types (-8 and -9 respectively, instead of 1 and 12 as defined in sqlext.h) when SQLColumns() is called. Enabling the NO_I_IS option causes the issue to go away, but cannot act as a permanent solution for people wishing to use the latest ODBC driver.

The below example and attached log files indicate this is an issue with the Microsoft SQL API, rather than MySQL or ODBC. Software I work on relies on SQLColumns() for its database connectivity features, so a solution to this issue would be much appreciated.

----------

Example

In the below sample program which demonstrates the issue (stripped down and slightly modified from a program provided here), we retrieve information on a MySQL table named "testcolumntypes" in some MySQL database via an ODBC driver named "MySQL8030". This is an empty table hosted with MySQL version 8.0.30 (also tested with MySQL version 8.0.20), with columns of various data types (in my case, the columns have types char, varchar, mediumtext, char, varchar, longtext, decimal, decimal, smallint, int, double, float, double, and bit).

// SQLColumns_Function.cpp    
// compile with: ODBC32.lib    
#include <windows.h>    
#include <sqlext.h>    
#include <stdio.h>  
#include <string.h>  
#include <stdbool.h>  
#include <assert.h>  
#define STR_LEN 128 + 1    
#define REM_LEN 254 + 1    
  
// Declare buffers for result set data    
SQLCHAR szSchema[STR_LEN];  
SQLCHAR szCatalog[STR_LEN];  
SQLCHAR szColumnName[STR_LEN];  
SQLCHAR szTableName[STR_LEN];  
SQLCHAR szTypeName[STR_LEN];  
  
SQLINTEGER OrdinalPosition;  
  
SQLSMALLINT DataType;  
SQLSMALLINT SQLDataType;  
  
SQLHSTMT hstmt = NULL;  
  
// Declare buffers for bytes available to return    
SQLINTEGER cbCatalog;  
SQLINTEGER cbSchema;  
SQLINTEGER cbTableName;  
SQLINTEGER cbColumnName;  
SQLINTEGER cbDataType;  
SQLINTEGER cbTypeName;  
SQLINTEGER cbSQLDataType;  
SQLINTEGER cbOrdinalPosition;  
  
int main() {  
    SQLHENV henv;  
    SQLHDBC hdbc;  
    SQLHSTMT hstmt = 0;  
    SQLRETURN retcode;  
  
    char* pdsn = "MySQL8030";  
    char* puid = "root";  
    char* ppwd = "databasepassword";  
    char* tablename = "testcolumntypes";  
  
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC2, 0);  
    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);  
    retcode = SQLConnect(hdbc, pdsn, SQL_NTS, puid, SQL_NTS, ppwd, SQL_NTS);  
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);  
    retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, tablename, SQL_NTS, NULL, 0);  
  
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
        // Bind columns in result set to buffers    
        SQLBindCol(hstmt, 1, SQL_C_CHAR, szCatalog, STR_LEN, &cbCatalog);  
        SQLBindCol(hstmt, 2, SQL_C_CHAR, szSchema, STR_LEN, &cbSchema);  
        SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName, STR_LEN, &cbTableName);  
        SQLBindCol(hstmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);  
        SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);  
        retcode = SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);  
        SQLBindCol(hstmt, 14, SQL_C_SSHORT, &SQLDataType, 0, &cbSQLDataType);  
        SQLBindCol(hstmt, 17, SQL_C_SLONG, &OrdinalPosition, 0, &cbOrdinalPosition);  
          
        while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {  
            retcode = SQLFetch(hstmt);  
  
            printf("Column %d: %s : %d\n", OrdinalPosition, szTypeName, DataType);  
        }  
    }  
  
    return 0;  
}  

ACTUAL Behaviour:

This program gives the following output running on ODBC for MySQL version 8.0.28 onwards with NO_I_IS disabled:

Column 1: char(5) : -8  
Column 2: varchar(45) : -9  
Column 3: mediumtext : -1  
Column 4: char(45) : -8  
Column 5: varchar(45) : -9  
Column 6: longtext : -1  
Column 7: decimal(10,0) : 3  
Column 8: decimal(10,0) : 3  
Column 9: smallint : 5  
Column 10: int : 4  
Column 11: double : 8  
Column 12: float : 7  
Column 13: double : 8  
Column 14: bit(1) : -7  

EXPECTED Behaviour:

Running on ODBC for MySQL version 8.0.27 and earlier, or 8.0.28 onwards with NO_I_IS enabled:

Column 1: char : 1
Column 2: varchar : 12
Column 3: mediumtext : -1
Column 4: char : 1
Column 5: varchar : 12
Column 6: longtext : -1
Column 7: decimal : 3
Column 8: decimal : 3
Column 9: smallint : 5
Column 10: integer : 4
Column 11: double : 8
Column 12: float : 7
Column 13: double : 8
Column 14: bit : -7

----------

Additional Information

Enabling debug logging for the ODBC driver produces the following output when NO_I_IS is enabled:

-- Query logging  
--  
--  Driver name: MySQL ODBC 8.0 Driver  Version: 08.00.0028  
-- Timestamp: 221012  9:59:30  
  
1665565170:SHOW TABLE STATUS LIKE 'testcolumntypes';  

It produces no output at all when NO_I_IS is disabled.

--

Enabling tracing via ODBC gives identical log files. These are attached - 249723-sql-no-i-is-disabled.log and 249742-sql-no-i-is-enabled.log. The only difference is when NO_I_IS is enabled it gives a SUCCESS_WITH_INFO when calling SQLColumns, warning that NO_I_IS is deprecated.

--

The MySQL server logs are attached - 249743-mysql-log-no-i-is-disabled.log and 249751-mysql-log-no-i-is-enabled.log. Manually executing the query given when NO_I_IS is disabled appears to return correct information on the table. A screenshot of this query's output is also attached - .