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.
