Deciphering SQL_VARIANT values

[gordthompson]

A question on Stack Overflow regarding pyodbc and SQL_VARIANT got me curious to see if an output converter function might be feasible. I confirmed that, without an output converter function, pyodbc would throw

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

when I tried to retrieve a SQL_VARIANT column. When I added a little function to simply return the bytes (so I could see what we had to work with) ...

import pyodbc  # 4.0.21


def return_bytes(the_bytes):
    return the_bytes


conn_str = (
    r'DRIVER=ODBC Driver 11 for SQL Server;'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()

crsr.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, x SQL_VARIANT)")
crsr.execute("INSERT INTO #tmp (id, x) VALUES (1, 'Gord')")
crsr.execute("INSERT INTO #tmp (id, x) VALUES (2, 1685221191)")

cnxn.add_output_converter(-150, return_bytes)
for row in crsr.execute("SELECT x FROM #tmp ORDER BY id"):
    print(row.x)

crsr.close()
cnxn.close()

... I found that just the data bytes were returned, without the metadata to describe what type of value it was. That is, under Python 3.6 the above code prints

b'Gord'
b'Gord'

so we apparently cannot differentiate between the values in the two rows: 'Gord' and 1685221191.

The Microsoft document Mapping Data Types (ODBC) suggests that there is a way to get that information (SQL_CA_SS_VARIANT_TYPE) so perhaps pyodbc can make it available to the output converter function.

[LeoQuote]

I have the same issue when trying to select data structure from database.

[jcfernandez-890825]

I also have problem when I tried to do a SELECT over a SQL_VARIANT Column. I'm using Ubuntu 18.04, python3.6, pyodbc 4.0.27, freetds 1.00.82-2, unixodbc 2.3.7. I have tried both [Microsoft ODBC Driver 17] and [FreeTDS] individually.

Using TDS version 4.2 I can make the SELECT, but I can't use v4.2 with Django 2.2 because it complains with Datetime Fields.

By using pyodbc.connect(...).add_output_converter(-150, sql_variant_handler), where sql_variant_handler is a function I'm able to retrieve the SQL_VARIANT value, but such value is a encrypted somehow. I'm currently trying to find a way to get the real data (integer values) to no avail.

[gordthompson]

@jcfernandez-890825 - If your output converter function is receiving four bytes and you want to convert them to an int then try struct.unpack('i', the_bytes), e.g.,

>>> struct.unpack('i', b'Gord')
(1685221191,)

[ghost]

I know it won't be ideal for all use cases but if your workload is not intensive or overly complex and you're trying to retrieve data in SQL Server from something like a system property where the value is of type sql_variant, I had luck doing the type conversion in the SQL statement so that when it was returned to pyodbc it was already in a compatible type:

            cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
            cursor = cnxn.cursor()
            cursor.execute("SELECT CONVERT(VARCHAR(MAX), SERVERPROPERTY('InstanceDefaultDataPath'), 2)")
            datapath = cursor.fetchone()
            datapath = str(pathlib.PurePath(datapath[0]))

[hummingbird1989]

@gordthompson Do you know if this request was ever worked on?

[gordthompson]

@hummingbird1989 - Not that I'm aware of.

[v-chojas]

That's because pyODBC Is generic and variant types are specific to SQL Server, however you can obtain the base type of a variant using SQL: https://learn.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql