Issue 292: FreeTDS->unixODBC->pyodbc character problem on linux
Status:  New
Owner: ----
Reported by alberto....@gmail.com, Oct 19, 2012
Hi,

 this may be similar to  issue 222 
 https://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_US.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.

Oct 19, 2012
#1 alberto....@gmail.com
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
Nov 26, 2012
#2 szybal...@gmail.com
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/6ded8a8397e7d0692fe522ac2140e5d41d1d22c6