Stored Procedure with TVP input Returns Error Despite Schema and Type name in the Parameters

Question

Stored Procedure with TVP input Returns Error Despite Schema and Type name in the Parameters

Closed this issue 3 months ago · 10 comments

Hi,

There seem to be an issue with TVP as input for stored procedure in pyodbc, despite adding the type and schema names in the parameter list as described in the documentation :https://github.com/mkleehammer/pyodbc/wiki/Working-with-Table-Valued-Parameters-(TVPs)

Here is my code :

def update_permit():
    records_list = ["type_permit", "geoscience"] + df_to_records(select_permits)
    query = f"""
    EXECUTE GEODB.geoscience.usp_update_permit @permit_source_table = ?;
    """
    response = write_query("GEODB", query, (records_list, ))
    return response

And here is a sample of data as it is presented from the "(records_list, )' part of the code:

(['type_permit', 'geoscience', ('378', 'FOUKANDA II'), ('232', 'TCHENDO'), ('112', 'MARINE IV'), ('353', 'PNGF bis'), ('230', 'TCHIBELI-LITANZI'), ('376', 'AWA-PALOUKOU'), ('156', 'LIKOUALA MARINE'), ('379', 'IKALOU / IKALOU SUD'), ('305', 'Marine 28'), ('25', 'YOMBO-MASSEKO-YOUBI'), ('377', 'DJAMBALA II'), ('231', 'TCHIBOUELA'), ('53', 'EMERAUDE'), ('380', 'MWAFI II'), ('306', 'KOMBI-LIKALALA-LIBONDO'), ('320', 'Boatou'), ('386', 'YANGA-SENDJI')],)

Yet, I get this error:

 ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type geoscience.type_permit. (2715) (SQLExecDirectW); [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)")

Any suggestions please?
I am using pyodbc version 5.1.0 and Python version 3.

Answer 1 · 2024-09-04T17:39:29.000Z

Here is the type table definition:

USE GEODB;

GO

DROP TYPE IF EXISTS geoscience.type_permit;

GO

CREATE TYPE geoscience.type_permit AS TABLE(
	id_permit INT PRIMARY KEY,
	permit NVARCHAR(100)
)

And here is the stored procedure:

USE GEODB;

GO

DROP PROCEDURE IF EXISTS geoscience.usp_update_permit;

GO

CREATE OR ALTER PROCEDURE geoscience.usp_update_permit
@permit_source_table geoscience.type_permit READONLY
AS
BEGIN
	SET NOCOUNT ON
	MERGE geoscience.permit AS tgt
	USING @permit_source_table AS src
	ON src.id_permit = tgt.id_permit
	WHEN MATCHED
	THEN
		UPDATE
		SET
			tgt.permit = src.permit
	WHEN NOT MATCHED BY TARGET
	THEN
		INSERT (id_permit, permit)
		VALUES (src.id_permit, src.permit)
	WHEN NOT MATCHED BY SOURCE
	THEN
		DELETE;
END;

Answer 2 · 2024-09-04T17:42:37.000Z

Finally, here is my connection and cursor handling script:

from pyodbc import connect, Error
import pandas as pd
import numpy as np

connection_strings = {
	"GEODB" : {
		"SERVER" : <hidden>,
		"DATABASE" : "GEODB",
		"UID" : <hidden>,
		"DSN" : "GEODB",
		"DRIVER" : "ODBC Driver 17 for SQL Server"
    },
}
def get_connection(database):
	driver = connection_strings[database].get("DRIVER", "")
	server = connection_strings[database].get("SERVER", "")
	user = connection_strings[database].get("UID", "")
	pwd = connection_strings[database].get("PWD", "")
	dsn = connection_strings[database].get("DSN", "")
	
	try:
		connection = connect(f"DRIVER={driver}; SERVER={server}; database = {database}; DSN={dsn}; Trusted_Connection=Yes")
		if pwd:
			connection = connect(f"DRIVER={driver}; SERVER={server}; database = {database}; UID={user}; PWD = {pwd}; DSN={dsn}; Trusted_Connection=Yes")
		return connection
	except Error as err:
		return err

def handle_query_connections(query_executing_function):
	def wrapper(database, query_statement, insert_records = None):
		connection = None #Initialize connection to none to be able to close it in case of failure in the try and except block.
		try:
			connection = get_connection(database)
			cursor = connection.cursor()
			#cursor.fast_executemany = True

			query_result = query_executing_function(database, cursor, query_statement, insert_records)
			
			cursor.close()
			connection.commit()
			connection.close()
			return query_result
		except Error as e:
			if connection:
				connection.close() #Close connection if not closed in the try block after an error.
			return e
	return wrapper

@handle_query_connections
def write_query(database, cursor, query_statement, insert_records):
	try:
		if insert_records is None:
			cursor.execute(query_statement)
		else:
			cursor.execute(query_statement, insert_records)
		return "Success"
	except Error as err:
		return str(err)

Answer 3 · 2024-09-04T17:58:20.000Z

EXECUTE GEODB.geoscience.usp_update_permit

What is the current database when that statement is executed? UDTs cannot be used across databases.

Answer 4 · 2024-09-04T18:23:33.000Z

EXECUTE GEODB.geoscience.usp_update_permit

What is the current database when that statement is executed? UDTs cannot be used across databases.

"GEODB". That's also specified in the connection string.

Answer 5 · 2024-09-05T08:07:02.000Z

As a proof that the problem is not comming from the database, when I run the following from Python, it works as expected:

