Closed jdiaz-merkle closed 10 months ago
Hello jdiaz-merkle, I am also facing the same issue, How did you resolved this issue.
Hi @naresh561,
It took some time for us to find a reliable solution, but following the steps below it should resolve the problem.
_Please notice that on example below tvpcolumns is the list with the columns from this process.
import pytds
import datetime
sql_statement = """EXEC MySchema.MyUserStoredProcedure
@Data = %(tvp)s,
@EventUUID = %(event_uuid)s,
@UserUUID = %(user_uuid)s;"""
array_tvp = [
['d191affd66e4d8ef1cd14a368cc3b920', 'SOME TEXT ROW 1 COLUMN 2', 'SOME TEXT ROW 1 COLUMN 3', 'SOME TEXT ROW 1 COLUMN 4', 100, None, datetime.datetime(2021, 5, 7, 21, 32, 45)]
, ['7b61f7245e1f5bf0d17f0d14ad57339d', 'SOME TEXT ROW 2 COLUMN 2', 'SOME TEXT ROW 2 COLUMN 3', 'SOME TEXT ROW 2 COLUMN 4', 1, 'd191affd66e4d8ef1cd14a368cc3b920', datetime.datetime(2021, 5, 7, 21, 32, 45)]
]
with conn.cursor() as cursor:
logger.info('Execute SQL statement for MySchema.MyUserStoredProcedure')
cursor.execute(
operation=sql_statement,
params={
"tvp": pytds.TableValuedParam(
type_name='MySchema.MyUserStoredProcedureDataType',
rows=array_tvp,
columns=tvp_columns
),
"event_uuid": '2ca5a8df-27de-4fc9-b8bd-314f64af420c',
"user_uuid": '29c1da9b-661d-4bc8-a24a-c4f60155b8fd'
}
)
cursor.get_proc_return_status()
logger.info('Execute SQL statement for MySchema.MyUserStoredProcedure successfully executed')
On top of this, we have also implemented some efficiencies like doing all this transformation during the initialisation of our software and storing resultant tvp_columns for each TVP on a dictionary in a system cache.
I hope this helps!
Thank You @jdiaz-merkle, I will try and Let you know. Thank You for quick response.
@jdiaz-merkle, It worked, I created a stored procedure to get the columns data and cached it for future use.
@baresh561 @jdiaz-merkle Knowing it has been late for years. pytds estimates column type by checking first row of data. Column type default to be nvarchar(1) If data in first column is None. Hence error is thrown if data succeeding is not nvarchar(1).
@baresh561 @jdiaz-merkle made 2 helper functions to find out user data types and populate a list of Column objects:
def getType(tvtName, cursor):
from pytds.tds_base import Column
parser = pytds.tds_types.DeclarationsParser()
rs = cursor.execute("SELECT name, system_type_name, is_nullable FROM sys.dm_exec_describe_first_result_set('SELECT * FROM @test', N'@test " + tvtName + " READONLY', 0) ORDER BY column_ordinal").fetchall()
return [Column(name=row[0], type=parser.parse(row[1]), flags = row[2] & Column.fNullable) for row in rs]
array_tvp = [
[None, 'SOME TEXT ROW 1 COLUMN 2', 'SOME TEXT ROW 1 COLUMN 3', 'SOME TEXT ROW 1 COLUMN 4', 100, '1', datetime.datetime(2021, 5, 7, 21, 32, 45)]
, ['7b61f7245e1f5bf0d17f0d14ad57339d', 'SOME TEXT ROW 2 COLUMN 2', 'SOME TEXT ROW 2 COLUMN 3', 'SOME TEXT ROW 2 COLUMN 4', 1, 'd191affd66e4d8ef1cd14a368cc3b920', datetime.datetime(2021, 5, 7, 21, 32, 45)]
]
# getType() to get column list from user-defined types and getTable() for tables
with conn.cursor() as cursor:
columns = getType("testType", cursor)
with conn.cursor() as cursor:
cursor.execute(
operation=sql_statement,
params={
"tvp": pytds.TableValuedParam(
type_name='testType',
columns=columns,
rows=array_tvp)
}
)
I changed default flags in Column class to allow nullable values by default. This change would address issue raised here while at the same time I don't see any negative effects in changing this default.
Hello,
We are running the code below in Python (3.8) using pytds version 1.11.0 and I am receiving the error message "
pytds.tds_base.OperationalError: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 5 (""), row 2, column 6: Data type 0xE7 has an invalid data length or metadata length.
"Looking at the error it seems that colums 6 in row 2 is exceeding the data type size, however, if I revers the order of the rows, this code runs successfully.
It looks like the
None
value provided on the first row is causing the issue, since replacing thisNone
by an empty string is making the code to run without any problem.I have also tried to use
callproc
method instead ofexecute
, but it is returnining the same error.The expected behaviour is to accept the None value and proceed with the others rows without any problem since all of them are complient with the type definition.
Supporting code:
Thanks in advance for your help!