pyodbc execute not supporting insert special symbols like trademark(™) symbol and other unicode( ’ ) , others

[shravankumargoud]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

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: 3.9.12
• pyodbc: 4.0.34
• OS: Windows 64-bit
• DB: IBM i access server
• driver: IBM i access ODBC Driver

Issue

When I try to insert a value "B™¥R™ Z" using pyodbc execute, it stores in db as "BÂ¥R Z"
The same insert works good with plain insert command in dB.

Seeing the forum, here i tried, multiple encoding and decoding methods, but none worked for me. Is there a way to skip the decoding or encoding performed by connection to execute the query.

Thanks.

[v-makouz]

What's the ODBC driver being used?

[shravankumargoud]

IBM i Access ODBC Driver

[v-chojas]

I think somewhere in the process there is a confusion between CP1252 and ISO-8859-1 encoding. The 'TM' character is present in the former (at location 99h) but not the latter.

[shravankumargoud]

@v-chojas : The issue is not only with 'TM' other chars like ’ in HMF Women’s Daily are displayed like HMF Womens Daily

[v-chojas]

That is consistent with the confusion between CP1252 and ISO-8859-1, as that character is not present in the latter either. What encoding settings have you tried?

[shravankumargoud]

cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8') cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8') cnxn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le') cnxn.setencoding(encoding='utf-8')

Also tried similarly with iso-8859-1

[v-chojas]

I'm not familiar with your driver but you should look at its documentation for what encodings it expects to use at the ODBC interface. Try CP1252/Windows-1252 instead.

[shravankumargoud]

from the driver documentation, https://www.ibm.com/docs/en/db2/11.5?topic=managers-datadirect-odbc-driver-manager, it uses UTF-8, I tried with CP1252 and Windows-1252, but no luck.

[v-chojas]

pyODBC on Windows wouldn't be using DataDirect's driver manager, but the Windows one. Set the SQL_WCHAR encoding to UCS-2 or UTF-16 and try again.

[shravankumargoud]

I tried with options: setdecoding(po.SQL_WCHAR, encoding='UCS-2') ValueError: not a registered codec: 'UCS-2'

and with UTF-16, no luck, same boxes

[shravankumargoud]

@v-chojas , @v-makouz , @mkleehammer : Any recommendations here, thanks

[gordthompson]

Can you locate an existing row in the table that contains either '™' or '’' and displays correctly in a DB2-specific tool (akin to psql or PGAdmin4 for Postgresql)? If so, what do you get when you try to retrieve that row with pyodbc's default encoding settings?

[keitherskine]

Just a thought, but if this is an IBM database, perhaps the database is expecting EBCDIC-encoded values.

Also, as a workaround, perhaps you can provide the TM symbol as an EBCDIC_CHAR function, e.g. "BÂ" + EBCDIC_CHAR(175) + "¥RÂ" + EBCDIC_CHAR(175) + " Z" (I may have the wrong syntax there).

[gordthompson]

(Ha! I had completely forgotten about EBCDIC. :smile:)

[shravankumargoud]

Thanks a lot for all the recommendations, Finally I got through this by passing the values in a tuple like below: cursor.execute(insert_query, insert_query_values)

insert_query = insert into table column1, column2, colum3 values (?, ?, ?) insert_query_values = (id, name, description)