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.
>
> 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
>>
>> 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
>>>> athttp://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
>>> athttp://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.
