Type mismatch for None/NULL value in a cell in a table-valued parameter.

[mhmurray]

Sys info

• python: 3.6.1 |Anaconda custom (64-bit)| (default, May 11 2017, 13:09:58)
  [GCC 4.4.7 20120313 (Red Hat 4.4.7-1)]

• pyodbc: 4.0.26 [...]/lib/python3.6/site-packages/pyodbc.cpython-36m-x86_64-linux-gnu.so

• odbc: 03.52

• driver: libmsodbcsql-17.3.so.1.1 17.03.0001
  supports ODBC version 03.52

• os: Ubuntu 16.04

• DB: Azure SQL Server

Issue

When using a table-valued parameter (TVP) for a simple stored procedure that echos the input, None values in the TVP cells cause a pyodbc error because of a type mismatch with the parameter in the previous row.

Stored procedure:
CREATE PROCEDURE TestSelectTVP @TVP TVPType READONLY AS SELECT * FROM @TVP;

TVP Type:
CREATE TYPE TVPType AS TABLE(c01 BIGINT, c02 BIGINT)

Call the SP:

param_array = [
        [1, 0],
        [None, 1],
        ]
cursor.execute("EXEC TestSelectTVP ?", [param_array])

Expected

Returned values:

c0   | c1
-----|-----
1    | 0
NULL | 1

Observed

Exception raised:

Traceback (most recent call last):
  File "test_pyodbc.py", line 144, in <module>
    main()
  File "test_pyodbc.py", line 139, in main
    run_tvp(conn)
  File "test_pyodbc.py", line 103, in run_tvp
    cursor.execute("EXEC TestSelectTVP ?", [param_array])
pyodbc.ProgrammingError: ('Type mismatch between TVP row values', 'HY000')

If None/NULL is in the first row, there is a different exception:
pyodbc.Error: ('HY090', '[HY090] [unixODBC][Driver Manager]Invalid string or buffer length (0) (SQLBindParameter)')

Other info

The exception is raised here:
https://github.com/mkleehammer/pyodbc/blob/4.0.26/src/cursor.cpp#L766

Doesn't this type check require non-NULL data, since it's testing strict type equality?

Script to reproduce:

import pyodbc
import sys
import os
import platform


CONNECTION_STR=''

def reset_tvp(conn):
    """
    Reset stored procedures and table types associated with the TVP method.
    """
    try:
        conn.execute("DROP PROCEDURE TestSelectTVP")
    except:
        pass

    try:
        conn.execute("DROP TYPE TVPType")
    except:
        pass



def run_tvp(conn):
    conn.execute("DROP TYPE IF EXISTS TVPType")
    cursor = conn.cursor()

    query = (
            "CREATE TYPE TVPType AS TABLE("
            "c01 BIGINT,"
            "c02 BIGINT)"
            )

    conn.execute(query)
    conn.commit()
    conn.execute("CREATE PROCEDURE TestSelectTVP @TVP TVPType READONLY AS SELECT * FROM @TVP;")
    conn.commit()

    # NULL doesn't match parameter type or value type of previous row.
    param_array = [
            [1, 0],
            [None, 1],
            ]

    # Initial NULL causes "Invalid string or buffer length (0) (SQLBindParameter)"
    param_array_initial_null = [
            [None, 1],
            ]

    cursor.execute("EXEC TestSelectTVP ?", [param_array])
    # cursor.execute("EXEC TestSelectTVP ?", [param_array_initial_null])
    conn.commit()


def print_library_info(cnxn):
    # from pyodbc.tests2.testutils.py
    print('python:  %s' % sys.version)
    print('pyodbc:  %s %s' % (pyodbc.version, os.path.abspath(pyodbc.__file__)))
    print('odbc:    %s' % cnxn.getinfo(pyodbc.SQL_ODBC_VER))
    print('driver:  %s %s' % (cnxn.getinfo(pyodbc.SQL_DRIVER_NAME), cnxn.getinfo(pyodbc.SQL_DRIVER_VER)))
    print('         supports ODBC version %s' % cnxn.getinfo(pyodbc.SQL_DRIVER_ODBC_VER))
    print('os:      %s' % platform.system())
    print('unicode: Py_Unicode=%s SQLWCHAR=%s' % (pyodbc.UNICODE_SIZE, pyodbc.SQLWCHAR_SIZE))

    cursor = cnxn.cursor()
    for typename in ['VARCHAR', 'WVARCHAR', 'BINARY']:
        t = getattr(pyodbc, 'SQL_' + typename)
        cursor.getTypeInfo(t)
        row = cursor.fetchone()
        print('Max %s = %s' % (typename, row and row[2] or '(not supported)'))

    if platform.system() == 'Windows':
        print('         %s' % ' '.join([s for s in platform.win32_ver() if s]))


def main():
    conn = pyodbc.connect(CONNECTION_STR)
    print_library_info(conn)
    conn.close()

    conn = pyodbc.connect(CONNECTION_STR)
    
    reset_tvp(conn)
    run_tvp(conn)



if __name__ == '__main__':
    main()

[v-makouz]

I can reproduce the issue, and I'm looking into fixing it

[v-makouz]

I made a PR that seems to work for both this repro app and doesn't break any existing tests (including TVP). It basically skips type checking for NULLs.

One "catch" is that the first line still has to have all the proper datatypes, because otherwise there is no way to know what the types are, but that's true regardless of my fix (I think you'd get an error trying to bind SQL_C_DEFAULT if you have a None in the first line)

[mkleehammer]

I'm assuming that the PR fixes this? Should we add "Fixes #xxx" to the PR commits so they close issues automatically?

If I'm wrong, please reopen.

[githubuserkkk]

I am still able to reproduce the issue if the first row is having None.
getting this error "('HY004', '[HY004] [Microsoft][SQL Server Native Client 11.0]Invalid SQL data type (0) (SQLBindParameter)"

[gordthompson]

A workaround for SQL Server 2016+ is documented here:

#1229 (comment)