Summary
SQL_SS_UDT is accepted by setinputsizes() and maps to SQL_C_BINARY, but there is no way for a caller to supply the UDT's type name. Because the three-part name is mandatory in the TDS parameter header, a CLR UDT (hierarchyid, geometry, geography, or a user-registered assembly type) cannot currently be bound as a parameter.
Repro
from mssql_python.constants import ConstantsDDBC
cursor.setinputsizes([(ConstantsDDBC.SQL_SS_UDT.value, 8000, 0)])
cursor.execute("INSERT INTO t(h) VALUES (?)", [payload_bytes])Expected: the row inserts.
Actual: HY000 - At least 3-parts name of a UDT type should be present.
The same sequence fails against msodbcsql (ODBC Driver 18) with the identical SQLSTATE and message, so this is not driver-specific.
Why
The UDT type name reaches the wire only through the IPD descriptor field SQL_CA_SS_UDT_TYPE_NAME (plus the optional catalog/schema fields). Two things block that today:
setinputsizes() stores a 4-tuple (sql_type, c_type, column_size, decimal_digits), and ParamInfo (mssql_python/pybind/param_detect.hpp) has nine fields - inputOutputType, paramCType, paramSQLType, columnSize, decimalDigits, strLenOrInd, isDAE, dataPtr, utf16Len - none of which is a catalog/schema/type name. ApplyInputSizeOverride reads exactly values[0..3].SQLSetDescField is called only for SQL_C_NUMERIC, gated on if (paramInfo.paramCType == SQL_C_NUMERIC) in ddbc_bindings.cpp, where it sets SQL_DESC_TYPE / SQL_DESC_PRECISION / SQL_DESC_SCALE / SQL_DESC_DATA_PTR. SQL_CA_SS_UDT_TYPE_NAME (1220) does not appear anywhere in the repo, and ddbc_bindings.h defines SQL_CA_SS_VARIANT_TYPE (1215) but none of the UDT field IDs (1217-1220).
Automatic detection never produces SQL_SS_UDT either: bytes / bytearray map to SQL_VARBINARY on both the legacy _map_sql_type path and the native DetectParamTypes path, so setinputsizes is the only route in.
Note also that SQL_SS_UDT is not exported as module-level public API (_DDBC_PUBLIC_API lists SQL_SS_TIME2 / SQL_SS_XML / SQL_SS_VARIANT but not SQL_SS_UDT; tests/test_003_connection.py asserts it as an internal_type_constants member), so callers must reach it via ConstantsDDBC.SQL_SS_UDT.value.
Two possible fixes
Option 1 - let the caller name the type. Accept an optional 4th element in the setinputsizes tuple, carry it on ParamInfo, and call SQLSetDescField(SQL_CA_SS_UDT_TYPE_NAME) on the IPD after SQLBindParameter:
cursor.setinputsizes([(ConstantsDDBC.SQL_SS_UDT.value, 8000, 0, "dbo.hierarchyid")])
Option 2 - let the server name it. Call SQLDescribeParam for a SQL_SS_UDT parameter before SQLBindParameter. Both msodbcsql and mssql-rs fill the IPD's UDT identity from sp_describe_undeclared_parameters' suggested_user_type_database / _schema / _name columns when the caller is SQLDescribeParam. The ordering matters: an already-bound record is left untouched by both drivers.
Option 2 needs no public API change and would make the repro above work as written. Option 1 is more explicit and avoids a metadata round trip. They are not mutually exclusive.
Driver-side status
mssql-rs implements both halves: the descriptor fields (SQL_CA_SS_UDT_CATALOG_NAME / _SCHEMA_NAME / _TYPE_NAME / _ASSEMBLY_TYPE_NAME, settable and readable on the IPD) and the SQLDescribeParam auto-fill described in Option 2. So either option above is enough to unblock this end to end.
Environment
- mssql-python:
main
- Repro is independent of server version; verified against the ODBC contract and msodbcsql source, not yet measured against a live server.
Summary
SQL_SS_UDTis accepted bysetinputsizes()and maps toSQL_C_BINARY, but there is no way for a caller to supply the UDT's type name. Because the three-part name is mandatory in the TDS parameter header, a CLR UDT (hierarchyid,geometry,geography, or a user-registered assembly type) cannot currently be bound as a parameter.Repro
Expected: the row inserts.
Actual:
HY000 - At least 3-parts name of a UDT type should be present.The same sequence fails against msodbcsql (ODBC Driver 18) with the identical SQLSTATE and message, so this is not driver-specific.
Why
The UDT type name reaches the wire only through the IPD descriptor field
SQL_CA_SS_UDT_TYPE_NAME(plus the optional catalog/schema fields). Two things block that today:setinputsizes()stores a 4-tuple(sql_type, c_type, column_size, decimal_digits), andParamInfo(mssql_python/pybind/param_detect.hpp) has nine fields -inputOutputType,paramCType,paramSQLType,columnSize,decimalDigits,strLenOrInd,isDAE,dataPtr,utf16Len- none of which is a catalog/schema/type name.ApplyInputSizeOverridereads exactlyvalues[0..3].SQLSetDescFieldis called only forSQL_C_NUMERIC, gated onif (paramInfo.paramCType == SQL_C_NUMERIC)inddbc_bindings.cpp, where it setsSQL_DESC_TYPE/SQL_DESC_PRECISION/SQL_DESC_SCALE/SQL_DESC_DATA_PTR.SQL_CA_SS_UDT_TYPE_NAME(1220) does not appear anywhere in the repo, andddbc_bindings.hdefinesSQL_CA_SS_VARIANT_TYPE(1215) but none of the UDT field IDs (1217-1220).Automatic detection never produces
SQL_SS_UDTeither:bytes/bytearraymap toSQL_VARBINARYon both the legacy_map_sql_typepath and the nativeDetectParamTypespath, sosetinputsizesis the only route in.Note also that
SQL_SS_UDTis not exported as module-level public API (_DDBC_PUBLIC_APIlistsSQL_SS_TIME2/SQL_SS_XML/SQL_SS_VARIANTbut notSQL_SS_UDT;tests/test_003_connection.pyasserts it as aninternal_type_constantsmember), so callers must reach it viaConstantsDDBC.SQL_SS_UDT.value.Two possible fixes
Option 1 - let the caller name the type. Accept an optional 4th element in the
setinputsizestuple, carry it onParamInfo, and callSQLSetDescField(SQL_CA_SS_UDT_TYPE_NAME)on the IPD afterSQLBindParameter:Option 2 - let the server name it. Call
SQLDescribeParamfor aSQL_SS_UDTparameter beforeSQLBindParameter. Both msodbcsql and mssql-rs fill the IPD's UDT identity fromsp_describe_undeclared_parameters'suggested_user_type_database/_schema/_namecolumns when the caller isSQLDescribeParam. The ordering matters: an already-bound record is left untouched by both drivers.Option 2 needs no public API change and would make the repro above work as written. Option 1 is more explicit and avoids a metadata round trip. They are not mutually exclusive.
Driver-side status
mssql-rs implements both halves: the descriptor fields (
SQL_CA_SS_UDT_CATALOG_NAME/_SCHEMA_NAME/_TYPE_NAME/_ASSEMBLY_TYPE_NAME, settable and readable on the IPD) and theSQLDescribeParamauto-fill described in Option 2. So either option above is enough to unblock this end to end.Environment
main