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 ? On Sep 8, 2011, at 12:41 PM, Victor Olex wrote: > 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. > -- 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.