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 <[email protected]> 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 <[email protected]> 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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
