Bound NULL parameter mis-typed on MS SQL Server when not the first parameter (SQLDescribeParam rejected after binds, 07009)

[christianparpart]

Summary

A bound NULL parameter is bound with the wrong type whenever it is not the first bound parameter on MS SQL Server, producing a spurious conversion error (e.g. conversion from data type char to varbinary is not allowed) for NULLs into non-char columns (binary, and potentially others). The affected path is SqlStatement::ExecuteWithVariants together with SqlDataBinder<SqlNullType>::InputParameter.

This is the general form of the bug worked around for the query-builder path in #613. #613 stops the query builder from emitting a bound NULL (it renders the SQL literal NULL instead), so the builder path is safe. Any caller that binds a NULL SqlVariant directly — ExecuteWithVariants with a null in the vector, or a SqlWildcard/? bound to a null at runtime — still hits this.

Root cause (proven)

SqlDataBinder<SqlNullType>::InputParameter resolves the column's SQL type lazily, while binding:

https://github.com/LASTRADA-Software/Lightweight/blob/master/src/Lightweight/DataBinder/SqlNullValue.hpp#L38-L59

SQLSMALLINT const sqlType = [stmt, column, serverType = cb.ServerType()]() -> SQLSMALLINT {
    if (serverType == SqlServerType::MICROSOFT_SQL)
    {
        SQLSMALLINT columnType {};
        auto const sqlReturn = SQLDescribeParam(stmt, column, &columnType, nullptr, nullptr, nullptr);
        if (SQL_SUCCEEDED(sqlReturn))
            return columnType;
    }
    return SQL_C_CHAR;   // <-- fallback when describe fails; SQL Server rejects char -> varbinary
}();

ExecuteWithVariants binds parameters in ordinal order in a loop:

https://github.com/LASTRADA-Software/Lightweight/blob/master/src/Lightweight/SqlStatement.cpp#L659-L667

for (auto const& [i, arg]: args | std::views::enumerate)
    SqlDataBinder<SqlVariant>::InputParameter(m_hStmt, static_cast<SQLUSMALLINT>(1 + i), arg, *this);

The MS SQL Server ODBC driver rejects SQLDescribeParam once any parameter on the statement has been bound, returning:

SQLSTATE 07009 — "Invalid Descriptor Index"

So for a NULL at ordinal N > 1, parameters 1..N-1 are already bound by the time the null binder calls SQLDescribeParam for ordinal N → the call fails → sqlType falls back to SQL_C_CHAR → the NULL is bound as a char parameter → SQL Server refuses to assign it to a binary column.

Evidence (standalone ODBC probe, no Lightweight)

Prepared INSERT INTO MITARBEITER (MITARB_NR, NAME, KUERZEL, LOHNKOST_HIST, PW) VALUES (?,?,?,?,?) where PW is varbinary:

When SQLDescribeParam is called	Result
Before any SQLBindParameter	Succeeds for every parameter, incl. PW (typed SQL_VARBINARY, code -3)
After earlier parameters are bound (ExecuteWithVariants order)	Fails, 07009 "Invalid Descriptor Index"

sp_describe_undeclared_parameters (which backs SQLDescribeParam) types the parameter as varbinary(250) correctly on its own — the failure is purely the describe-after-bind ordering, not the column or table shape.

Reproduction

INSERT (or UPDATE) a NULL into a binary column where the null parameter is not the first bound parameter, executed via ExecuteWithVariants (or any direct null bind), against MS SQL Server. The NULL is bound as char and the driver returns the conversion error.

Impact

• Silent-until-execution wrong parameter typing for any directly-bound runtime NULL after the first parameter on MS SQL Server.
• Manifests as a data-dependent write failure — only when the null lands in a column that rejects a char NULL (binary today; a latent risk for other strict types).
• Not caught by the query-builder path after fix(SqlQuery): render a null SqlVariant as a literal NULL, not a bound parameter #613, so it hides behind a green builder test suite.

Proposed fix

Resolve the null parameter's type before any parameter is bound, then bind with the discovered type. Options:

1. Pre-describe pass in ExecuteWithVariants: before the bind loop, for each argument that is null, call SQLDescribeParam (all describes happen before any bind, so none fails), cache the ordinal→SQL-type map, and have the null binder consume the cached type via the SqlDataBinderCallback.
2. Callback-supplied type hint: extend SqlDataBinderCallback so a caller that already knows the column type (e.g. a schema-driven gateway) can hand it to the null binder, avoiding SQLDescribeParam entirely.
3. SQL_ATTR_ENABLE_AUTO_IPD: evaluate whether enabling auto-populated IPD lets the driver supply the type without an explicit SQLDescribeParam after binds (needs verification per driver).

Option 1 is self-contained and driver-correct; option 2 is the most efficient when the type is already known.

Add a Lightweight ODBC test: ExecuteWithVariants inserting a NULL into a binary column at a non-first parameter position on MS SQL Server, asserting the row is written with the column NULL.

Relationship to #613

#613 is the correct, minimal fix for the query-builder path (a null constant is SQL NULL, not a bound parameter) and unblocks the consumer that surfaced this. This issue tracks the remaining general-case defect for directly-bound NULLs.