From 62edc4023e189836a5ba490c2dbb835827486b0b Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Thu, 16 Apr 2020 23:01:03 +0200 Subject: [PATCH] Fix regression when reflecting tables in MSSQL Fix a regression introduced by the reflection of computed column in MSSQL when using SQL server versions before 2012, which does not support the ``concat`` function and when using the legacy TDS version 4.2. The dialect will try to detect the protocol version of first connect and run in compatibility mode if it cannot detect it. Fixes: #5255 Fixes: #5271 Change-Id: I7b33f7889ac0784cd8ae5385cbd50bc8c862398a (cherry picked from commit 50d283af1a254ef981ac9a2466399b4828de0117) --- doc/build/changelog/unreleased_13/5255.rst | 8 +++++ doc/build/changelog/unreleased_13/5271.rst | 7 ++++ lib/sqlalchemy/dialects/mssql/base.py | 41 +++++++++++++++------- 3 files changed, 44 insertions(+), 12 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/5255.rst create mode 100644 doc/build/changelog/unreleased_13/5271.rst diff --git a/doc/build/changelog/unreleased_13/5255.rst b/doc/build/changelog/unreleased_13/5255.rst new file mode 100644 index 0000000000..7f8f941c82 --- /dev/null +++ b/doc/build/changelog/unreleased_13/5255.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, mssql, reflection + :tickets: 5255 + + Fix a regression introduced by the reflection of computed column in + MSSQL when using the legacy TDS version 4.2. The dialect will try + to detect the protocol version of first connect and run in compatibility + mode if it cannot detect it. diff --git a/doc/build/changelog/unreleased_13/5271.rst b/doc/build/changelog/unreleased_13/5271.rst new file mode 100644 index 0000000000..ea2296b14c --- /dev/null +++ b/doc/build/changelog/unreleased_13/5271.rst @@ -0,0 +1,7 @@ +.. change:: + :tags: bug, mssql, reflection + :tickets: 5271 + + Fix a regression introduced by the reflection of computed column in + MSSQL when using SQL server versions before 2012, which does not support + the ``concat`` function. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index d746cbeee3..0a6e27a464 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2287,6 +2287,7 @@ class MSDialect(default.DefaultDialect): non_native_boolean_check_constraint = False supports_unicode_binds = True postfetch_lastrowid = True + _supports_nvarchar_max = False server_version_info = () @@ -2392,19 +2393,22 @@ class MSDialect(default.DefaultDialect): finally: cursor.close() else: + # note that the NotImplementedError is caught by + # DefaultDialect, so the warning here is all that displays util.warn( "Could not fetch transaction isolation level, " "tried views: %s; final error was: %s" % (views, last_error) ) - raise NotImplementedError( "Can't fetch isolation level on this particular " - "SQL Server version" + "SQL Server version. tried views: %s; final error was: %s" + % (views, last_error) ) def initialize(self, connection): super(MSDialect, self).initialize(connection) self._setup_version_attributes() + self._setup_supports_nvarchar_max(connection) def on_connect(self): if self.isolation_level is not None: @@ -2435,6 +2439,16 @@ class MSDialect(default.DefaultDialect): self.server_version_info >= MS_2012_VERSION ) + def _setup_supports_nvarchar_max(self, connection): + try: + connection.scalar( + sql.text("SELECT CAST('test max support' AS NVARCHAR(max))") + ) + except exc.DBAPIError: + self._supports_nvarchar_max = False + else: + self._supports_nvarchar_max = True + def _get_default_schema_name(self, connection): if self.server_version_info < MS_2005_VERSION: return self.schema_name @@ -2591,10 +2605,8 @@ class MSDialect(default.DefaultDialect): columns.c.table_schema == owner, ) table_fullname = "%s.%s" % (owner, tablename) - concat = func.concat( - columns.c.table_schema, ".", columns.c.table_name - ) - join_on = computed_cols.c.object_id == func.object_id(concat) + full_name = columns.c.table_schema + "." + columns.c.table_name + join_on = computed_cols.c.object_id == func.object_id(full_name) else: whereclause = columns.c.table_name == tablename table_fullname = tablename @@ -2606,12 +2618,17 @@ class MSDialect(default.DefaultDialect): join_on, columns.c.column_name == computed_cols.c.name ) join = columns.join(computed_cols, onclause=join_on, isouter=True) + + if self._supports_nvarchar_max: + computed_definition = computed_cols.c.definition + else: + # tds_version 4.2 does not support NVARCHAR(MAX) + computed_definition = sql.cast( + computed_cols.c.definition, NVARCHAR(4000) + ) + s = sql.select( - [ - columns, - computed_cols.c.definition, - computed_cols.c.is_persisted, - ], + [columns, computed_definition, computed_cols.c.is_persisted], whereclause, from_obj=join, order_by=[columns.c.ordinal_position], @@ -2632,7 +2649,7 @@ class MSDialect(default.DefaultDialect): numericscale = row[columns.c.numeric_scale] default = row[columns.c.column_default] collation = row[columns.c.collation_name] - definition = row[computed_cols.c.definition] + definition = row[computed_definition] is_persisted = row[computed_cols.c.is_persisted] coltype = self.ischema_names.get(type_, None) -- 2.39.5