Ansi to Unicode conversion error (noob)

[oza4h07]

I'm completely new to pyodbc.
I've got an "Ansi to Unicode conversion error" when running a script.

Client Environment

• Python 3.9.2
• pyodbc 4.0.30
• OS Debian 11.9
• driver: libmaodbc.so
• unixodbc: 2.3.6

Server Environment

• DB server: MySQL 5.5.28

Issue

$ cat foo.py

#!/usr/bin/python3
import pyodbc

DSN = 'mydsn'
cx = pyodbc.connect(f'DSN={DSN};charset=utf8')
cx.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cx.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cx.setencoding(encoding='utf-8')

cursor = cx.cursor()
cursor.execute("select credit from FOO")

When executing foo.py, I've got:

Traceback (most recent call last):
  File "/usr/local/bin/foo.py", line 14, in <module>
    cursor.execute("select credit from FOO")
pyodbc.Error: ('HY000', '[HY000] [ma-3.1.9][5.5.28]Ansi to Unicode conversion error occurred (95) (SQLDescribeCol)')

If I comment the last "cursor.execute" line, the script runs fine.
I've read the wiki pages but do not understand much of it, though doc seems complete and well written.

[gordthompson]

libmaodbc.so - That's the MariaDB ODBC driver, correct?

[gordthompson]

Also, an ODBC trace log might be helpful.

[oza4h07]

Yes it is: client uses MariaDB connector and server is a (rather old) MySQL instance.

As I can't change much in this environment, I would like to leave current drivers untouched to only edit above script source code, if possible.

My understanding of setencoding or setdecoding is really minimal to say the least: I copied/pasted these lines from examples found on the net.

I added ODBC trace logging on client host and got several similar errors, the first one being (I redacted database name)

[```
ODBC][213788][1718718822.100400][SQLDriverConnectW.c][290]
Entry:
Connection = 0xdc6220
Window Hdl = (nil)
Str In = [DSN=FOOBAR;charset=utf8][length = 35 (SQL_NTS)]
Str Out = (nil)
Str Out Max = 0
Str Out Ptr = (nil)
Completion = 0
UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][213788][1718718822.104289][SQLDriverConnectW.c][699]
Exit:[SQL_ERROR]

[v-chojas]

Your initial error message says the error occured (in the driver) at SQLDescribeCol but your latest comment references SQLDriverConnectW. Which one is it?

In any case, this seems like a mismatch between what the driver thinks the current narrow encoding should be, and what you've given it.

[oza4h07]

There are serveral errors in ODBC trace: I randomly picked one of them.
Please, find a different one

[ODBC][213788][1718718822.113535][SQLDescribeColW.c][209]
		Entry:
			Statement = 0xe3f7f0
			Column Number = 1
			Column Name = 0x7ffe1e6987a0
			Buffer Length = 300
			Name Length = 0x7ffe1e698790
			Data Type = 0x7ffe1e698792
			Column Size = 0x7ffe1e698798
			Decimal Digits = 0x7ffe1e698794
			Nullable = 0x7ffe1e698796
[ODBC][213788][1718718822.113546][SQLDescribeColW.c][467]
		Exit:[SQL_ERROR]
		DIAG [HY000] [ma-3.1.9][5.5.28]Ansi to Unicode conversion error occurred

If you think the error comes from a driver issue that cannot be worked around in python script, please do not hesitate to close this issue as my environment is probably too specific.

[gordthompson]

If you comment out the lines

# cx.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
# cx.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
# cx.setencoding(encoding='utf-8')

do you still get an error? If so, is it the same error?

[oza4h07]

After commenting those 3 lines (and leaving "cx = pyodbc.connect(f'DSN={DSN};charset=utf8')" unchanged, error becomes:

pyodbc.DataError: ('22018', '[22018] [ma-3.1.9][5.5.28]Invalid character value for cast specification (0) (SQLExecDirectW)')

[gordthompson]

Try using PyMySQL and see if it works better for you.

[oza4h07]

With a different query and PyMySQL, I could successfully get a response.

Immediately, I'll try to adapt to PyMySQL to get what I need.
In the long run, I'll ask people in charge to update DB server to a recent version before testing anew with pyodbc.

Thank you all for your help