PyODBCConnector, possible wrong assumption re Unicode in bind parameters

[Victor Olex]

Using SQLAlchemy 0.7.2 with pyodbc 2.1.9, FreeTDS 0.91, unixODBC 2.3.0
and SQL Server 2008 I find that the supports_unicode_bind may be
incorrectly set to False in the PyODBCConnector.initialize. As a
result a unicode parameter gets encoded as str and to make matters
worse the value gets silently overridden with empty Unicode string
(u'').

Consider a simple table (IDENTITY, NTEXT, VARCHAR(255)) with one
record:
ID, col1, col2
1, 'Łódź', 'abc'.

We will update existing value in col1 to 'Łódź!'.

>>> from sqlalchemy import Column, Sequence, create_engine
>>> from sqlalchemy.types import UnicodeText, Integer, VARCHAR
>>> from sqlalchemy.orm import sessionmaker
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>> Base = declarative_base()
>>> metadata = Base.metadata
>>>
>>> class A(Base):
... __tablename__ = 'A'
... id = Column(u'ID', Integer, Sequence('A_PK'),
primary_key=True)
... col1 = Column(u'col1', UnicodeText())
... col2 = Column(u'col2', VARCHAR(255))
...
>>> e = create_engine('mssql://user:pwd@sqlserverhost:2431/MYDB?driver=FreeTDS&TDS_Version=8.0', echo=True)
>>> Session=sessionmaker()
>>> s = Session(bind=e)
>>> lodz = u'\u0141\xf3d\u017a'
>>> oa = s.query(A).one()
2011-09-07 17:22:25,260 INFO sqlalchemy.engine.base.Engine SELECT
user_name() as user_name;
2011-09-07 17:22:25,261 INFO sqlalchemy.engine.base.Engine ()
2011-09-07 17:22:25,270 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'

2011-09-07 17:22:25,271 INFO sqlalchemy.engine.base.Engine
(u'SPEED_IT',)
2011-09-07 17:22:25,291 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine SELECT [A].
[ID]
AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2]
FROM [A]
2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine ()
>>> oa.col1
u'\u0141\xf3d\u017a'
>>> oa.col2
'abc'
>>> oa.col1 = u'\u0141\xf3d\u017a!'
>>> s.commit()
2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine UPDATE [A]
SET
col1=? WHERE [A].[ID] = ?
2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine
('\xc5\x81\xc3\xb3d\xc5\xba!', 1)
2011-09-07 17:23:17,061 INFO sqlalchemy.engine.base.Engine COMMIT
>>> oa.col1
2011-09-07 17:23:24,226 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine SELECT [A].
[ID]
AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2]
FROM [A]
WHERE [A].[ID] = ?
2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine (1,)
u''

Using a patched initialize method with the supports_unicode_binds line
#110 removed the parameter gets passed as Unicode and the database
updates correctly as does the in memory object. Different version
combinations of pyodbc, FreeTDS and SQL may likely yield a different
result so unless a deterministic factor is found I would like to
propose adding parameter bind_unicode to dialect class and connection
url.

Regards and respect,

Victor Olex
http://linkedin.com/in/victorolex
http://twitter.com/agilevic

[Michael Bayer]

I can't actually make that string work at all with FreeTDS, but I am on 0.82. If I turn on Python unicodes with FreeTDS 0.82, which until recently was the FreeTDS release for years, everything breaks immediately - the CREATE TABLE statements won't even work, as you can see below just the strings u'A', u'dbo' blow it up:

sqlalchemy.exc.DBAPIError: (Error) ('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)') 'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?' (u'A', u'dbo')

You can be assured that the code you see in pyodbc.py is not by accident - dozens of hours went into making this thing work with Pyodbc + FreeTDS, and I would vastly prefer that it just accept u'' strings - but on 0.82, it does not.

So I'm just thrilled that A. FreeTDS has apparently broken compatibility with all of that effort and B. I can't barely even get FreeTDS 0.91 to work, nor can I C. get pyodbc 2.1.9 to build.

FreeTDS 0.91 doesn't appear to work for me period. Your exact query *does* work, but if I try to create or drop tables, I get either:

MemoryError:

or an erroneously blank result set, when trying to query from INFORMATION_SCHEMA.COLUMNS, depending on how I set up that flag.

So I can't really test or do anything with FreeTDS 0.91.

Can you please try a "metadata.drop_all()" and "metadata.create_all()" for me and tell me if it works as expected, both with and without the patch ?

Your flag is not a big deal but the much more ominous issue is a whole new set of users installing 0.91 and not being able to do simple checks for table existence.

What OS you're on would be helpful here as well, as it appears I'm at least going to have to test from a linux VM to a windows VM to even get this going.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Michael Bayer]

The ticket for SQLAlchemy is:

http://www.sqlalchemy.org/trac/ticket/2273

