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.
