Docs (wiki): Mention TVP support, or lack thereof #319
Description
I see that TVP support is on the TODO list.
At the moment, the wiki does not mention TVPs at all. You might want to at least note them, even if only to say that the old pyodbc method doesn't work …
""" Existing environment:
CREATE TYPE issue_1229_table_type AS TABLE (
id int NOT NULL,
txt nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY (id)
);
GO
CREATE PROCEDURE issue_1229_sp
@tvp dbo.issue_1229_table_type READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM @tvp;
END;
GO
"""
tvp_data = [(1, "Alfa"), (2, "Bravo")]
results = crsr.execute("EXEC issue_1229_sp ?", (tvp_data,)).fetchall()
# TypeError: Unsupported parameter type: The driver cannot safely convert it to a SQL type.… but this workaround succeeds
tvp_data = [(1, "Alfa"), (2, "Bravo")]
tvp_json = [dict(zip(["id", "txt"], row)) for row in tvp_data]
sql = """\
SET NOCOUNT ON;
DECLARE @tvp dbo.issue_1229_table_type;
INSERT INTO @tvp (id, txt)
SELECT id, txt FROM OPENJSON(?)
WITH (
id int '$.id',
txt nvarchar(50) '$.txt'
);
EXEC issue_1229_sp @tvp
"""
results = crsr.execute(sql, json.dumps(tvp_json, default=str)).fetchall()
print(results)
# [(1, 'Alfa'), (2, 'Bravo')]