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 On Sep 8, 11:51 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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. > > > -- > > 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.