使用UnixODBC与MariaDB时SQLDescribeParam始终返回SQL_VARCHAR问题

在UnixODBC与MariaDB环境中调用SQLDescribeParam()函数时，无论参数对应表列的实际类型是什么，该函数始终返回SQL_VARCHAR类型。例如，表中定义了CHAR(20)类型的USERID列，执行的SELECT语句为：

SELECT * FROM USERS WHERE USERID = ?

测试代码 #

#include <iostream>
#include <sql.h>
#include <sqlext.h>
    
std::string describeNullable(const SQLSMALLINT &value) {
    switch(value) {
    case SQL_NO_NULLS: return "SQL_NO_NULLS";
    case SQL_NULLABLE: return "SQL_NULLABLE";
    case SQL_NULLABLE_UNKNOWN: return "SQL_NULLABLE_UNKNOWN";
    default:
        return std::to_string(value);
    }
}

std::string describeDataType(const SQLSMALLINT &value) {
    switch(value) {
    case SQL_UNKNOWN_TYPE: return "SQL_UNKNOWN_TYPE";
    case SQL_CHAR: return "SQL_CHAR";
    case SQL_NUMERIC: return "SQL_NUMERIC";
    case SQL_DECIMAL: return "SQL_DECIMAL";
    case SQL_INTEGER: return "SQL_INTEGER";
    case SQL_SMALLINT: return "SQL_SMALLINT";
    case SQL_FLOAT: return "SQL_FLOAT";
    case SQL_REAL: return "SQL_REAL";
    case SQL_DOUBLE: return "SQL_DOUBLE";
    case SQL_DATETIME: return "SQL_DATETIME";
    case SQL_VARCHAR: return "SQL_VARCHAR";
    case SQL_TYPE_DATE: return "SQL_TYPE_DATE";
    case SQL_TYPE_TIME: return "SQL_TYPE_TIME";
    case SQL_TYPE_TIMESTAMP: return "SQL_TYPE_TIMESTAMP";
    default:
        return std::to_string(value);
    }
}

int main() {
    // set environement
    SQLHANDLE env;
    auto ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    // get connection
    SQLHANDLE conn;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
    std::string inConnectString{"DRIVER=MariaDB;UID=***;PWD=***;database=clientserver"};
    SQLCHAR* outConnectString = new SQLCHAR[512];
    SQLSMALLINT outStringLen{0};
    ret = SQLDriverConnect(conn,SQL_NULL_HANDLE, reinterpret_cast<SQLCHAR*>(const_cast<char*>(inConnectString.c_str())),
            inConnectString.length(),outConnectString, 512, &outStringLen,SQL_DRIVER_NOPROMPT);
    delete [] outConnectString;
    outConnectString = nullptr;

    // set statement
    SQLHANDLE stmt;
    ret = SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);
    std::string sqlstmt{"SELECT * FROM USERS WHERE USERID = ?"};
    ret = SQLPrepare(stmt,reinterpret_cast<SQLCHAR*>(const_cast<char*>(sqlstmt.c_str())), sqlstmt.length());

    if (SQL_SUCCEEDED(ret)) {
        SQLSMALLINT parmNum{1};
        SQLSMALLINT dataType{0};
        SQLULEN parameterSize{0};
        SQLSMALLINT decimalDigits{0};
        SQLSMALLINT nullable{SQL_NULLABLE};
        ret = SQLDescribeParam(stmt, parmNum, &dataType, &parameterSize,
                 &decimalDigits, &nullable);
        if (SQL_SUCCEEDED(ret)) {
            std::cout << "Parameter: " << parmNum << " Type: " << describeDataType(dataType);
            std::cout << " Size: " << parameterSize << " Decimal digits: " << decimalDigits;
            std::cout << " Nullable: " <<describeNullable(nullable) << std::endl;
        }
    }
}

运行输出 #

Parameter: 1 Type: SQL_VARCHAR Size: 25165824 Decimal digits: 0 Nullable: SQL_NULLABLE_UNKNOWN

预期结果 #

预期返回参数类型为SQL_CHAR，长度为20。

已尝试的操作 #

• 在SQLPrepare()之前调用SQLBindParameter()，返回结果仍相同；
• 修改USERID的数据类型，尝试过char、tinyint、timestamp，始终返回SQL_VARCHAR。

内容的提问来源于stack exchange，提问作者Tom Davidson