For Pyodbc I've opened:

http://code.google.com/p/pyodbc/issues/detail?id=209
http://code.google.com/p/pyodbc/issues/detail?id=210

as you can see, issue 210 is quite serious. Would be curious what results you get for the script there.

[Victor Olex]

I never for a moment thought that your change was thoughtless. To the
contrary, I have huge respect for SQLAlchemy. I will try to test the
drop_all and your pyodbc issue with my setup and to report here later
today.

Meanwhile, I can tell you how to build the stack because it is a bit
tricky given certain package issues. I chose to build unixODBC and
FreeTDS and pyodbc from sources into /usr/local.

1. 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.

2. 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

3. pyodbc

Pyodbc package on pypi is currently broken in that it is missing a
utils and web folders from sources and does not build. There is a bug
for this (with my comments too) at http://code.google.com/p/pyodbc/issues/detail?id=192.
There are two ways around it but before you start (and at runtime)
make sure that /usr/local/lib is in LD_LIBRARY_PATH (or add it
permanently system-wide using ldconfig).

export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH

3.1 Quick way

pip install https://github.com/mkleehammer/pyodbc/zipball/2.1.9

This will result in pyodbc-2.1.0-unsupported out of 2.1.9 code base
installed, which looks ugly and may potentially confuse other
packages, which check its version. But it works fine. The reason for
this is that the github code version does not hat PKG-INFO file.

3.2 Longer way, which I followed

3.2.1 Get official package and git sources

wget http://pyodbc.googlecode.com/files/pyodbc-2.1.9.zip
unzip pyodbc-2.1.9.zip
wget --no-check-certificate -O git-pyodbc-2.1.9.zip
https://github.com/mkleehammer/pyodbc/zipball/2.1.9
unzip git-pyodbc-2.1.9.zip

3.2.2 Copy missing utils and web folders to the packaged version

cp -R mkleehammer-pyodbc-e3c95dc/utils/ pyodbc-2.1.9/
cp -R mkleehammer-pyodbc-e3c95dc/web/ pyodbc-2.1.9/

3.2.3 Build and install

python setup.py bdist_egg
cd dist
easy_install pyodbc-2.1.9-py2.6-linux-i686.egg # filename may vary
based on architecture

At run time be sure to have the LD_LIBRARY_PATH and TDSVER=8.0
variables set. The latter is not needed if you put the same into
connection string as I have in the example above.

I hope this helps.

Victor Olex
http://linkedin.com/in/victorolex
http://twitter.com/agilevic

On Sep 7, 11:53 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> The ticket for SQLAlchemy is:
>
> http://www.sqlalchemy.org/trac/ticket/2273
>
> For Pyodbc I've opened:
>

> http://code.google.com/p/pyodbc/issues/detail?id=209http://code.google.com/p/pyodbc/issues/detail?id=210

> >> For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Sep 8, 2011, at 9:37 AM, Victor Olex wrote:

> I never for a moment thought that your change was thoughtless. To the
> contrary, I have huge respect for SQLAlchemy. I will try to test the
> drop_all and your pyodbc issue with my setup and to report here later
> today.

thanks ! Unfortunately I've tested this some more and things are looking very, very bad. For us to support 0.91, we'd need to figure out how to get all of our "table exists" functions to work. If you look at http://www.sqlalchemy.org/trac/ticket/2273, I've now added a patch that detects 0.82 vs. 0.91 and sets the flag, but you can see that we can't send u'' strings when we query INFORMATION_SCHEMA still - literally, the number of characters present in one of the bind parameters changes the behavior. So there is something very strange and arbitrary (seems basically like it's just making guesses about datatypes) going on with the internals of FreeTDS, and I'm not optimistic about being able to get clear answers from their list.

Would you have any resources to evaluate the test cases on that ticket , both are now against pure PyODBC 2.1.9? Without being able to query information schema, none of our unit tests can run period with 0.91 - I need a reliable way to do so, hopefully without losing support for table names that contain non-ascii characters. A lot of adjustments to the MSSQL dialect and testing will be needed.

[Victor Olex]

Pyodbc issue 209 works fine in my setup. I think the key thing is
matching SQL Server version with the correct TDS protocol version and
correct FreeTDS version. Also with regards to your Mac testing, check
if you have the libiconv installed and that FreeTDS is built with it.
http://www.freetds.org/userguide/config.htm

On Sep 8, 10:32 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 8, 2011, at 9:37 AM, Victor Olex wrote:
>
> > I never for a moment thought that your change was thoughtless. To the
> > contrary, I have huge respect for SQLAlchemy. I will try to test the
> > drop_all and your pyodbc issue with my setup and to report here later
> > today.
>

