Description
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()