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

[supernyv]

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.

[supernyv]

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;

[supernyv]

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)

[v-chojas]

EXECUTE GEODB.geoscience.usp_update_permit

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

[supernyv]

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.

[supernyv]

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.

[v-chojas]

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

[supernyv]

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.

[supernyv]

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.

[gordthompson]

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

[supernyv]

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