Merge pull request #8411 from wonder-sk/slap-mssql-around-with-a-trout

Fix a bunch of MS SQL provider issues
qgis · Nov 5, 2018 · e7900a3 · e7900a3
2 parents 68be6df + 923adf2
commit e7900a3
Show file tree

Hide file tree

Showing 3 changed files with 19 additions and 5 deletions.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -98,7 +98,12 @@ QSqlDatabase QgsMssqlConnection::getDatabase( const QString &service, const QStr
 #ifdef Q_OS_WIN
     connectionString = "driver={SQL Server}";
 #else
-    connectionString = QStringLiteral( "driver={FreeTDS};port=1433" );
+    // It seems that FreeTDS driver by default uses an ancient TDS protocol version (4.2) to communicate with MS SQL
+    // which was causing various data corruption errors, for example:
+    // - truncating data from varchar columns to 255 chars - failing to read WKT for CRS
+    // - truncating binary data to 4096 bytes (see @@TEXTSIZE) - failing to parse larger geometries
+    // The added "TDS_Version=auto" should negotiate more recent version (manually setting e.g. 7.2 worked fine too)
+    connectionString = QStringLiteral( "driver={FreeTDS};port=1433;TDS_Version=auto" );
 #endif
   }
 

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -347,6 +347,14 @@ void QgsMssqlConnectionItem::setLayerType( QgsMssqlLayerProperty layerProperty )
     layerProperty.srid = sridList[i];
     schemaItem->addLayer( layerProperty, true );
   }
+
+  if ( typeList.isEmpty() )
+  {
+    // this suggests that retrieval of geometry type and CRS failed if no results were returned
+    // for examle due to invalid geometries in the table (WHAAAT?)
+    // but we still want to add have such table in the list
+    schemaItem->addLayer( layerProperty, true );
+  }
 }
 
 bool QgsMssqlConnectionItem::equal( const QgsDataItem *other )
@@ -728,6 +736,11 @@ QgsMssqlLayerItem *QgsMssqlSchemaItem::addLayer( const QgsMssqlLayerProperty &la
         layerType = QgsLayerItem::TableLayer;
         tip = tr( "as geometryless table" );
       }
+      else if ( !layerProperty.geometryColName.isEmpty() && layerProperty.type.isEmpty() )
+      {
+        // geometry column is there but we failed to determine geometry type (e.g. due to invalid geometries)
+        layerType = QgsLayerItem::Vector;
+      }
       else
       {
         return nullptr;

diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -1490,10 +1490,6 @@ QgsCoordinateReferenceSystem QgsMssqlProvider::crs() const
 {
   if ( !mCrs.isValid() && mSRId > 0 )
   {
-    mCrs.createFromSrid( mSRId );
-    if ( mCrs.isValid() )
-      return mCrs;
-
     // try to load crs from the database tables as a fallback
     QSqlQuery query = createQuery();
     query.setForwardOnly( true );