pyodbc & FreeTDS: Can I work with NVARCHAR column on MSSQL2008

[GoogleCodeExporter]

Hello!

Can I get unicode data from nvarchar column on Mssql2008 database using pyodbc?
To do this, what does other library or configuration need me?

I use FreeTDS driver to connect with mssql by pyodbc. But I don't know version 
of FreeTDS.
How to know the version of FreeTDS?

What steps will reproduce the problem?
        import pyodbc
        conn = pyodbc.connect(r'DRIVER={FreeTDS};SERVER=10.0.0.61\mssql2008;DATABASE=eoffice_clone;UID=erp;PWD=123;CHARSET=UCS-2;')
        crms = conn.cursor()
        crms.execute("SELECT cc_Name FROM tblHR_CodeClass")
        for line in crms:
            print 'cc_Name:', str(line.cc_Name)
        conn.close()

What is the expected output? What do you see instead?
Expected result is 'cc_Name: Аймаг, хот', but instead 'cc_Name: ?????, 
???' comes.

What version of the product are you using? On what operating system?
I have FreeTDS, pyodbc-2.1.8, python-2.6, ubuntu-10.4.
I also have MSSQL2008 database server on Windows7.

Please provide any additional information below.

Original issue reported on code.google.com by orgi...@gmail.com on 31 Jan 2011 at 12:44

[GoogleCodeExporter]

I get more problem reports from FreeTDS than anything else.  I need to setup a 
debug FreeTDS environment so I can trace through my C code and theirs.

In the meantime, I'm not sure the CHARSET takes affect there.  According to 
their Connect attributes page of the user guide, it isn't supported: 
http://www.freetds.org/userguide/odbcconnattr.htm

I would not be surprised to find out the docs are out of date.  However, can 
you also try putting the charset into the freedts.org file and testing: 
http://www.freetds.org/userguide/freetdsconf.htm

Original comment by mkleehammer on 28 Feb 2011 at 12:58

• Changed state: Investigating

[GoogleCodeExporter]

Issue 152 has been merged into this issue.

Original comment by mkleehammer on 28 Feb 2011 at 1:04

[GoogleCodeExporter]

IMHO problem is django-pyodbc related.
Shortly: 
tried to read nvarchar fields from MSSQL 2008R2 Express. The stored strings are 
windows-1250 encoded.

- tsql reads and displays OK
- isql                    OK
- pyodbc                  OK
- django-pyodbc           fail

Details in attached file.

Original comment by gimbus...@gmail.com on 13 Aug 2011 at 9:29

Attachments:

• issue.txt

[GoogleCodeExporter]

I have the same problem, but I don't use django-pyodbc. I use freetds-0.91 
unixodbc-2.2.14 and pyodbc 2.1.7 on debian wheezy and connects to MSSQL SERVER 
2008R2.
However isql shows fine.

Original comment by wWolfov...@gmail.com on 20 Aug 2012 at 2:13

[GoogleCodeExporter]

I confirm this bug, I've the same problem with a view in Sybase 11.0.1.2324.

I have a view, with two varchar columns. the CHAR character set encoding is 
windows-1252.

When I use pyodbc 3.0.6 and unicode_results=True in connect() method, I have 
this exception when I retrieve a line with a character encoded with 
windows-1252:

   [HY003] [FreeTDS][SQL Server]Program type out of range (0) (SQLGetData)

With unicode_results=False, I retrieve correctly the data, but with the 
windows-1252 encoding.

Thanks for your feedback.

Original comment by gml...@gmail.com on 18 Sep 2012 at 1:43

[GoogleCodeExporter]

We're going to have to involve the FreeTDS developers - the value is being 
written correctly into the database, but retrieving it fails:

[000000.101492]
python          7FFF79F56180 ENTER SQLGetData
        SQLHSTMT          0x7fc828e1af60
        SQLUSMALLINT      1
        SQLSMALLINT       -8 (SQL_C_WCHAR)
        SQLPOINTER        0x7fff5157b860
        SQLLEN            1024
        SQLLEN          * 0x7fff5157b820

[000000.101532]
python          7FFF79F56180 EXIT  SQLGetData with return code 1 
(SQL_SUCCESS_WITH_INFO)
        SQLHSTMT          0x7fc828e1af60
        SQLUSMALLINT      1
        SQLSMALLINT       -8 (SQL_C_WCHAR)
        SQLPOINTER        0x7fff5157b860
                  | cc_Name: ?????, ???                      |
        SQLLEN            1024
        SQLLEN          * 0x7fff5157b820 (76)

Original comment by mkleehammer on 27 Sep 2012 at 10:42

[GoogleCodeExporter]

I've joined the FreeTDS mailing list and posted a question.  Hopefully we'll 
hear something soon.

I've created a temporary branch issue153 with a unit test test_issue153 until 
this is resolved.

Original comment by mkleehammer on 27 Sep 2012 at 11:01

[GoogleCodeExporter]

Hi, i had the same issue using FreeTDS 0.82 on my Debian squeeze using pyodbc 
when connection to an MSSQL 2005 server.

I could solve this by building FreeTDS 0.91 from sources. I changed the driver 
in my odbcinst.ini to new self-build location and left all connection options 
untouched. Everything is working fine, no character encoding problems even not 
for Cyrillic or Chinese characters.

So this seems to be an FreeTDS issue.

Original comment by dan...@widerin.net on 9 Mar 2013 at 12:34