unicode parameters on pyodbc -> unixodbc -> freetds

[ghost]

I am using Ubuntu 9.04

I have installed the following package versions:

unixodbc and unixodbc-dev: 2.2.11-16build3
tdsodbc: 0.82-4
libsybdb5: 0.82-4
freetds-common and freetds-dev: 0.82-4

I have configured /etc/unixodbc.ini like this:

[FreeTDS]
Description             = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout               = 
CPReuse         = 
UsageCount              = 2

I have configured /etc/freetds/freetds.conf like this:

[global]
    tds version = 8.0
    client charset = UTF-8

I have grabbed pyodbc revision 31e2fae from http://github.com/mkleehammer/pyodbc and installed it using "python setup.py install"

I have a windows machine with Microsoft SQL Server 2000 installed on my local network, up and listening on the local ip address 10.32.42.69. I have an empty database created with name "Common". I have the user "sa" with password "secret" with full priviledges.

I am using the following python code to setup the connection:

import pyodbc
odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS"
con = pyodbc.connect(s)
cur = con.cursor()
cur.execute('''
CREATE TABLE testing (
    id INTEGER NOT NULL IDENTITY(1,1), 
    name NVARCHAR(200) NULL, 
    PRIMARY KEY (id)
)
    ''')
con.commit()

Everything WORKS up to this point. I have used SQLServer's Enterprise Manager on the server and the new table is there. Now I want to insert some data on the table.

cur = con.cursor()
cur.execute('INSERT INTO testing (name) VALUES (?)', (u'áéí',))

That fails!! Seems like pyodbc won't accept a unicode object. Here's the error I get:

pyodbc.Error: ('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)'

Since my freetds client is configured to use UTF-8 as above, I thought I could solve by encoding data to UTF-8. That gives no error, but then I get back strange data when I query. pyodbc returns unicode strings, and decoded with the wrong encoding so the chars are wrong.

If I can't insert an unicode string, why do I get those back? And wrong?

[mdobrzanski]

Have you tried querying with tsql and isql?

This may not be pyodbc issue. I had this problem with selecting with unicode parameter and I've installed from sources freetds 0.91, unixODBX 2.3.0 and latest pyodbc.

quoting http://groups.google.com/group/sqlalchemy/browse_thread/thread/971e068032d51dd5 this is what has worked for me on ubuntu 10.04

unixODBC

First I removed system packages unixODBC and unixODBC-dev (names may
vary by Linux distro). Then

wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
tar zxvf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0
./configure
make
sudo make install

As root ensure that /usr/local/etc/odbcinst.ini has the following
entries:

[FreeTDS]
Driver          = /usr/local/lib/libtdsodbc.so

[SQLServer]
Driver          = /usr/local/lib/libtdsodbc.so

The second entry is for aesthetics only - so you can use "SQLServer"
in your connection strings. Odbc.ini file is not important if you use
fully qualified host names in your connection string but at your
option you may configure DSNs there.

FreeTDS

Also uninstall any system packages that you may have for this, then:

wget http://www.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar xvf freetds-stable.tgz
cd freetds-0.91
./configure --with-unixodbc=/usr/local  --enable-msdblib --with-
tdsver=8.0
make
sudo make install

Remove or otherwise disable /etc/freetds.conf if any. Modify /usr/
local/etc/freetds.conf to read as follows:

[global]
        # TDS protocol version
        tds version = 8.0
        client charset = UTF-8
        # This is probably not needed...
        text size = 64512
# Typical SQLServer
[server_name]
        host = hostname.somedomain
        port = 2431
        tds version = 8.0

At this point you should be able to login successfully using:

tsql -S server_name -U username -P password
or
isql Database username password

Pyodbc

git clone https://github.com/mkleehammer/pyodbc.git
python setup.py bdist_egg
cd dist
easy_install pyodbc-3.0.2_beta01-py2.6-linux-i686.egg  # filename may vary based on architecture

#pyodbc_sand.py

import pyodbc
con = pyodbc.connect('DSN=Northwind; UID=vte; PWD=vte')
sqlstr = "select * from Customers where CustomerID = (?)"

cur = con.cursor()
q = cur.execute(sqlstr, u'ĄŁÓ$!')
res = q.fetchall()
print res
#output: [(u'\u0104\u0141\xd3$!', u'URL Encoders', None, None, None, None, None, None, None, None, None)]

[maparent]

Look at my pull request, I suspect it's relevant. I have had the same issue with Virtuoso and solved it with
settings['extra_compile_args'].append('-DSQL_WCHART_CONVERT=1')

[mkleehammer]

If you use the default unixODBC build (2-byte UCS2) this should now work on 3.0.10 (not built yet). Can you try the master branch and confirm?