setinputsizes() with SQL_DECIMAL/SQL_NUMERIC crashes with C type mismatch

[dlevy-msft-sql]

Describe the bug

Calling cursor.setinputsizes() with SQL_DECIMAL or SQL_NUMERIC type tuples causes a RuntimeError when followed by executemany(). The C binding rejects Python Decimal objects because _get_c_type_for_sql_type maps SQL_DECIMAL/SQL_NUMERIC to SQL_C_NUMERIC, but the binding doesn't convert Decimal to the SQL_NUMERIC_STRUCT that ODBC expects.

Without setinputsizes(), the driver infers the correct type and inserts succeed.

Exception message:
RuntimeError: Parameter's object type does not match parameter's C type. paramIndex - 2, C type - SQL_C_NUMERIC

To reproduce

import mssql_python
from decimal import Decimal

conn = mssql_python.connect(
    "Server=myserver;Database=mydb;UID=user;PWD=pass;"
    "TrustServerCertificate=yes;Encrypt=yes"
)
cursor = conn.cursor()

cursor.execute("""
    IF OBJECT_ID('dbo.SetInputSizesTest') IS NOT NULL
        DROP TABLE dbo.SetInputSizesTest;
    CREATE TABLE dbo.SetInputSizesTest (
        Name NVARCHAR(100),
        CategoryID INT,
        Price DECIMAL(18,2)
    )
""")
conn.commit()

# This works fine - no setinputsizes
cursor.executemany(
    "INSERT INTO dbo.SetInputSizesTest (Name, CategoryID, Price) VALUES (?, ?, ?)",
    [("Widget", 1, Decimal("19.99")), ("Gadget", 2, Decimal("29.99"))]
)
conn.commit()
print("Without setinputsizes: OK")

cursor.execute("DELETE FROM dbo.SetInputSizesTest")
conn.commit()

# This crashes
cursor.setinputsizes([
    (mssql_python.SQL_WVARCHAR, 100, 0),   # nvarchar(100) - works
    (mssql_python.SQL_INTEGER, 0, 0),       # int - works
    (mssql_python.SQL_DECIMAL, 18, 2)       # decimal(18,2) - crashes
])

cursor.executemany(
    "INSERT INTO dbo.SetInputSizesTest (Name, CategoryID, Price) VALUES (?, ?, ?)",
    [("Widget", 1, Decimal("19.99")), ("Gadget", 2, Decimal("29.99"))]
)
# RuntimeError: Parameter's object type does not match parameter's C type.
# paramIndex - 2, C type - SQL_C_NUMERIC

Expected behavior

setinputsizes() with SQL_DECIMAL should accept Python Decimal values and insert them into a DECIMAL(18,2) column, the same way the driver handles them when type inference is used (without setinputsizes).

Further technical details

Python version: 3.14.0
mssql-python version: 1.4.0 (also confirmed on main branch source)
SQL Server version: SQL Server 2022
Operating system: Windows 11 (ARM64, using x64 fallback module)

Additional context

The root cause is in cursor.py -> _get_c_type_for_sql_type():

SQL_DECIMAL: SQL_C_NUMERIC,
SQL_NUMERIC: SQL_C_NUMERIC,

The C binding expects an SQL_NUMERIC_STRUCT for SQL_C_NUMERIC but receives a Python Decimal object. The same issue occurs with SQL_NUMERIC.

Workaround: Omit SQL_DECIMAL/SQL_NUMERIC from setinputsizes() and let the driver infer the type automatically, which works correctly.

[github-actions]

Hi @dlevy-msft-sql, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[jahnvi480]

Closing this issue, we have fixed this and is now available in our new release v1.6.0.