FreeTDS ODBC + pyodbc是否支持表值参数(tvp)?

Question

我在unix/rhel7系统上工作。我已经安装了FreeTDS, unixODBC和pyodbc的驱动程序。其他查询工作正常，但当我试图执行存储过程与TVP(表值参数)，它给我的错误。是否有任何方法连接SQL Server使用windows服务帐户从python ?

的例子:

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

错误:

pyodbc。错误:('HY004'， '[HY004] [FreeTDS][SQL Server]无效数据类型(0)(SQLBindParameter)')

所以有没有其他方法连接SQL服务帐户从python支持TVP?或者上面的例子有解决方案吗?

Answer 1

FreeTDS ODBC不直接支持这里讨论的表值参数(tvp)。然而，我们可以使用临时表和匿名代码块来解决这个问题。对于用户定义的表类型

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

和接受该表类型

的存储过程

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

我们可以调用存储过程并像这样检索结果:

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, )]