pyodbc error 22018 - Operand type clash : image is incompatible with varchar(max)

[nsrao1975]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 64 bit Version 3.8.6
• pyodbc: 4.3.0
• OS: Windows 10
• DB: SQL Server 2017
• driver: {SQL Server Native Client 11.0}

Issue

The destination table column is with collation 'Latin1_General_100_CI_AI_SC_UTF8'
when i try to insert into the table with a string length more than 8000 (10531) it throws me the following error.

in sql profiler trace it generates the following sql statements

i am unable to understand why it is converting the parameter type to image ?

when the string length is <8000 it works fine by using value.encode('utf-8') without any error.

[keitherskine]

Firstly, if at all possible, I would upgrade your driver to {ODBC Driver 17 for SQL Server}. {SQL Server Native Client 11.0} is rather old now. It might make a difference.
https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows#using-an-odbc-driver

[v-chojas]

It will bind as image when you are sending binary (bytes) type over the 8000 limit. If you want to insert into varchar or nvarchar column, use the Python string type instead.

[mkleehammer]

Should we close this then?

[gordthompson]

Should we close this then?

I think so. It's similar to #835 but apparently not something that pyodbc would need to address if the user simply converted the bytes to an actual string before using it as a parameter value. There would still be the question of how SQL Server handles an arbitrary Unicode string as a parameter to a varchar (not nvarchar) column, but that's "not our problem".

[Lexcon]

The query below, executed through pyODBC does not work when the database is set to Latin1_General_100_CI_AS_SC_UTF8.
Substituting the parameter with a huge string literal and running the query in SSMS works.
This is SQL Server 2022, Odbc driver 18, python 311.

Declare @ccontent varchar(max), @xmldata xml
Set @ccontent = ?xmlDataGoesHere -- large unicode string containing XML > 8000 characters
Set @xmldata = @ccontent

[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC_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)

I tried pushing the xml content in as raw utf-8, which changed the error message to this:

Declare @ccontent varchar(max), @xmldata xml
Set @ccontent = ?b_xmlData -- large utf-8 binary bytestream containing XML
Set @xmldata = @ccontent

[22018] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Operand type clash: image is
incompatible with varchar(max) (206) (SQLExecDirectW);
[22018] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180))

Then when I changed the query to assume varbinary(max) it worked:
Declare @ccontent varbinary(max), @xmldata xml
Set @ccontent = ?xmlDataGoesHere -- large utf-8 encoded string containing XML > 8000 characters
Set @xmldata = @ccontent

Note that this means that when in a Latin1_General_100_CI_AS_SC_UTF8 encoded database you want to have large XML strings as parameter, you must resort to 'work around' some problems by pushing in UTF-8 bytestreams into SQL Server so use myparameter.encode.

[gordthompson]

@Lexcon - Since you are using "ODBC Driver 18 for SQL Server" did you try adding LongAsMax=yes to your connection string?

[Lexcon]

@gordthompson Yes! this solves it too, without the workaround.

Thanks!