Implemented support for the SQL Server sql_variant data type

[will-hinson]

This commit adds support for decoding the SQL Server sql_variant data type.

Attempting to read records containing sql_variant columns in the current version of pyodbc (5.1.0) results in the following exception:

pyodbc.ProgrammingError: ('ODBC SQL type -150 is not yet supported.  column-index=0  type=-150', 'HY106')

As mentioned in #307, it is not trivial to write a custom output converter for sql_variant as the bytes returned by SQL Server give no indication as to the underlying data type of each value.

This commit adds a GetData_SqlVariant() method which retrieves the underlying data type per the Microsoft documentation using SQLColAttribute():

// Call SQLGetData on the current column with a data length of 0. According to MS, this makes
// the ODBC driver read the sql_variant header which contains the underlying data type
pBuff = 0;
indicator = 0;
retcode = SQLGetData(cur->hstmt, static_cast<SQLSMALLINT>(iCol + 1), SQL_C_BINARY,   
                                &pBuff, 0, &indicator);
if (!SQL_SUCCEEDED(retcode))
    return RaiseErrorFromHandle(cur->cnxn, "SQLGetData", cur->cnxn->hdbc, cur->hstmt);

// Get the SQL_CA_SS_VARIANT_TYPE field for the column which will contain the underlying data type
variantType = 0;
retcode = SQLColAttribute(cur->hstmt, iCol + 1, SQL_CA_SS_VARIANT_TYPE, NULL, 0, NULL, &variantType);
if (!SQL_SUCCEEDED(retcode))
    return RaiseErrorFromHandle(cur->cnxn, "SQLColAttribute", cur->cnxn->hdbc, cur->hstmt);

This underlying data type is then patched into the ColumnInfo struct for the current column and GetData() is invoked using this new type:

// Replace the original SQL_VARIANT data type with the underlying data type then call GetData() again
cur->colinfos[iCol].sql_type = static_cast<SQLSMALLINT>(variantType);
return GetData(cur, iCol);

The sql_variant type is used by a number of system functions and views and this commit adds support for reading them:

import pyodbc

connection = pyodbc.connect(...)
print(
    connection.execute(
        """
        SELECT
            SERVERPROPERTY('MachineName'),
            SERVERPROPERTY('InstanceName'),
            SERVERPROPERTY('Edition'),
            SERVERPROPERTY('ProductVersion'),
            SERVERPROPERTY('ProductLevel');
        """
    ).fetchone()
)

# ('acfd83beaa19', '', 'Developer Edition (64-bit)', '16.0.4003.1', 'RTM')

[gordthompson]

Hey, Will. This looks promising. Thanks for contributing!

Can we add a test to tests/sqlserver_test.py that will exercise your code a bit?

[will-hinson]

Hello @gordthompson,

Of course! I have pushed another commit with an added test for the sql_variant type.

I also made the following related changes:

• Added a couple lines to reset the sql_type of the ColumnInfo struct to SQL_SS_VARIANT after decoding. Without this, all values in the column would try to take on the type of the first value returned.
• Added cases for data type values that can be returned for sql_variant columns and are defined in sqlext.h. (See this MS doc)

Please let me know if further action is required on my part. Thanks!

[gordthompson]

Comments @mkleehammer @v-chojas ?

[v-chojas]

Not sure how much DBMS-specific stuff you want in pyODBC.