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. On Sep 7, 2011, at 11:25 PM, Michael Bayer wrote: > 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. > > > > > On Sep 7, 2011, at 7:12 PM, Victor Olex wrote: > >> 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 >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
