FreeTDS->unixODBC->pyodbc character problem on linux

[GoogleCodeExporter]

Hi,

 this may be similar to issue 222
 http://code.google.com/p/pyodbc/issues/detail?id=222

 but cannot find a solution

I am trying to connect to a MS SQL 2008 database from a django application 
running on Apache 2 on a Ubuntu 11.04 box

the database is reporting character iso_1 and collation Modern_Spanish_CI_AS 

What steps will reproduce the problem?
1. I installed required packages

sudo apt-get install freetds-bin
sudo apt-get install freetds-common
sudo apt-get install tdsodbc
sudo pip install pyodbc
sudo pip install 
svn+http://django-pyodbc.googlecode.com/svn/trunk/#django-pyodbc

2. I configured the ODBC/FreeTDS environment

/etc/freetds/freetds.conf 
[SQLServer]
    host = 192.168.0.20
        instance = sqllimspro
    tds version = 8.0

/etc/odbc.ini 
[SQLServer] 
Description = MS SQL Server
Driver = /usr/lib/odbc/libtdsodbc.so 
ServerName = SQLServer
Server = 192.168.0.20\sqllimspro
User = 
Password = 
ReadOnly = No
Database = LilaCV

/etc/odbcinst.ini 
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
client charset = utf-8

3. Test freetds, looks Ok

tsql -S SQLServer -U * -P *
locale is 
"LC_CTYPE=en_US.UTF-8;LC_NUMERIC=es_ES.UTF-8;LC_TIME=es_ES.UTF-8;LC_COLLATE=en_U
S.UTF-8;LC_MONETARY=es_ES.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=es_ES.UTF-8;LC_
NAME=es_ES.UTF-8;LC_ADDRESS=es_ES.UTF-8;LC_TELEPHONE=es_ES.UTF-8;LC_MEASUREMENT=
es_ES.UTF-8;LC_IDENTIFICATION=es_ES.UTF-8"
locale charset is "UTF-8"
1> 

4. Test isql

isql SQLServer 
SQL> select TOP 1 nombre_comercial from clientes_industria
NESTLE ESPAÑA S.L.

5. testing pyodbc

import pyodbc
conn = 
pyodbc.connect('DSN=SQLServer;UID=Lilacv;PWD=Lila1991',convert_unicode=False)
cursor = conn.cursor()
cursor.execute("SELECT TOP 1 nombre_comercial FROM clientes_industria")
rows = cursor.fetchall()
for row in rows:
    print row

What is the expected output? What do you see instead?

NESTLE ESPAÑA S.L.
NESTLE ESPA?A S.L.

this is happenning with all spanish special characters (unicode?)

What version of the product are you using? On what operating system?

ubuntu 11.04
Linux SRVLILACV 2.6.38-8-server #42-Ubuntu SMP Mon Apr 11 03:49:04 UTC 2011 
x86_64 x86_64 x86_64 GNU/Linux

Python version
Python 2.7.1+ (r271:86832, Sep 27 2012, 21:12:17) 
[GCC 4.5.2] on linux2

>>> import pyodbc
>>> pyodbc.version
'3.0.6'

Please provide any additional information below.

Original issue reported on code.google.com by alberto....@gmail.com on 19 Oct 2012 at 9:25

[GoogleCodeExporter]

Hi,

 If I use

 conn = pyodbc.connect('DSN=SQLServer;UID=user;PWD=pass',unicode_results=True)

when I do

rows = cursor.fetchall()

I get

pyodbc.Error: ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range 
(0) (SQLGetData)')

Alberto

Original comment by alberto....@gmail.com on 19 Oct 2012 at 12:02

[GoogleCodeExporter]

pyodbc 2.1.8 does not have this problem.
We recently upgraded to Debian squeeze and installed latest pyodbc.
I don't have any international character encoding other then what is default 
for US standard SQL version.

>>> cnxn=pyodbc.connect("DSN=SQLServer2008;UID=xxxx;PWD=xxxx")
>>> cursor = cnxn.cursor()
>>> cursor.execute('assp_ReturnChecks @begin_date="11/21/2012", 
@end_date="11/21/2012"')
<pyodbc.Cursor object at 0x25388d0>
>>> a=cursor.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range 
(0) (SQLGetData)')

While looking for a solution I found this, but I'm not sure if that actually is 
the solution. For now I'm reverting back to pyodbc 2.1.8 to fix this problem. 
IF you could let me know which version this will be fixed in, and which were 
affected I would greatly appreciate it.

https://github.com/mkleehammer/pyodbc/commit/6ded8a8397e7d0692fe522ac2140e5d41d1
d22c6

Original comment by szybal...@gmail.com on 26 Nov 2012 at 8:18