On 04/04/2016 06:10 PM, Douglas Eisenstein wrote:
Hi,
Here's the situation, we're trying to reflect tables from SQL Server
into metadata, and we're encountering a problem of case sensitivity, in
particular when it executes the following query:
SELECT [C].[COLUMN_NAME], [R].[TABLE_SCHEMA], [R].[TABLE_NAME],
[R].[COLUMN_NAME], [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME],
[REFERENTIAL_CONSTRAINTS_1].[MATCH_OPTION],
[REFERENTIAL_CONSTRAINTS_1].[UPDATE_RULE],
[REFERENTIAL_CONSTRAINTS_1].[DELETE_RULE]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R],
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS
[REFERENTIAL_CONSTRAINTS_1]
WHERE [C].[TABLE_NAME] = CAST(%(table_name_1)s AS NVARCHAR(max)) AND
[C].[TABLE_SCHEMA] = CAST(%(table_schema_1)s AS NVARCHAR(max)) AND
[C].[CONSTRAINT_NAME] = [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME]
AND [R].[CONSTRAINT_NAME] =
[REFERENTIAL_CONSTRAINTS_1].[UNIQUE_CONSTRAINT_NAME] AND
[C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY
[REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [R].[ORDINAL_POSITION]
The issue is that when we have case sensitivity set to False, it runs
this statement below (probably because there are two columns names in
the result set that are the same name ([C].[COLUMN_NAME], ....
[R].[COLUMN_NAME]) and we're getting an error on "by_key[key]" that
the key doesn't exist (and by_key.keys() returns a list of all UPPERCASE
keys, while it looks like the code is expecting all lowercase keys).
It seems there's a bug in 1.0 (also in 1.1 but with fewer symptoms than
this) if you set case_sensitive=False on create_engine() with regards to
result sets that have duplicate non-lower-case columns in them.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3690/dupe-col-logic-when-case-sensitive-is
is added.
If
we set the case sensitivity to True then it works perfectly fine (but we
don't want that set across the board for obvious reasons).
This flag only refers to column names and typically only impacts things
when you're using Core and targeting at result row values directly with
string keys. Typically there's no real need to set this flag, as the
comment states it was there to work around some old edge cases with
Oracle and Firebird. I'd shoot for keeping the flag set to True.
The issue is when this code result.py
if len(by_key)!= num_ctx_cols:
seen= set()
for recin raw:
key= rec[1]
if keyin seen:
by_key[key]= (None, by_key[key][1],None)
seen.add(key)
We're using:
Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64)
Aug 23 2013 20:08:13
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
(Hypervisor)
pymssql (2.1.2)
SQLAlchemy (1.0.12).
We've read this article that seems to refer to the kind of problem we're
running into, but this resolution didn't seem to help us...
https://bitbucket.org/zzzeek/sqlalchemy/commits/87bbba32bc54fa0253e9c81663df669dc355f5da
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
