Unable to use latin1 characters in SQL querys on MSSQL like ÅÄÖ

[Slasktra]

Environment

• Python:
  3.7.3 (32bit) Tk 8.6.9
• pyodbc:
  python-pyodbc is already the newest version (4.0.22-1+b1)
• OS:
  Debian version 10 (Buster) Rel. Name 2020-02-13
  https://en.wikipedia.org/wiki/Raspbian
• Hardware:
  Raspberry Pi 4B
• DB:
  Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)
  Jan 23 2020 21:00:04
  Copyright (C) 2017 Microsoft Corporation
  Express Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
• driver:
  pi@raspberrypi:~ $ tsql -C
  Compile-time settings (established with the "configure" script)
  Version: freetds v1.00.104
  freetds.conf directory: /etc/freetds
  MS db-lib source compatibility: no
  Sybase binary compatibility: yes
  Thread safety: yes
  iconv library: yes
  TDS version: 4.2
  iODBC: no
  unixodbc: yes
  SSPI "trusted" logins: no
  Kerberos: yes
  OpenSSL: no
  GnuTLS: yes
  MARS: no

Issue

Hi,
I have a RPi 4b with a connection to a MSSQL server. Earlier I hade RPi 3B with Stretch and Python 2.7. With the RPi3 I was able to communicate with my server and send query like "SELECT * from provpress.dbo.Valsidentitet where Hårdhet = 1" without any problems. The Python 2 code started with # -- coding: latin-1 -- .
Now I'm "forced" to use Python 3 and I'm not able to choose coding any longer. From reading the documents for pyodbc and FreeTDS my understanding is that I don't have to bather about coding. Obviously I'm totally wrong. My MSSQL server expect "SQL_Latin1_General_CP1_CI_AS" .

This is an reduced part of my code showing the problem:

connection = pyodbc.connect(driver='{FreeTDS}',
server='hogx0204\SQLEXPRESS',
uid=User, pwd=Psw)
cursor = connection.cursor()
query = "SELECT * from provpress.dbo.Valsidentitet where Hårdhet = 1"
cursor.execute(query)

Results in:

%Run SQL_PY3_Test.py
Traceback (most recent call last):
File "/home/pi/SQL_PY3_Test.py", line 15, in
cursor.execute(query)
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '¥'. (102) (SQLExecDirectW)")

I assume the reason is that (finally) FreeTDS sends out "å" represented as "something" in unicode while the SQL.server expects 0xE5.

If I send a query to the server were the returned recordset includes a "å" works perfectly. But sending "å" to the server is not working.

I would really appreciate if some one could help me to move forward.

[gordthompson]

I am able to reproduce your issue. I strongly suspect that

1. you still have # -- coding: latin-1 -- at the top of your Python source file, but
2. the file is actually UTF-8 encoded.

Remove the # -- coding: latin-1 -- line and your problem should go away.

[Slasktra]

Thanks for taking your time!

I'm so confused because I have been googling my problem for some time before I wrote the issue. But I have not seen anything. Still I think there have to a lot of people struggling with this or the solution is so obvious. (No, the server can't handle UTF-8 so that is not an option).
I checked my code, but no, I'm not using any encoding declaration (#-- coding: latin_1 --) in the program for Python3. I do agree with you about your second point.

You mention you have been able to reproduce my issue. Where you able to solve it?

[gordthompson]

(No, the server can't handle UTF-8 so that is not an option)

SQL Server 2017 definitely can handle Unicode characters, both as column data (nvarchar) and as table/column names (although using supplementary characters like emoji in table/column names is considered bad practice).

To solve your issue I simply used a proper UTF-8 encoded source file with no BOM and no "coding:" declaration at the top.

[Slasktra]

I found this article : https://techcommunity.microsoft.com/t5/sql-server/introducing-utf-8-support-for-sql-server/ba-p/734928 and therefore I was convinced that UTF-8 was not handled by SQL Server 2017. If I under stand you right you set up a table or a data base named with at least one of "å", "ä" or "ö" and it worked to use them in a query? Well you saw my little source file. The only thing I did't show was "import pyodbc" and the lines where Username and Password where past to the User and Psw.

Do you mind sending me your source code and I could run it in my environment?
Thanks

[gordthompson]

import pyodbc

# Note: Save this file as UTF-8 encoded

conn_str = (
    "Driver=FreeTDS;"
    "Server=192.168.0.179;"
    "Port=49242;"
    "UID=sa;PWD=_whatever_;"
    "Database=myDb;"
)
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
crsr.execute("SELECT N'inte tillgänglig' AS Hårdhet")

print(f"Column name: {crsr.description[0][0]}")
# Column name: Hårdhet

print(f"Column value: {crsr.fetchval()}")
# Column value: inte tillgänglig

[keitherskine]

Closed due to inactivity. Feel free to re-open with current information if necessary.