ODBC.jl does not process latin1 strings correctly on Windows

[ahjulstad]

On Win 10, it seems that the ODBC driver outputs strings in the local system locale, while ODBC.jl erroneously assumes they are coming as UTF-8.

Tested on Win10 with ODBC driver 18. MSSQL from mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04

If I change system locale to UTF-8 as discussed here, Strings are correctly parsed.

The test code I used is below: (DockerCompose is just a small package I threw together to make it easy to spin up fresh MSSQL instances for testing.)

Something should perhaps be fixed in ODBC.jl, if the scenario of ODBC towards MSSQL on Windows is important.. .. a local workaround is still to force UTF-8 as in the link

using DockerCompose     #  https://github.com/ahjulstad/DockerCompose.jl
using DBInterface
using DBInterface: execute
using DataFrames

c = DockerCompose.sqlserver()

conn = try
    DockerCompose.connect(c)
catch
    sleep(10)
    DockerCompose.connect(c)
end

execute(conn, "CREATE DATABASE withutf8 COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8") 
execute(conn, "USE withutf8")
execute(conn, "CREATE TABLE textutf8 (
    t  [nvarchar](max) NULL,
)")

execute(conn, "INSERT INTO textutf8 (t) values 
    ('Bjørnefjært i går')
")
execute(conn, "INSERT INTO textutf8 (t) values 
('望研測来白制父委供情治当認米注。規')
")


res = execute(conn, "SELECT * from textutf8") 
DataFrame(res)

[taylorwarczinsky]

Thanks @ahjulstad for your issue. Saved me after days of tracking down why greek symbols were not working correctly.
I agree that it would be great if this was fixed to avoid having to check the "use unicode" box on every machine you run on.