'[HY004] [Microsoft][ODBC Driver 17 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)' When first row of TVP contains None as a cell value

[hjb417]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.8
• pyodbc: 4.0.30
• OS: Windows 10 64-bit
• DB: SQL Server 2017
• driver: ODBC Driver 17 for SQL Server
• ODBC Log: SQL.LOG

Issue

The below code fails because the 1st row of param_array contains None. If you swap the rows, the error goes away.. So it seems that TVP will only work when the 1st row contains all non-None values... All other rows can contain None.

It's similar to #520

param_array = [
    [None, 0],
    [1, 1],
    ]    
conn_str = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;UID=sa;DATABASE=hasani"
    
pyodbc.connect(conn_str).cursor().execute("EXEC TestSelectTVP ?", [param_array]).fetchall()

CREATE TYPE [dbo].[TVPType] AS TABLE(
	[c01] INT NULL,
	[c02] INT NULL
)
GO


CREATE PROCEDURE dbo.TestSelectTVP
(
	@TVP dbo.TVPType READONLY
)
AS
BEGIN
SELECT * FROM @TVP;
END

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

[v-chojas]

In general, Nones are a bit problematic since they don't provide any hints of the type that the parameter should be sent as; when the parameter is not a TVP, then the SQLDescribeParam function is used to determine the type, but when it is a TVP, SQLDescribeParam only says that the parameter is a TVP, and does not provide information about its individual columns.

If the parameter is not None, then it can be used to determine the type, which is why it works when the first row doesn't have Nones --- pyODBC uses the contents of that row to determine what types it should bind each of the sub-parameters in the TVP as.

[hjb417]

Hi Chojas,
Thanks for the prompt reply. Is it possible pyodbc can internally retrieve types when the 1st row contains None, or let the client/caller specify or register the types. Also, it may be unreliable to use the value to determine the type because it's not guaranteed to be the same across rows. (E.x.: The first row can contain all strings and the subsequent row can contain an mix of strings and floats strings for a TVP that has all FLOAT columns and this is because SQL server is doing an implicit conversion of the data.

I did try to take a stab at fixing it yesterday. I was able to compile the code and create a python test to replicate it but the ODBC protocol was too much to grok for me... It was my first time dabbling in the ODBC API.

[hjb417]

For the 2nd idea, maybe pyodbc can provide a class we can optionally use to send data to pyodbc..

E.x.:

from dataclasses import dataclass

@dataclass
class TableValueParameter:
    column_types: list[type] # the python type that to map the the TVP column
    rows: # the sequence that we normally send to pyodbc for a TVP

[gordthompson]

If having pyodbc auto-detect the TVP types is not feasible (as suggested by my research into a related issue indicating that all pyodbc can get from SQL Server itself is that the ParameterType is SQL_SS_TABLE (-153), and the ValueType is SQL_C_BINARY) then perhaps the "let the client/caller specify or register the types" could be accomplished by an extension to .setinputsizes where one of the tuple elements in the outer list of tuples (for each parameter) could itself be a list of tuples (for each column in the TVP).

[hjb417]

I give up... I tried using TVP in AWS Lambda and pyodbc caused a core dump. I'll just upload JSON strings instead of TVP.

[ElhamNobakht]

I have Error from this code:
def new_insert_to_db(item):
mydb = pyodbc.connect('Driver={SQL Server};'
'Server=.;'
'Database=dsdb;'
'Trusted_Connection=yes;')
mycursor = mydb.cursor()
sql = ('INSERT INTO dbo.Invert Index (Filename,Word) VALUES (?,?,)')
for i in item:
tmp = i
print(tmp)
val = (tmp[0], tmp[1])
mycursor.execute(sql,val)
mydb.commit()
Error: ('HY004', '[HY004] [Microsoft][ODBC SQL Server Driver]Invalid SQL data type (0) (SQLBindParameter)')

[v-chojas]

"SQL Server " is a very old driver from SQL Server 6.x, which obviously did not support TVP.
Try a newer ODBC driver, like ODBC Driver 17 for SQL Server: https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server

[gordthompson]

Closing because

1. this is a limitation of parameter type discovery for TVPs, and
2. the workaround is to use OPENJSON as below.

This fails

param_array = [
    [None, 0],
    [1, 1],
]

cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()

crsr.execute("EXEC TestSelectTVP ?", [param_array]).fetchall()

while this works

param_json = [dict(c01=x, c02=y) for x, y in param_array]
print(param_json)
# [{'c01': None, 'c02': 0}, {'c01': 1, 'c02': 1}]

sql = """\
SET NOCOUNT ON;
DECLARE @tvp [dbo].[TVPType];
INSERT INTO @tvp
SELECT c01, c02 
FROM OPENJSON(?)
WITH (
    c01 int '$.c01',
    c02 int '$.c02'
);
EXEC [dbo].[TestSelectTVP] @tvp;
"""
crsr.execute(sql, json.dumps(param_json, default=str))
print(crsr.fetchall())
# [(None, 0), (1, 1)]