Issue with returning Cyrillic symbols from MSSQL via unixODBC and FreeTDS

Question

I'm using django-pyodbc as database backend on Ubuntu 12.04 LTS and MSSQL 2008 on remote host. It works good except of returning Cyrillic symbols. Instead of them I see question marks - '?'. I have begin investigation what could cause this problem.

As far as I understand MSSQL-django chain looks so:

MSSQL <-> FreeTDS <-> unixODBC <-> pyodbc <-> django-pyodbc

So I have started from FreeTDS. When I run query in tsql - it works good I can see all symbols including Cyrillic.

The next one was isql - as far as I understand there I can test FreeTDS <-> unixODBC pair. And there I didn't get proper data. In fact when I run query in isql columns that contain Cyrillic symbols are empty or consist of not visible symbols. I guess that problem in communication between FreeTDS <-> unixODBC. What could cause this problem? Btw, I have also tried iusql - nothing have changed.

MSSQL collation is Cyrillic_General_CI_AS.

Content of freetds.conf:

[global]
tds version = 4.2
dump file = /tmp/freetds.log
debug flags = 0xffff
timeout = 10
connect timeout = 10
client charset = UTF-8
text size = 64512

[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0

[rfxdigest]
host = mssql-iis-1
port = 1433
tds version = 8.0
client charset = UTF-8

Content of odbc.ini:

[RFX]
Description = Rfx digest server
Driver = FreeTDS
Database = RFXDB
Servername = rfxdigest
TDS_Version = 8.0 

Edit1 15.08.12

In python using pyodbc I get '?' instead of Cyrillic symbols - I have tried both python versions: UCS2 and UCS4.

Answer 1

You get a ? returned for non-printable characters

Run the command below to see what unicode your Python setup supports :-

python -c "import sys;print(sys.maxunicode<66000)and'UCS2'or'UCS4'"

Next will need to set FreeTDS to use the same character set as Python. If FreeTDS does not support the unicode format you are using in Python you will need to change both Python and FreeTDS.

To rebuild Python from source with UCS2 enabled you need to do something like :-

$ ./configure --enable-unicode=ucs2
$ make
$ sudo make install

Answer 2

Ok, I have made all this modules chain work:

MSSQL <-> FreeTDS <-> unixODBC <-> pyodbc <-> django-pyodbc

I just have added 'unicode_results':True in DATABASES options in django settings:

DATABASES = {
'default': {
    'ENGINE': 'sql_server.pyodbc', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
    'NAME': 'name',                      # Or path to database file if using sqlite3.
    'USER': 'user',                      # Not used with sqlite3.
    'PASSWORD': 'pwd',                  # Not used with sqlite3.
    'HOST': 'server-name',                      # Set to empty string for localhost. Not used with sqlite3.
    'PORT': 'port',                      # Set to empty string for default. Not used with sqlite3.
    'OPTIONS': {
            'unicode_results':True,
            'driver': 'FreeTDS',
            'host_is_server': True,
            'extra_params': 'TDS_VERSION=8.0'
    }

But pyodbc and isql still doesn't work correctly - maybe I have missed other unicode-specific parameters. Going to check how odbc and pyodbc use this unicode_results parameter later. Anyway site now able to show Cyrillic symbols.

Answer 3

I was fighting with problem with cyrillic letters over week. I find diffrent solution, I just use microsoft mssql drivers for Linux, here is great how to install it on other than supported distributions (Suse, redhat): https://groups.google.com/forum/#!topic/shiny-discuss/AyFthz3UGwg

This driver returns normal utf-8, and everything working out of package.