0

What i'am trying: - just make a select with erlang-odbc from elixir and dump all result to console.

Enviroment: my side

  • Red Hat Enterprise Linux Server release 7.6 (Maipo)
  • unixODBC-devel ( yum)
  • Elixir 1.8.2 (compiled with Erlang/OTP 20)
  • Erlang/OTP 21
  • erlang-odbc R16B (yum)
  • (either) mssql driver
  • (or) FreeTds driver 1.1.6 (compiled from sources --with-unixodbc)

target

  • mssql server 2016
  • table 


    CREATE TABLE db_name.dbo.rating (
        [Year] int NULL,
        Code varchar(256) COLLATE Cyrillic_General_CI_AS NULL,
        Name nvarchar(4000) COLLATE Cyrillic_General_CI_AS NULL,
        GroupeCode varchar(256) COLLATE Cyrillic_General_CI_AS NULL,
        GroupeName nvarchar(4000) COLLATE Cyrillic_General_CI_AS NULL,
        Cost numeric(38,5) NOT NULL,
        PrchasesCount int NULL
        ) GO

nvarchar: character_set_name: UNICODE collation_name: Cyrillic_General_CI_AS

varchar: character_set_name: cp1251 collation_name: Cyrillic_General_CI_AS

elixir code looks like:

conn_str = 
    "SERVER=XX.XX.XX.XX,1433;" <>
     #tried this too! "DRIVER={ODBC Driver 17 for SQL Server};" <>
    "DRIVER=FreeTDS;" <>
    "DATABASE=db_name;UID=bot;PWD=XXXXXX;" 
|> to_charlist

 statement = "select top(3) Name from Rating order by Cost desc" |> to_charlist

 {:ok, pid}=:odbc.connect(conn_str,[])

 {:selected, col_names, rows} = :odbc.sql_query(pid, statement)

and, after all attempts, i have something like this in result

{:selected, ['Name'],
 [
   {<<32, 4, 48, 4, 49, 4, 62, 4, 66, 4, 75, 4, 32, 0, 65, 4, 66, 4, 64, 4, 62,
      4, 56, 4, 66, 4, 53, 4, 59, 4, 76, 4, 61, 4, 75, 4, 53, 4, 32, 0, 63, 4,
      62, 4, 32, ...>>},
   {<<16, 4, 64, 4, 53, 4, 61, 4, 52, 4, 48, 4, 32, 0, 63, 4, 48, 4, 65, 4, 65,
      4, 48, 4, 54, 4, 56, 4, 64, 4, 65, 4, 58, 4, 62, 4, 51, 4, 62, 4, 32, 0,
      66, 4, ...>>},
   {<<35, 4, 65, 4, 59, 4, 67, 4, 51, 4, 56, 4, 32, 0, 63, 4, 62, 4, 32, 0, 64,
      4, 53, 4, 58, 4, 67, 4, 59, 4, 76, 4, 66, 4, 56, 4, 50, 4, 48, 4, 70, 4,
      56, ...>>}
 ]}

instead a correct Cyrillic text

its a not random numbers! result always the same.

MS driver gives same result as FreeTDS

what else i've tried

  • changing conn options binary_strings: :on/off
  • setting option client charset = UTF-8 in freetds.conf (in global section)
  • scratching my head
  • using :unicode functions to read << data >>
  • using is_binary() on received numbers returns true

Questions

  • what type of data do i receive?
  • why data is not correct decoded?
  • what app is responsible for it?
  • how can i fix it? some part of freetds log here (about iconv)
iconv.c:326:tds_iconv_open(0x1e02330, UTF-8)
iconv.c:186:local name for ISO-8859-1 is ISO-8859-1
iconv.c:186:local name for UTF-8 is UTF-8
iconv.c:186:local name for UCS-2LE is UCS-2LE
iconv.c:186:local name for UCS-2BE is UCS-2BE
iconv.c:348:setting up conversions for client charset "UTF-8"
iconv.c:350:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:389:tds_iconv_open: done
iconv.c:785:setting server single-byte charset to "CP1251"
Drilla
  • 109
  • 8
  • Are you sure it's UTF-8 and not UTF-16? See this [question and answer](https://stackoverflow.com/questions/22517250/how-to-convert-an-elixir-binary-to-a-string) for something that may have some bearing on your question. – Onorio Catenacci Jun 27 '19 at 12:55

1 Answers1

1

If you're using SQL Server 2019, you're going to want to use a collation that ends in _UTF8 rather than _AS. There's a full article here with details on collation types:

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017#utf-8-support

Previous to MSSQL 2019,

I know this works for Python 3.

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • i wonder this is about ms sql server configuration, but, alas, it is out of my control. Anyway, they are on 2016 version – Drilla Jun 27 '19 at 10:31