Can pyodbc handle more than 2100 parameters like mxodbc does?

[fazalsulaiman]

How does mxodbc handle the bind param, can 'pyodbc' do that?.
mxodbc is handling more than 2100 bind parameters in the application query, In this use case mxodbc convert query with out band parameters, and it works.

Current pyodbc (v4.0.39) Behaviour:

if the bind parameters <= 2097.
    successful query execution.

if the bind parameters is 2098 or 2100.
    ProgrammingError:
    `ProgrammingError('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. (8003) (SQLExecDirectW)')`

if the bind paramters > 2100.
    pyodbc.Error:
    `sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')`

Current mxODBC (egenix-mxodbc v3.3.6) Behaviour:

if bind parameters <= 2098.
    successful query execution.

if the bind parameters is 2099 or 2100.
    sqlalchemy.exc.ProgrammingError:
   `sqlalchemy.exc.ProgrammingError: (mx.ODBC.Error.ProgrammingError) ('42000', 8003, '[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.', 11159)` 

if the bind parameters > 2100.
    In-place replacement of parameters(no bindparams), successful query execution.

[v-chojas]

ODBC driver implementation limits are beyond the scope of pyODBC.

[gordthompson]

How does mxodbc handle the bind param

mxodbc is closed-source, so the only way to know is to ask the authors. However, the package has not seen a release in over 7 years, so I wouldn't have much hope for a reply.

The OPENJSON() function in SQL Server 2016+ can be used as a workaround. For example,

id_list = list(range(1, 4))

sql = f"""\
SELECT int_col FROM million_rows
WHERE id IN ({', '.join('?' * len(id_list))})
"""
rows = crsr.execute(sql, id_list).fetchall()

creates id_list as [1, 2, 3] and sql as SELECT int_col FROM million_rows WHERE id IN (?, ?, ?). When we .execute() we get three (3) rows back.

However, if we use id_list = list(range(1, 2201)) we get an error:

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

The workaround is

id_list = list(range(1, 2201))

id_list_of_dict = [{"id": v} for v in id_list]
sql = """\
SELECT int_col FROM million_rows
WHERE id IN (
    SELECT id FROM OPENJSON(?) WITH (id int '$.id')
)
"""
rows = crsr.execute(
    sql,
    json.dumps(id_list_of_dict, default=str)
).fetchall()