Does FreeTDS ODBC + pyodbc support table-valued parameters (TVPs)?

Question

I'm working on unix/rhel7 system. I have installed require drivers for FreeTDS, unixODBC and pyodbc.Other query is working fine but when I'm trying execute stored proc with TVP (table valued parameter), its giving me error. Is there any way to connect SQL Server using windows service account from python?

Example:

import pyodbc;

cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER=SERVERNAME;PORT=1234;UID=USERNAME;PWD=PASSWORD;DATABASE=DBNAME')
cnxn.cursor()


param_array = []
for i in range(3):
  param_array.append(['abc', 'adi', '/somepath/', '2021-01-04', 'NEW'])

result_array = cursor.execute("EXEC abc.stored_proc_name ?", [param_array]).fetchall()
cursor.commit()

cnxn.close()

Error:

pyodbc.Error: ('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)')

So Is there any other way to connect SQL service account from python which supports TVP? Or Is there any solution in above example?

I'm pretty sure that FreeTDS ODBC does not support TVPs. But Microsoft's "ODBC Driver 17 for SQL Server" does, and it is available for RHEL, so you can use that instead. — Gord Thompson, Feb 17 '21 at 21:22
does "ODBC Driver 17 for SQL Server" requires to install Kerberos? or is there any other way to do it? — user11853415, Feb 18 '21 at 16:34
As far as I know if the SQL Server instance does not support SQL logins (just "Windows authentication") and the client is on Linux then yes, "ODBC Driver 17 for SQL Server" does require Kerberos. — Gord Thompson, Feb 18 '21 at 17:14

score 1 · Answer 1 · answered Feb 18 '21 at 17:57

FreeTDS ODBC does not directly support table-valued parameters (TVPs) as discussed here. However we can use a temporary table and an anonymous code block to work around the issue. For a user-defined table type

USE [myDb]
GO

/****** Object:  UserDefinedTableType [dbo].[dboListInt]    Script Date: 2021-02-18 10:53:17 ******/
CREATE TYPE [dbo].[dboListInt] AS TABLE(
    [Id] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

and a stored procedure that accepts that table type

USE [myDb]
GO
/****** Object:  StoredProcedure [dbo].[dboPyOdbcTestTvp]    Script Date: 2021-02-18 10:41:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[dboPyOdbcTestTvp](@tvp [dbo].dboListInt READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

we can call the stored procedure and retrieve the results like so:

import pyodbc

cnxn = pyodbc.connect(
    "DRIVER=FreeTDS_1.2.18;"
    "SERVER=192.168.0.179;"
    "PORT=49242;"
    "DATABASE=myDb;"
    "UID=sa;PWD=_whatever_;"
)
crsr = cnxn.cursor()
crsr.execute("CREATE TABLE #tvp_data (Id int)")
tvp_data = [(123, ), (234, ), (345, )]
crsr.executemany(
    "INSERT INTO #tvp_data (Id) VALUES (?)",
    tvp_data
)
crsr.execute("""\
SET NOCOUNT ON;
DECLARE @tvp dbo.dboListInt;
INSERT INTO @tvp (Id)
SELECT Id FROM #tvp_data;
EXEC dbo.dboPyOdbcTestTvp @tvp;
""")
print(crsr.fetchall())
# [(123, ), (234, ), (345, )]

FreeTDS 1.3.74 and later do support TVP, here's the PR which added it: https://github.com/FreeTDS/freetds/pull/465 — John Zwinck, Sep 07 '22 at 15:10

Does FreeTDS ODBC + pyodbc support table-valued parameters (TVPs)?

1 Answers1