From ebf96c34a22a61843d8c7320c413287ae2ed3379 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 28 Jun 2019 10:41:38 -0400 Subject: [PATCH] CAST bind values against SQL Server sys into NVARCHAR Ensured that the queries used to reflect indexes and view definitions will explicitly CAST string parameters into NVARCHAR, as many SQL Server drivers frequently treat string values, particularly those with non-ascii characters or larger string values, as TEXT which often don't compare correctly against VARCHAR characters in SQL Server's information schema tables for some reason. These CAST operations already take place for reflection queries against SQL Server ``information_schema.`` tables but were missing from three additional queries that are against ``sys.`` tables. Fixes: #4745 Change-Id: I3056533bf1a1e8ef17742879d369ab13f8b704ea (cherry picked from commit 345f2eb05b07713cec19c620b95ca2dfa1ca5aa0) --- doc/build/changelog/unreleased_13/4745.rst | 13 +++++++++++++ lib/sqlalchemy/dialects/mssql/base.py | 12 ++++++------ test/dialect/mssql/test_reflection.py | 17 +++++++++++++++++ 3 files changed, 36 insertions(+), 6 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/4745.rst diff --git a/doc/build/changelog/unreleased_13/4745.rst b/doc/build/changelog/unreleased_13/4745.rst new file mode 100644 index 0000000000..0728588a1c --- /dev/null +++ b/doc/build/changelog/unreleased_13/4745.rst @@ -0,0 +1,13 @@ +.. change:: + :tags: bug, mssql + :tickets: 4745 + + Ensured that the queries used to reflect indexes and view definitions will + explicitly CAST string parameters into NVARCHAR, as many SQL Server drivers + frequently treat string values, particularly those with non-ascii + characters or larger string values, as TEXT which often don't compare + correctly against VARCHAR characters in SQL Server's information schema + tables for some reason. These CAST operations already take place for + reflection queries against SQL Server ``information_schema.`` tables but + were missing from three additional queries that are against ``sys.`` + tables. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 675c743f34..c43aa1b05f 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2427,8 +2427,8 @@ class MSDialect(default.DefaultDialect): "and ind.is_primary_key=0 and ind.type != 0" ) .bindparams( - sql.bindparam("tabname", tablename, sqltypes.String()), - sql.bindparam("schname", owner, sqltypes.String()), + sql.bindparam("tabname", tablename, ischema.CoerceUnicode()), + sql.bindparam("schname", owner, ischema.CoerceUnicode()), ) .columns(name=sqltypes.Unicode()) ) @@ -2452,8 +2452,8 @@ class MSDialect(default.DefaultDialect): "and sch.name=:schname" ) .bindparams( - sql.bindparam("tabname", tablename, sqltypes.String()), - sql.bindparam("schname", owner, sqltypes.String()), + sql.bindparam("tabname", tablename, ischema.CoerceUnicode()), + sql.bindparam("schname", owner, ischema.CoerceUnicode()), ) .columns(name=sqltypes.Unicode()) ) @@ -2478,8 +2478,8 @@ class MSDialect(default.DefaultDialect): "views.schema_id=sch.schema_id and " "views.name=:viewname and sch.name=:schname" ).bindparams( - sql.bindparam("viewname", viewname, sqltypes.String()), - sql.bindparam("schname", owner, sqltypes.String()), + sql.bindparam("viewname", viewname, ischema.CoerceUnicode()), + sql.bindparam("schname", owner, ischema.CoerceUnicode()), ) ) diff --git a/test/dialect/mssql/test_reflection.py b/test/dialect/mssql/test_reflection.py index d9b65d4e0a..8393a7b482 100644 --- a/test/dialect/mssql/test_reflection.py +++ b/test/dialect/mssql/test_reflection.py @@ -208,6 +208,23 @@ class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL): ], ) + @testing.provide_metadata + def test_table_name_that_is_greater_than_16_chars(self): + metadata = self.metadata + Table( + "ABCDEFGHIJKLMNOPQRSTUVWXYZ", + metadata, + Column("id", Integer, primary_key=True), + Column("foo", Integer), + Index("foo_idx", "foo"), + ) + metadata.create_all() + + t = Table( + "ABCDEFGHIJKLMNOPQRSTUVWXYZ", MetaData(), autoload_with=testing.db + ) + eq_(t.name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ") + @testing.provide_metadata def test_db_qualified_items(self): metadata = self.metadata -- 2.47.3