> thanks !     Unfortunately I've tested this some more and things are looking very, very bad.    For us to support 0.91, we'd need to figure out how to get all of our "table exists" functions to work.   If you look athttp://www.sqlalchemy.org/trac/ticket/2273, I've now added a patch that detects 0.82 vs. 0.91 and sets the flag, but you can see that we can't send u'' strings when we query INFORMATION_SCHEMA still - literally, the number of characters present in one of the bind parameters changes the behavior.   So there is something very strange and arbitrary (seems basically like it's just making guesses about datatypes) going on with the internals of FreeTDS, and I'm not optimistic about being able to get clear answers from their list.    

[Michael Bayer]

On Sep 8, 2011, at 11:37 AM, Victor Olex wrote:

> Pyodbc issue 209 works fine in my setup.

that is very strange ? There are files missing from the .zip. If you installed from the zip I don't see how it built for you. Here's the original issue:

http://code.google.com/p/pyodbc/issues/detail?id=192

> I think the key thing is
> matching SQL Server version with the correct TDS protocol version and
> correct FreeTDS version.

I use tds version 8.0 for this particular DSN and that is working fine in production with FreeTDS 0.82, on both linux and OSX platforms. These issues have all been introduced with FreeTDS 0.91. Here, I tried 7.2 and got slightly better results, though unicode round trips still fail when Python unicodes are passed. PyODBC still dies with "MemoryError" if I attempt to query for a table that already exists.

> Also with regards to your Mac testing, check
> if you have the libiconv installed and that FreeTDS is built with it.
> http://www.freetds.org/userguide/config.htm

yup that's in my configure:

checking for iconv... yes
checking how to link with libiconv... -liconv
checking for iconv declaration... install-shextern size_t iconv (iconv_t cd, char * *inbuf, size_t *inbytesleft, char * *outbuf, size_t *outbytesleft);

>
> On Sep 8, 10:32 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Sep 8, 2011, at 9:37 AM, Victor Olex wrote:
>>
>>> I never for a moment thought that your change was thoughtless. To the
>>> contrary, I have huge respect for SQLAlchemy. I will try to test the
>>> drop_all and your pyodbc issue with my setup and to report here later
>>> today.
>>
>> thanks ! Unfortunately I've tested this some more and things are looking very, very bad. For us to support 0.91, we'd need to figure out how to get all of our "table exists" functions to work. If you look athttp://www.sqlalchemy.org/trac/ticket/2273, I've now added a patch that detects 0.82 vs. 0.91 and sets the flag, but you can see that we can't send u'' strings when we query INFORMATION_SCHEMA still - literally, the number of characters present in one of the bind parameters changes the behavior. So there is something very strange and arbitrary (seems basically like it's just making guesses about datatypes) going on with the internals of FreeTDS, and I'm not optimistic about being able to get clear answers from their list.
>>
>> Would you have any resources to evaluate the test cases on that ticket , both are now against pure PyODBC 2.1.9? Without being able to query information schema, none of our unit tests can run period with 0.91 - I need a reliable way to do so, hopefully without losing support for table names that contain non-ascii characters. A lot of adjustments to the MSSQL dialect and testing will be needed.
>

[Victor Olex]

I know of those issues with pyodbc package. Michael, please read my
first response where I wrote how to build the unixODBC, FreeTDS and
pyodbc stack. I gave this detail for a reason - i.e. that you can
replicate my built.

By the way I did sqlalchemy level testing as promised. Predictably,
the DDL using both patched and non-patched PyODBCCOnnector executes
correctly. The only difference is that parameters are bound to unicode
strings in the former. This actually works exactly as I would expect
it i.e. accepts plain string for Unicode fields. Most fields in
COLUMNS table are sysdate, which generally equates to nvarchar(128).
Here's complete screenshot for you.

### Without patch:

In [29]: speed.metadata.create_all()
2011-09-08 12:17:01,094 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:17:01,094 INFO sqlalchemy.engine.base.Engine ('A',
'dbo')
2011-09-08 12:17:01,099 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:17:01,099 INFO sqlalchemy.engine.base.Engine ('B',
'CMBS')
2011-09-08 12:17:01,102 INFO sqlalchemy.engine.base.Engine
CREATE TABLE [A] (
[ID] INTEGER NOT NULL IDENTITY(1,1),
col1 NTEXT NULL,
col2 VARCHAR(255) NULL,
PRIMARY KEY ([ID])
)


2011-09-08 12:17:01,102 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:17:01,152 INFO sqlalchemy.engine.base.Engine COMMIT
2011-09-08 12:17:01,154 INFO sqlalchemy.engine.base.Engine
CREATE TABLE [CMBS].[B] (
[ID] INTEGER NOT NULL IDENTITY(1,1),
col1 NTEXT NULL,
PRIMARY KEY ([ID])
)


2011-09-08 12:17:01,154 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:17:01,184 INFO sqlalchemy.engine.base.Engine COMMIT

