pyodbc supports queries with table valued parameters in sql server?

[gabomgp]

Environment

• OS: Windows 64 bits
• DB: Sql Server
• driver: ODBC Driver 13 for SQL Server

Issue

I'm searching for a way to use a table valued parameter when quering a Sql Server 2017 database from python. Is it possible with pyodbc?

[v-chojas]

TVPs require access to descriptor fields (IPD/APD), which pyODBC currently does not support.

[mkleehammer]

An update: @v-chojas and his team has expressed a desire to have pyodbc support this. We don't have a time frame yet.

[mkleehammer]

@v-chojas Thanks for finding the duplicate. Since it has been asked again, can we clarify if you think this is something that should be added and if you and your team are considering working on this? I can't find any notes on this. Otherwise we should update this and close it.

[v-chojas]

@mkleehammer Yes, we were planning on adding TVP and had done some preliminary design and investigation but other things (the attempted merging of fast_executemany, etc.) took priority. Now that the latter seems to have calmed down, we can resume working on TVP.

[glennmschultz]

I am trying to execute a table value function with parameters in python. As follows. The query does not work as pyodbc seems to recognize the parameter values as column names. Not sure if this related to the above. Can I do this with pyodbc?, it seems to me it is nothing more than execution of a query.

sqlquery = select * from getsomething(?, ?)
data = pandas.read_sql_query(sql = sqlquery, con = sqlquery, params = [foo1, foo2])

[v-chojas]

@glennmschultz please open a new issue and provide an ODBC trace. TVF is different from TVP.

[tmandekic]

(almost) a year since the last post: have you made any progress on adding TVPs to pyodbc?

[v-chojas]

It has been implemented: #488

[Lijo-CheeranJoseph]

It has been implemented: #488

Can you please give an example/ sample implementation about how to use it?

[gordthompson]

@Lijo-CheeranJoseph - See the example in the tests.

[sandeepnmenon]

@gordthompson
The example shows the input to the stored proc is only a TVP, what if the input has a normal string variable along with the TVP?

Do I have to duplicate that string along each row in the params?

I am getting this error
Execute sql got error:('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #2: Cannot find data type READONLY. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parameter or variable '@p2' has an invalid data type. (2724)")

where my second parameter is a TVP with a uniqueidentifier and float value

[gordthompson]

@sandeepnmenon

Do I have to duplicate that string along each row in the params?

No.

# a table-valued parameter is a list of tuples
tvp_data = [("4FE34A93-E574-04CC-200A-353F0D1770B1", 1.23),
            ("33F7504C-2BAC-1B83-01D1-7434A7BA6A17", 3.14),]

# parameter values for .execute() are a tuple
params_for_execute = ("my string value", tvp_data)
#                     --------+--------  ----+---
#                             |              |
#           a simple string --+              +-- a list of tuples

crsr.execute("{CALL my_sp (?, ?)}", params_for_execute)

[gordthompson]

@mkleehammer - I think we can close this now.

[sandeepnmenon]

@gordthompson

Still I am getting the same error

sql_connection = pyodbc.connect(connection_string)

mappingvalues = []
mappingvalues.append(("8e4d7360-9a46-ea11-a812-000d3a54419d",0.6))
mappingvalues.append(("ed0ef314-9a46-ea11-a812-000d3a8d88aa",0.6))

params = ("1a1d243c-a587-4d10-995e-cbbf6cad4dce", mappingvalues, 0.5)
sqlCmd = "execute [dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?"

sqlCursor = sql_connection.cursor()
result = sqlCursor.execute(sqlCmd, params)

Have explained this in detail in this issue : #732