Use ODBC 3.x temporal types for parameter binding

[Theekshna]

Describe the bug

mssql-python declares itself as an ODBC 3.8 application by setting SQL_ATTR_ODBC_VERSION to SQL_OV_ODBC3_80, but some parameter-binding paths still pass ODBC 2.x temporal SQL type identifiers to SQLBindParameter:

• SQL_DATE (9) instead of SQL_TYPE_DATE (91)
• SQL_TIMESTAMP (11) instead of SQL_TYPE_TIMESTAMP (93)

This currently works with drivers that retain ODBC 2.x application compatibility, but it is not behavior an ODBC 3.x application should rely on. mssql-odbc targets ODBC 3.x only and intentionally does not implement ODBC 2.x application behavior, so it rejects SQL_DATE as an ambiguous ParameterType with HY004 (Invalid SQL data type). It will not add ODBC 2.x application support in the future.

This causes mssql-python compatibility tests to fail when run against mssql-odbc, including:

• tests.test_004_cursor::test_executemany_mixed_null_and_typed_values
• tests.test_004_cursor::test_setinputsizes_with_null_values

The optimized detector in mssql_python/pybind/param_detect.hpp already uses the ODBC 3.x identifiers. The inconsistency is in the Python fallback used by executemany() and setinputsizes():

• Cursor._map_sql_type() returns SQL_DATE for datetime.date and SQL_TIMESTAMP for naive datetime.datetime.
• Cursor.setinputsizes() accepts legacy temporal identifiers and stores them unchanged.
• ddbc_bindings.cpp forwards ParamInfo.paramSQLType directly to SQLBindParameter without canonicalization.

To reproduce

import os
import mssql_python

connection = mssql_python.connect(os.environ["DB_CONNECTION_STRING"])
cursor = connection.cursor()

cursor.execute("CREATE TABLE #odbc3_date_repro (value DATE)")
cursor.setinputsizes([(mssql_python.SQL_DATE, 0, 0)])
cursor.execute("INSERT INTO #odbc3_date_repro VALUES (?)", None)

Against mssql-odbc, the bind receives ParameterType = 9 and fails with HY004. Using mssql_python.SQL_TYPE_DATE (91) succeeds.

The executemany() path can reach the same legacy mapping without an explicit setinputsizes() call because its Python-side type inference returns SQL_DATE/SQL_TIMESTAMP.

Expected behavior

Because mssql-python declares ODBC 3.8, every SQL temporal ParameterType sent to the driver should use the ODBC 3.x concise identifiers:

• SQL_TYPE_DATE (91)
• SQL_TYPE_TIME (92)
• SQL_TYPE_TIMESTAMP (93)

Suggested implementation:

1. Change Python-side automatic inference to return SQL_TYPE_DATE and SQL_TYPE_TIMESTAMP.
2. Canonicalize legacy SQL_DATE, SQL_TIME, and SQL_TIMESTAMP values supplied through setinputsizes() to their SQL_TYPE_* equivalents before storing/binding them.
3. Keep the exported legacy constants at their standard numeric values for API compatibility; do not redefine SQL_DATE from 9 to 91.

Acceptance criteria

• datetime.date inference binds with SQL_TYPE_DATE (91).
• Naive datetime.datetime inference binds with SQL_TYPE_TIMESTAMP (93).
• Legacy temporal hints passed to setinputsizes() are canonicalized before SQLBindParameter.
• The two compatibility tests listed above pass against mssql-odbc.
• Existing execution against msodbcsql remains passing.

Further technical details

Python version: all supported versions
SQL Server version: not server-version-specific
Operating system: cross-platform

Additional context

Related mssql-rs tracking item: AB#47874

[github-actions]

Hi ttk (@Theekshna), 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!