In [30]: speed.metadata.drop_all()
2011-09-08 12:17:04,729 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:17:04,729 INFO sqlalchemy.engine.base.Engine ('B',
'CMBS')
2011-09-08 12:17:04,734 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:17:04,734 INFO sqlalchemy.engine.base.Engine ('A',
'dbo')
2011-09-08 12:17:04,737 INFO sqlalchemy.engine.base.Engine
DROP TABLE [CMBS].[B]
2011-09-08 12:17:04,737 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:17:04,825 INFO sqlalchemy.engine.base.Engine COMMIT
2011-09-08 12:17:04,827 INFO sqlalchemy.engine.base.Engine
DROP TABLE [A]
2011-09-08 12:17:04,827 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:17:04,850 INFO sqlalchemy.engine.base.Engine COMMIT

### With patch:

In [5]: speed.metadata.drop_all()
2011-09-08 12:20:35,596 INFO sqlalchemy.engine.base.Engine SELECT
user_name() as user_name;
2011-09-08 12:20:35,597 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:20:35,599 INFO sqlalchemy.engine.base.Engine

SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'

2011-09-08 12:20:35,599 INFO sqlalchemy.engine.base.Engine (u'XXXX',)
2011-09-08 12:20:35,608 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:20:35,608 INFO sqlalchemy.engine.base.Engine (u'B',
u'CMBS')
2011-09-08 12:20:35,612 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:20:35,612 INFO sqlalchemy.engine.base.Engine (u'A',
u'dbo')

In [6]: speed.metadata.create_all()
2011-09-08 12:20:50,391 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:20:50,391 INFO sqlalchemy.engine.base.Engine (u'A',
u'dbo')
2011-09-08 12:20:50,395 INFO sqlalchemy.engine.base.Engine SELECT

[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].
[COLLATION_NAME]

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

2011-09-08 12:20:50,395 INFO sqlalchemy.engine.base.Engine (u'B',
u'CMBS')
2011-09-08 12:20:50,397 INFO sqlalchemy.engine.base.Engine
CREATE TABLE [A] (
[ID] INTEGER NOT NULL IDENTITY(1,1),
col1 NTEXT NULL,
col2 VARCHAR(255) NULL,
PRIMARY KEY ([ID])
)


2011-09-08 12:20:50,397 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:20:50,487 INFO sqlalchemy.engine.base.Engine COMMIT
2011-09-08 12:20:50,489 INFO sqlalchemy.engine.base.Engine
CREATE TABLE [CMBS].[B] (
[ID] INTEGER NOT NULL IDENTITY(1,1),
col1 NTEXT NULL,
PRIMARY KEY ([ID])
)


2011-09-08 12:20:50,490 INFO sqlalchemy.engine.base.Engine ()
2011-09-08 12:20:50,514 INFO sqlalchemy.engine.base.Engine COMMIT

[Michael Bayer]

Hi Victor -

Since you're there, do you have any luck actually running unit tests ? The test in particular here is:

./sqla_nose.py -v test.sql.test_types:UnicodeTest --dburi=mssql+pyodbc://user:pass@dsn

Also, on the Mac, iODBC is the standard ODBC product. unixODBC can be built though in the past I've found this to have problems - I'll see if I can look into it again if I have time.

I have no doubt that this works on linux so the path to my getting this patch committed is to get everything installed on a linux VM, and getting the tests to run as well as they did before at least on that platform.

For OSX I'm not sure where that will lead - if we need to put a doc on the site saying, "you need to replace OSX's standard ODBC install", then that will be that, but would need to check that all out first.

Also sorry I missed that pyodbc ticket 192 was yours. Disturbing that there's no response from the maintainer ?

[Victor Olex]

Unfortunately I don't have access to a blank database and I took the
chance and ran your tests on a non-empty database. Tests are mostly
good: 5/7 pass. You should know that I used current trunk and simply
commented out the line, which resets the supports_unicode_binds but it
should be equivalent in effect to your patch. Without the patch all
tests fail. Below is pretty verbose result for you.

I hope this helps,

Victor

./sqla_nose.py -v test.sql.test_types:UnicodeTest --dburi="mssql
+pyodbc://XXXX:xxx@xxx:2431/X?
driver=SQLServer&port=2431&TDS_Version=8.0" --log-
debug=sqlalchemy.orm.mapper --log-debug=sqlalchemy.pool --log-
debug=sqlalchemy.engine
DEBUG:sqlalchemy.pool.QueuePool:Created new connection
<pyodbc.Connection object at 0xa3fdfa0>

INFO:sqlalchemy.engine.base.Engine:SELECT user_name() as user_name;

INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('user_name',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'SPEED_IT', )

INFO:sqlalchemy.engine.base.Engine:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'

INFO:sqlalchemy.engine.base.Engine:(u'SPEED_IT',)

DEBUG:sqlalchemy.engine.base.Engine:Col ('default_schema_name',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'dbo', )
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool

INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].
[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

INFO:sqlalchemy.engine.base.Engine:(u'unicode_table', u'dbo')
DEBUG:sqlalchemy.engine.base.Engine:Col ('TABLE_SCHEMA', 'TABLE_NAME',
'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE', 'ORDINAL_POSITION',
'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION', 'NUMERIC_SCALE',
'COLUMN_DEFAULT', 'COLLATION_NAME')
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE unicode_table (
id INTEGER NOT NULL IDENTITY(1,1),
unicode_varchar NVARCHAR(250) NULL,
unicode_text NTEXT NULL,
PRIMARY KEY (id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
test.sql.test_types.UnicodeTest.test_blank_strings ...
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_table
(unicode_varchar) OUTPUT inserted.id VALUES (?)
INFO:sqlalchemy.engine.base.Engine:(u'',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (1, )
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT
unicode_table.unicode_varchar
FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('unicode_varchar',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'', )
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
ok
test.sql.test_types.UnicodeTest.test_ignoring_unicode_error ...
DEBUG:sqlalchemy.pool.QueuePool:Created new connection
<pyodbc.Connection object at 0xa4490e0>

INFO:sqlalchemy.engine.base.Engine:SELECT user_name() as user_name;

INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('user_name',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'SPEED_IT', )

INFO:sqlalchemy.engine.base.Engine:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'

INFO:sqlalchemy.engine.base.Engine:(u'SPEED_IT',)

DEBUG:sqlalchemy.engine.base.Engine:Col ('default_schema_name',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'dbo', )
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool

INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].
[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

INFO:sqlalchemy.engine.base.Engine:(u'unicode_err_table', u'dbo')
DEBUG:sqlalchemy.engine.base.Engine:Col ('TABLE_SCHEMA', 'TABLE_NAME',
'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE', 'ORDINAL_POSITION',
'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION', 'NUMERIC_SCALE',
'COLUMN_DEFAULT', 'COLLATION_NAME')
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE unicode_err_table (
sort INTEGER NULL,
plain_varchar_no_coding_error VARCHAR(248) NULL
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_err_table
(sort, plain_varchar_no_coding_error) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT unicode_err_table.sort,
unicode_err_table.plain_varchar_no_coding_error
FROM unicode_err_table
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('sort',
'plain_varchar_no_coding_error')
DEBUG:sqlalchemy.engine.base.Engine:Row (1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_err_table
(sort, plain_varchar_no_coding_error) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(2, u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb')
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT unicode_err_table.sort,
unicode_err_table.plain_varchar_no_coding_error
FROM unicode_err_table ORDER BY unicode_err_table.sort
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('sort',
'plain_varchar_no_coding_error')
DEBUG:sqlalchemy.engine.base.Engine:Row (1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
DEBUG:sqlalchemy.engine.base.Engine:Row (2, 'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m?a r
\xe9veill\xe9. Elle disait: \xab S?il vous pla\xeet? dessine-moi un
mouton! ')
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool

INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].
[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

INFO:sqlalchemy.engine.base.Engine:(u'unicode_err_table', u'dbo')
DEBUG:sqlalchemy.engine.base.Engine:Col ('TABLE_SCHEMA', 'TABLE_NAME',
'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE', 'ORDINAL_POSITION',
'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION', 'NUMERIC_SCALE',
'COLUMN_DEFAULT', 'COLLATION_NAME')
DEBUG:sqlalchemy.engine.base.Engine:Row (u'dbo', u'unicode_err_table',
u'sort', 'YES', u'int', 1, None, 10, 0, None, None)
INFO:sqlalchemy.engine.base.Engine:
DROP TABLE unicode_err_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
FAIL
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
test.sql.test_types.UnicodeTest.test_native_unicode ... FAIL
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
test.sql.test_types.UnicodeTest.test_round_trip ...
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_table
(unicode_varchar, unicode_text) OUTPUT inserted.id VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(u'Alors vous imaginez ma surprise,
au lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb', u'Alors vous imaginez ma surprise, au lever du jour,
quand une dr\xf4le de petite voix m\u2019a r\xe9veill\xe9. Elle
disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un mouton!
\xbb')
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (2, )
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT unicode_table.id,
unicode_table.unicode_varchar, unicode_table.unicode_text
FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('id', 'unicode_varchar',
'unicode_text')
DEBUG:sqlalchemy.engine.base.Engine:Row (2, u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb', u'Alors vous imaginez ma surprise, au
lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb')
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
ok
test.sql.test_types.UnicodeTest.test_round_trip_executemany ...
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_table
(unicode_varchar, unicode_text) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:((u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb', u'Alors vous imaginez ma surprise, au
lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb'), (u'Alors vous imaginez ma surprise, au lever du jour,
quand une dr\xf4le de petite voix m\u2019a r\xe9veill\xe9. Elle
disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un mouton!
\xbb', u'Alors vous imaginez ma surprise, au lever du jour, quand une
dr\xf4le de petite voix m\u2019a r\xe9veill\xe9. Elle disait: \xab S
\u2019il vous pla\xeet\u2026 dessine-moi un mouton! \xbb'))
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT unicode_table.id,
unicode_table.unicode_varchar, unicode_table.unicode_text
FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('id', 'unicode_varchar',
'unicode_text')
DEBUG:sqlalchemy.engine.base.Engine:Row (3, u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb', u'Alors vous imaginez ma surprise, au
lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb')
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
ok
test.sql.test_types.UnicodeTest.test_unicode_warnings ...
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
ok
test.sql.test_types.UnicodeTest.test_union ...
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:INSERT INTO unicode_table
(unicode_varchar, unicode_text) OUTPUT inserted.id VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(u'Alors vous imaginez ma surprise,
au lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb', u'Alors vous imaginez ma surprise, au lever du jour,
quand une dr\xf4le de petite voix m\u2019a r\xe9veill\xe9. Elle
disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un mouton!
\xbb')
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (5, )
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:SELECT
unicode_table.unicode_varchar
FROM unicode_table UNION SELECT unicode_table.unicode_varchar
FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('unicode_varchar',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb', )
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:DELETE FROM unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
ok
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> checked out from pool

INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].
[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

INFO:sqlalchemy.engine.base.Engine:(u'unicode_table', u'dbo')
DEBUG:sqlalchemy.engine.base.Engine:Col ('TABLE_SCHEMA', 'TABLE_NAME',
'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE', 'ORDINAL_POSITION',
'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION', 'NUMERIC_SCALE',
'COLUMN_DEFAULT', 'COLLATION_NAME')
DEBUG:sqlalchemy.engine.base.Engine:Row (u'dbo', u'unicode_table',
u'id', 'NO', u'int', 1, None, 10, 0, None, None)
INFO:sqlalchemy.engine.base.Engine:
DROP TABLE unicode_table
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <pyodbc.Connection object
at 0xa3fdfa0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Closing connection <pyodbc.Connection
object at 0xa4490e0>
DEBUG:sqlalchemy.pool.QueuePool:Exception closing connection
<pyodbc.Connection object at 0xa4490e0>
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
DEBUG:sqlalchemy.pool.QueuePool:Closing connection <pyodbc.Connection
object at 0xa3fdfa0>
DEBUG:sqlalchemy.pool.QueuePool:Exception closing connection
<pyodbc.Connection object at 0xa3fdfa0>
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating
INFO:sqlalchemy.pool.QueuePool:Pool disposed. Pool size: 5
Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
INFO:sqlalchemy.pool.QueuePool:Pool recreating

======================================================================
FAIL: test.sql.test_types.UnicodeTest.test_ignoring_unicode_error
----------------------------------------------------------------------
Traceback (most recent call last):
File "/opt/home/vo63573/satest/lib/python2.6/site-packages/nose/
case.py", line 197, in runTest
self.test(*self.arg)
File "<string>", line 1, in <lambda>
File "/opt/home/vo63573/satest/src/sqlalchemy/./test/lib/
testing.py", line 53, in decorate
return fn(*args, **kw)
File "/opt/home/vo63573/satest/src/sqlalchemy/./test/sql/
test_types.py", line 833, in test_ignoring_unicode_error
eq_(x, unicodedata)
File "/opt/home/vo63573/satest/src/sqlalchemy/./test/lib/
testing.py", line 499, in eq_
assert a == b, msg or "%r != %r" % (a, b)
AssertionError: u'Alors vous imaginez ma surprise, au lever du jour,
quand une drle de petite voix m?a rveill. Elle disait: S?il vous
plat? dessine-moi un mouton! ' != u'Alors vous imaginez ma surprise,
au lever du jour, quand une dr\xf4le de petite voix m\u2019a r\xe9veill
\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026 dessine-moi un
mouton! \xbb'
-------------------- >> begin captured logging << --------------------
sqlalchemy.pool.QueuePool: DEBUG: Created new connection
<pyodbc.Connection object at 0xa4490e0>
sqlalchemy.engine.base.Engine: INFO: SELECT user_name() as user_name;
sqlalchemy.engine.base.Engine: INFO: ()
sqlalchemy.engine.base.Engine: DEBUG: Col ('user_name',)
sqlalchemy.engine.base.Engine: DEBUG: Row (u'SPEED_IT', )
sqlalchemy.engine.base.Engine: INFO:

SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'

sqlalchemy.engine.base.Engine: INFO: (u'SPEED_IT',)
sqlalchemy.engine.base.Engine: DEBUG: Col ('default_schema_name',)
sqlalchemy.engine.base.Engine: DEBUG: Row (u'dbo', )
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: SELECT [COLUMNS_1].

[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

sqlalchemy.engine.base.Engine: INFO: (u'unicode_err_table', u'dbo')
sqlalchemy.engine.base.Engine: DEBUG: Col ('TABLE_SCHEMA',
'TABLE_NAME', 'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE',
'ORDINAL_POSITION', 'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION',
'NUMERIC_SCALE', 'COLUMN_DEFAULT', 'COLLATION_NAME')
sqlalchemy.engine.base.Engine: INFO:
CREATE TABLE unicode_err_table (
sort INTEGER NULL,
plain_varchar_no_coding_error VARCHAR(248) NULL
)


sqlalchemy.engine.base.Engine: INFO: ()
sqlalchemy.engine.base.Engine: INFO: COMMIT
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: INSERT INTO unicode_err_table
(sort, plain_varchar_no_coding_error) VALUES (?, ?)
sqlalchemy.engine.base.Engine: INFO: (1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
sqlalchemy.engine.base.Engine: INFO: COMMIT
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: SELECT unicode_err_table.sort,
unicode_err_table.plain_varchar_no_coding_error
FROM unicode_err_table
sqlalchemy.engine.base.Engine: INFO: ()
sqlalchemy.engine.base.Engine: DEBUG: Col ('sort',
'plain_varchar_no_coding_error')
sqlalchemy.engine.base.Engine: DEBUG: Row (1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: INSERT INTO unicode_err_table
(sort, plain_varchar_no_coding_error) VALUES (?, ?)
sqlalchemy.engine.base.Engine: INFO: (2, u'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m\u2019a
r\xe9veill\xe9. Elle disait: \xab S\u2019il vous pla\xeet\u2026
dessine-moi un mouton! \xbb')
sqlalchemy.engine.base.Engine: INFO: COMMIT
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: SELECT unicode_err_table.sort,
unicode_err_table.plain_varchar_no_coding_error
FROM unicode_err_table ORDER BY unicode_err_table.sort
sqlalchemy.engine.base.Engine: INFO: ()
sqlalchemy.engine.base.Engine: DEBUG: Col ('sort',
'plain_varchar_no_coding_error')
sqlalchemy.engine.base.Engine: DEBUG: Row (1, 'Alors vous imaginez ma
surprise, au lever du jour, quand une drle de petite voix ma rveill.
Elle disait: Sil vous plat dessine-moi un mouton! ')
sqlalchemy.engine.base.Engine: DEBUG: Row (2, 'Alors vous imaginez ma
surprise, au lever du jour, quand une dr\xf4le de petite voix m?a r
\xe9veill\xe9. Elle disait: \xab S?il vous pla\xeet? dessine-moi un
mouton! ')
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> checked out from pool
sqlalchemy.engine.base.Engine: INFO: SELECT [COLUMNS_1].

[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].
[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?

sqlalchemy.engine.base.Engine: INFO: (u'unicode_err_table', u'dbo')
sqlalchemy.engine.base.Engine: DEBUG: Col ('TABLE_SCHEMA',
'TABLE_NAME', 'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE',
'ORDINAL_POSITION', 'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION',
'NUMERIC_SCALE', 'COLUMN_DEFAULT', 'COLLATION_NAME')
sqlalchemy.engine.base.Engine: DEBUG: Row (u'dbo',
u'unicode_err_table', u'sort', 'YES', u'int', 1, None, 10, 0, None,
None)
sqlalchemy.engine.base.Engine: INFO:
DROP TABLE unicode_err_table
sqlalchemy.engine.base.Engine: INFO: ()
sqlalchemy.engine.base.Engine: INFO: COMMIT
sqlalchemy.pool.QueuePool: DEBUG: Connection <pyodbc.Connection object
at 0xa4490e0> being returned to pool
--------------------- >> end captured logging << ---------------------

======================================================================
FAIL: test.sql.test_types.UnicodeTest.test_native_unicode
----------------------------------------------------------------------
Traceback (most recent call last):
File "/opt/home/vo63573/satest/lib/python2.6/site-packages/nose/
case.py", line 197, in runTest
self.test(*self.arg)
File "/opt/home/vo63573/satest/src/sqlalchemy/./test/sql/
test_types.py", line 636, in test_native_unicode
testing.db.dialect.returns_unicode_strings)
AssertionError: name: mssql driver pyodbc
returns_unicode_strings=conditional

----------------------------------------------------------------------
Ran 7 tests in 0.358s

> ...
>
> read more »

[Michael Bayer]

thanks, the two test failures would be expected in this case, will see what response I get on the FreeTDS list if any.

[Victor Olex]

Your're welcome. As for no response from pyodbc that is indeed sloppy
as is the fact that PyPi package does not work. Hats off to you for
always being responsive (afaik). I often wonder what keeps you so
motivated but that's off topic.

> ...
>
> read more »

[Michael Bayer]

well I'm visibly upset about this one because i really *don't* have the motivation to go down another FreeTDS hole .... :)

[Michael Bayer]

On Sep 8, 2011, at 9:37 AM, Victor Olex wrote:

> Meanwhile, I can tell you how to build the stack because it is a bit
> tricky given certain package issues. I chose to build unixODBC and
> FreeTDS and pyodbc from sources into /usr/local.

Hi Victor -

I've installed unixODBC 2.3 /freetds 0.91 on a fedora server. I am getting better results, in that I no longer get "MemoryError:" when querying information schema. However, I'm still getting the datatype mismatch issue which prevents SQLAlchemy from querying the information schema tables appropriately, at least when a u'' string is sent:

[classic@f1 sqlalchemy]$ python test2.py

Traceback (most recent call last):

File "test2.py", line 14, in <module>
cursor.execute("SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]=? AND [TABLE_SCHEMA] =?", (u'abcdefghijk', u'dbo'))
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]The data types nvarchar and ntext are incompatible in the equal to operator. (402) (SQLParamData)')

note above, the error is dependent on the string having at least 11 characters.

so at this point I'm suspecting my SQL server may also have something going on in its settings. But the fact remains that I don't have this issue at all with FreeTDS 0.82.

It also makes me less than comfortable unconditionally emitting a u'' for a bound parameter as it appears to cause problems.

[Michael Bayer]

On Sep 9, 2011, at 10:48 AM, Michael Bayer wrote:

>
> It also makes me less than comfortable unconditionally emitting a u'' for a bound parameter as it appears to cause problems.

I've also checked my SQL server, all databases including master are configured at SQL Server 2008 compatibility...

[Victor Olex]

What version of pyodbc?

[Michael Bayer]

2.1.9 from the github link you showed me. Just to make sure I also just cloned the tip from code.google.com and tried that one too.

Has pyodbc made adjustments to changes in FreeTDS 0.91 ?

[Victor Olex]

Pyodbc has changes as late as this month. Misery likes company. When I
tried to install this same stack on 64 bit architecture I am getting
Segmentation fault errors for some queries. Easysoft has some info on
ODBC in 64bit at http://www.easysoft.com/developer/interfaces/odbc/64-bit.html
There are some variables that can be set wrt to word length when
building unixODBC. Hopefully I can find the right setup.

[Victor Olex]

I confirm successful build and run time on both 64-bit and 32-bit
Linux (RHEL5) againt SQL Server 2008.

It has turned out that Pyodbc needed the CPLUS_INCLUDE_PATH set to
where my version of UnixODBC was prefixed to prior to building. Best
bet is to remove all prior versions including distro packaged. If you
don't the Pyodbc may pickup header sources from there. By the way
their setup.py seems to have issues and can leave behind certain files
despite "clean" command. Also mind any leftovers in site-packages.

Playing with compiler settings for the unixODBC, namely various
combinations of the CPPFLAGS="-DBUILD_LEGACY_64_BIT_MODE -
DSIZEOF_LONG_INT=8" allowed me to align the libs to the Pyodbc such
that the segfault got eliminated but in the end the flags proved
unneccesary once the above mentioned include path was properly set.

On Sep 9, 10:54 pm, Victor Olex <victor.o...@vtenterprise.com> wrote:
> Pyodbc has changes as late as this month. Misery likes company. When I
> tried to install this same stack on 64 bit architecture I am getting
> Segmentation fault errors for some queries. Easysoft has some info on

> ODBC in 64bit athttp://www.easysoft.com/developer/interfaces/odbc/64-bit.html

[Michael Bayer]

On the fedora VM i tried, I fully de-installed the UnixODBC RPMS, so the one built from source should be the only one present.

Will take a look at the pyodbc files..

[Michael Bayer]

I've committed the patch that allows unicode binds in 0.91. I've also added a CAST() to the "tablename" column when we query the information schema, to work around the nvarchar/ntext issue that for utterly mysterious reasons your setup does not get (I get it on both linux and OSX).

At this point I can barely run FreeTDS on OSX at all as my 0.82 build is hosed and it no longer builds - I can use the binaries though. 0.91 continues to be plagued with MemoryError and completely broken encoding on OSX, and I also get many core dumps on Linux - more tickets have been posted to Pyodbc with much more detail including ODBC and FreeTDS logfiles. I can get basic unicode and ORM tests running with 0.91 and Linux, though.

I've wasted many days trying to get all of these libraries to work predictably and I'm out of time. Overall the status of ODBC on unix is extremely concerning to me as there doesn't seem to be anyone around to help with anything.

On Sep 12, 2011, at 3:27 PM, Victor Olex wrote:

[Victor Olex]

The ODBC on Linux has always been flaky but somehow I always managed
to get it to work well enough. I don't have OSX to try but I could do
some testing on more recent versions of Linux. What distro are you
struggling with? I am not sure how else I can help. Time is short for
most.