search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.92k stars 563 forks source link

[ODBC Driver 17 for SQL Server] Unsupported cast of parameter data to text/ntext with collation 'Latin1_General_100_BIN2_UTF8'. #1021

Closed jdhowroyd closed 2 years ago

jdhowroyd commented 2 years ago

Environment

Ubuntu: 20.04.2 LTS amd64 Python: 3.8.2-0ubuntu2 amd64 pyodbc: 4.0.32 driver: ODBC Driver 17 for SQL Server (msodbcsql17) 17.8.1.1-1 amd64 database: Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Enterprise Edition running on: Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) / ESXi VMWare

Issue

Parameterised queries raise pyodbc.ProgrammingError:

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot convert to text/ntext or collate to 'Latin1_General_100_BIN2_UTF8' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags. (4189) (SQLParamData)")

when string data exceeds 2000 characters and the database collation is utf8 encoded.

Looks like an underlying issue with the driver.

Example code

Attached. mssql_test.py.gz

Further details in the doc string!

v-chojas commented 2 years ago

https://github.com/mkleehammer/pyodbc/issues/835 Use setinputsizes() to send as varchar(max).

jdhowroyd commented 2 years ago

Thanks for pointing me to the earlier post (and apologies for raising what is essentially the same issue). I confirm that the use of cursor.setinputsize([(pyodbc.SQL_VARCHAR, 0, 0), ]) fixes the issue in the example code (or SQL_WVARCHAR or both). Clearly, a more complex issue deeper down.

gordthompson commented 2 years ago

Alternative to using setinputsizes()

https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-1047228743

alexey-formalmethods commented 1 year ago

works for me on Mac M2, thx

Thanks for pointing me to the earlier post (and apologies for raising what is essentially the same issue). I confirm that the use of cursor.setinputsize([(pyodbc.SQL_VARCHAR, 0, 0), ]) fixes the issue in the example code (or SQL_WVARCHAR or both). Clearly, a more complex issue deeper down.