SQLDescribeParam fails for VARBINARY/BINARY NULL when non-NULL parameter is bound first (ordinal remapping bug)

[jahnvi480]

Describe the bug

SQLDescribeParam returns SQLSTATE 07009 (Invalid descriptor index) when called after a non-NULL parameter has already been bound via SQLBindParameter on the same statement handle. This causes NULL parameters to silently fall back to SQL_VARCHAR, which breaks VARBINARY and BINARY columns:

Exception message:
mssql_python.exceptions.ProgrammingError: Driver Error: Syntax error or access violation; 
DDBC Error: [Microsoft][SQL Server]Implicit conversion from data type varchar to varbinary(max) 
is not allowed. Use the CONVERT function to run this query.

Stack trace:
  File "mssql_python/cursor.py", line 1518, in execute
    check_error(ddbc_sql_const.SQL_HANDLE_STMT.value, self.hstmt, ret)
  File "mssql_python/helpers.py", line 38, in check_error
    raise_exception(error_info.sqlState, error_info.ddbcErrorMsg)
  File "mssql_python/exceptions.py", line 563, in raise_exception
    raise exception_class

Root cause: Our BindParameters loop calls SQLDescribeParam and SQLBindParameter interleaved in a single pass. The ODBC driver internally passes already-bound params as "declared" to sp_describe_undeclared_parameters, which returns remaining params with ordinals restarting from 1. Our code still uses the original parameter index, causing a mismatch.

Proven by calling sp_describe_undeclared_parameters directly:

-- All undeclared → ordinals match
EXEC sp_describe_undeclared_parameters @tsql = N'INSERT INTO t VALUES (@P1, @P2)'
-- ordinal=1 → @P1(int), ordinal=2 → @P2(varbinary) ✅

-- @P1 declared → ordinals restart
EXEC sp_describe_undeclared_parameters @tsql = N'INSERT INTO t VALUES (@P1, @P2)',
    @params = N'@P1 int'
-- ordinal=1 → @P2(varbinary)  ← shifted from 2 to 1! Our code asks for index 2 → 07009 ❌

To reproduce

import mssql_python

conn = mssql_python.connect("Server=your_server;Database=your_db;Trusted_Connection=yes;TrustServerCertificate=yes;")
cur = conn.cursor()

cur.execute("IF OBJECT_ID('dbo.test_varbinary_bug', 'U') IS NOT NULL DROP TABLE dbo.test_varbinary_bug")
cur.execute("CREATE TABLE dbo.test_varbinary_bug (id INT, data VARBINARY(MAX))")
conn.commit()

# FAILS: non-NULL param before NULL VARBINARY param
cur.execute("INSERT INTO dbo.test_varbinary_bug VALUES (?, ?)", (1, None))

# WORKS: both params are NULL (nothing bound before SQLDescribeParam)
cur.execute("INSERT INTO dbo.test_varbinary_bug VALUES (?, ?)", (None, None))

# WORKAROUND: setinputsizes provides the correct type explicitly
from mssql_python.constants import ConstantsDDBC as C
cur.setinputsizes([(C.SQL_INTEGER.value, 0, 0), (C.SQL_VARBINARY.value, 0, 0)])
cur.execute("INSERT INTO dbo.test_varbinary_bug VALUES (?, ?)", (1, None))  # works

cur.execute("DROP TABLE dbo.test_varbinary_bug")
conn.commit()
conn.close()

Expected behavior

INSERT INTO dbo.test_varbinary_bug VALUES (?, ?) with (1, None) should succeed — SQL Server should receive the NULL typed as SQL_VARBINARY (matching the column type), not SQL_VARCHAR.

Further technical details

Python version: 3.13.13
SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8, also reproduced on localdb (MSSQLLocalDB)
Operating system: Windows 11

Additional context

• This bug affects both cursor.execute() (BindParameters in C++) and cursor.executemany() (BindParameterArray in C++).
• Bug has existed since the driver's initial implementation. It was discovered during the investigation of Lots of calls to sp_describe_undeclared_parameters #610.
  Only manifests for VARBINARY/BINARY columns — other types (INT, VARCHAR, DECIMAL, UNIQUEIDENTIFIER, XML) work because SQL Server accepts implicit conversion from varchar to those types for NULLs. VARBINARY/BINARY explicitly rejects varchar conversion even for NULLs.
• Proposed fix: Add a pre-scan loop — call SQLDescribeParam for all NULL params BEFORE calling SQLBindParameter for any param.Add any other context about the problem here.

[github-actions]

Hi @jahnvi480, 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!