def update_permit():
    query = f"""
    USE GEODB;
    DECLARE @table_example AS geoscience.type_permit;
    INSERT INTO @table_example VALUES (10, 'Test3'), (11, 'Test4');
    EXECUTE geoscience.usp_update_permit @table_example;
    """
    response = write_query("GEODB", query)
    return response

Returns : Success, and the data is indeed inserted into the permit table.
The issue then, I believe, is with the way pyodbc handles the query.

Answer 6 · 2024-09-05T14:42:13.000Z

Have you tried without specifying the type of the TVP parameter?

Answer 7 · 2024-09-05T15:05:05.000Z

Have you tried without specifying the type of the TVP parameter?

Yes,
I tried all the following:

def update_permit():
    records_list = df_to_records(select_permits)
    query = f"""
    EXECUTE GEODB.geoscience.usp_update_permit @permit_source_table = ?;
    """
    response = write_query("GEODB", query, (records_list, ))
    return response

Returns same error as with the addition of type and schema to the list.

def update_permit():
    records_list = ["type_permit", "geoscience", "GEODB"] + df_to_records(select_permits)
    query = f"""
    EXECUTE GEODB.geoscience.usp_update_permit @permit_source_table = ?;
    """
    response = write_query("GEODB", query, (records_list, ))
    return response

Does not work either.

def update_permit():
    records_list = ["GEODB.geoscience.type_permit"] + df_to_records(select_permits)
    query = f"""
    EXECUTE GEODB.geoscience.usp_update_permit @permit_source_table = ?;
    """
    response = write_query("GEODB", query, (records_list, ))
    return response

Does noot work.

I also tried using only the type or only the schema in the brackets of the records_list but not of these worked.

For now I resolved to manually creating a (temporary) table, populating it and using it as the input for my merge stored_procedure. (Found no other efficient method and I absolutely need to use the merge function of sql for my database updates).
If that could help anyone, below is my code.

Answer 8 · 2024-09-05T15:08:53.000Z

Here is my current solution, while waiting for a better solution from pyodbc.

def drop_temporary_permit():
    query = f"""
    USE GEODB;
    BEGIN
    DROP TABLE IF EXISTS geoscience.temp_permit;
    END;
    """
    response = write_query("GEODB", query)
    return response

def create_temporary_permit():
    query = f"""
    USE GEODB;
    BEGIN
    CREATE TABLE geoscience.temp_permit(
	    id_permit INT PRIMARY KEY,
	    permit NVARCHAR(100)
    );
    END;
    """
    response = write_query("GEODB", query)
    return response

def insert_into_temporary_permit():
    records_list = df_to_records(select_permits)
    query = f"""
    USE GEODB;
    BEGIN
    INSERT INTO GEODB.geoscience.temp_permit(id_permit, permit) VALUES (?, ?);
    END;
    """
    response = write_query("GEODB", query, records_list)
    return response

def merge_permit():
    query = f"""
    USE GEODB;
    BEGIN
    DECLARE @source_permit AS geoscience.type_permit;
    INSERT INTO @source_permit SELECT * FROM geoscience.temp_permit;
    EXECUTE geoscience.usp_update_permit @source_permit;
    END
    """
    response = write_query("GEODB", query)
    return response

def update_permit():
    try:
        drop_temporary_permit()
        create_temporary_permit()
        insert_into_temporary_permit()
        merge_permit()
        drop_temporary_permit()
    except Exception as e:
        return str(e)

    return "Success"

That works as expected but requires more code and effort than with pyodbc's TVP. And worse, the final function calling all the others is not transactional, so any one that fails in between the calls leave the process unfinished.

Answer 9 · 2024-09-05T16:33:16.000Z

I am unable to reproduce your issue. This code works for me:

import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "Database=GEODB;"
)

tvp_rows = [(1, "permit 1"), (2, "permit 2")]

sql = "EXEC geoscience.usp_update_permit ?"
params = ((["type_permit", "geoscience"] + tvp_rows),)
print(params)
# (['type_permit', 'geoscience', (1, 'permit 1'), (2, 'permit 2')],)

crsr = cnxn.cursor()
crsr.execute(sql, params)
cnxn.commit()

Answer 10 · 2024-09-06T13:24:09.000Z

I am unable to reproduce your issue. This code works for me:

import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "Database=GEODB;"
)

tvp_rows = [(1, "permit 1"), (2, "permit 2")]

sql = "EXEC geoscience.usp_update_permit ?"
params = ((["type_permit", "geoscience"] + tvp_rows),)
print(params)
# (['type_permit', 'geoscience', (1, 'permit 1'), (2, 'permit 2')],)

crsr = cnxn.cursor()
crsr.execute(sql, params)
cnxn.commit()

Thank you @gordthompson , turns out the issue was with my connection strings. Here is the corrected strings:

connection_strings = {
	"GEODB" : "DRIVER={ODBC Driver 17 for SQL Server};SERVER=<hidden on purpose>;DATABASE=GEODB;UID=nmbola;Trusted_Connection=Yes",
	"PDS_PRD" : "DRIVER={ODBC Driver 17 for SQL Server};SERVER=<hidden on purpose>;DATABASE=Some_Other_Database;UID=nmbola;Trusted_Connection=Yes",
	"PEDB_HQ" : "DRIVER={ODBC Driver 17 for SQL Server};SERVER=<hidden on purpose>;DATABASE=Some_Other_Database;UID=nmbola;Trusted_Connection=Yes"
    }

def get_connection(database):
	try:
		connection = connect(connection_strings[database])
		return connection
	except Error as err:
		return err