TVP support

[skyhook88]

Hi @pacman82, are there any plans to support passing Table valued prams within arrow-odbc? Have been using pyarrow odbc and so far have found full coverage for what I need until now as I can't see passing TVP as been something currently possible.

https://github.com/mkleehammer/pyodbc/wiki/Working-with-Table-Valued-Parameters-(TVPs)

[pacman82]

Note to self: https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-table-valued-parameters/binding-and-data-transfer-of-table-valued-parameters-and-column-values

[pacman82]

Hello @skyhook88 ,

This is a scary feature 😅

• Support for this would have to be build ground up, starting with odbc-api, forwarding it to arrow-odbc (the Rust crate) before it could finally arrive the arrow-odbc-py bindings. If you need a solution right now. This would take a while.
• From what I can tell this seems to be a Microsoft SQL Server only feature.

Does of course not mean that the effort might not be worth it. Could I learn more about your usecase? Do you want to read or write these tables? How big are the tables you intend to pass / receive this way? Is it completely in memory or streamed? Are these tables the payload, or just a bunch of parameters?

Currently this Artifact is all about (and only) connecting Arrow Arrays to ODBC bulk read/writes. It is by no means an abstraction of ODBC or a complete binding of the ODBC API. Intention is just table in/out as arrow over ODBC.

[skyhook88]

Glad you liked it!

The tables been passed in my case are small of a few hundred records - using the feature to passing lists to SQL. it saves having to pass as a string to a SPs and then translate to table variable. Currently these SPs were written to for a an existing dotnet code base so wouldn't want to alter these so will create a functional, ugly and less efficient place holder in pyodbc in the interim :-).

[pacman82]

Is the result of that stored procedure a table? How is arrow connected to all of this?

[skyhook88]

Yes the result that comes back is a table for the passed list - It allows for SQL to have a table passed from the program that the program generated internally and then have SQL process it and return back with the results.