CODEWITHSUNDEEP
pythonsql-serverpyodbc
Robert Criqui
Robert Criqui

Reputation: 218

Is it possible to pass in Table-Valued Parameters to PYODBC query?

I know I can pass a TVP to a stored procedure (as it's a question I've asked here before). I'm wondering now if this is something I can do in Python. I keep on getting this error. It's saying there's an invalid type, so would the parameter need to know what type I'm passing in?

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type READONLY. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Must declare the table variable "@P1". (1087); [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 '@P1' has an invalid data type. (2724)')

from src.data.dbaccess import con

queryString2 = """ 

SET NOCOUNT ON

DECLARE @t As Table(
PersonName VARCHAR(50)
)

INSERT INTO @t
SELECT *
FROM ?

SELECT * FROM @t
"""

data = ([('Jim', ), ('Bob', )],)

with pyodbc.connect(con.conString) as testCon:
    crsr = testCon.execute(queryString2, data)
    for row in crsr:
        print(row)

** Edit to clarify. In my question last year, I asked if a I could pass a TVP to a SQL Server stored procedure. This time, the procedure is being natively my Python code. This is the issue I'm trying to address now.

Upvotes: 2

Views: 2971

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123559

As mentioned in a comment to the question, it is possible to pass a TVP to an anonymous code block from C# using System.Data.SqlClient like so:

var tvpData = new DataTable();
tvpData.Columns.Add(new DataColumn("id", Type.GetType("System.Int32")));
tvpData.Rows.Add(new object[] { 1 });
tvpData.Rows.Add(new object[] { 2 });

using (var con = new SqlConnection(@"Server=127.0.0.1,49242;Database=mydb;Trusted_Connection=True;"))
{
    con.Open();
    using (var cmd = new SqlCommand("SELECT * FROM @p1", con))
    {
        SqlParameter tvpParam = cmd.Parameters.AddWithValue("@p1", tvpData);
        tvpParam.SqlDbType = SqlDbType.Structured;
        tvpParam.TypeName = "dbo.dboListInt";  // an existing user-defined table type

        SqlDataReader rdr = cmd.ExecuteReader();
        Console.WriteLine("rows returned:");
        while (rdr.Read())
        {
            Console.WriteLine(rdr[0]);
        }
    }
}

console output:

rows returned:
1
2

However, that won't work with an ODBC connection from C# using System.Data.Odbc because the code relies on the SqlClient-specific SqlDbType.Structured type.

pyodbc does not have a similar mechanism for specifying a "structured" (TVP) parameter type. Instead, it deduces the presence of a table-valued parameter by the "shape" of the parameters it receives (e.g., a row represented by a tuple of elements, one of which is itself a list of tuples).

Furthermore, the conversation between pyodbc and the ODBC driver currently does not fully account for user-defined types as parameters for anything other than a normal stored procedure call. (It would be nice if we could use a temporary stored procedure — CREATE PROCEDURE #myTempSP … — but testing indicates that temporary stored procedures cannot work with user-defined table types.)

TL;DR — TVPs can be passed to an anonymous code block from C# but (currently) not from Python via pyodbc.

Upvotes: 1

Related Questions