Bug report #17387

MSSQL provider fails to load UNIQUEIDENTIFIER fields

Added by Kristian Thy over 2 years ago. Updated over 2 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:master Regression?:No
Operating System:Windows 8.1 Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:25284

Description

Experienced in QGIS 2.18.12, 2.18.14 on Windows 8.1. (Code still present in master branch, but 2.99 crashes before I reach it, apparently due to an unrelated issue.)

I'm trying to load a layer from MSSQL into PostgreSQL using the DB Manager plugin (following the recipe at https://gis.stackexchange.com/a/200594/10741) but I receive this error for each row:

Error 7
Feature write errors:
Creation error for features from #0 to #0. Provider errors was: 
PostGIS error while adding features: ERROR:  value too long for type character varying(16)

Source code investigation leads me to believe that this is caused by the MSSQL provider treating the UNIQUEIDENTIFIER datatype as a QVariant::String. Problem is that MSSQL reports the length of this datatype as 16, which is the byte size of a GUID. When converted to a string, the length is actually 36 characters, so the error I'm seeing is likely caused by trying to cram a 36 character string into a field 16 characters wide.

Luckily, MSSQL reports the size of the datatype in the precision field also returned by exec sp_columns. My hunch is that the issue can thus be resolved by the attached patch to src/providers/mssql/qgsmssqlprovider.cpp. Unfortunately I can't build QGIS on Windows myself, so I have not tested the patch.

qgsmssqlprovider.cpp.patch Magnifier (605 Bytes) Kristian Thy, 2017-11-02 03:53 PM

Associated revisions

Revision 320bda01
Added by Kristian Thy over 2 years ago

[bugfix] Issue #17387: MSSQL provider fails to load UNIQUEIDENTIFIER fields (#5521)

  • Fix #17387: MSSQL provider fails to load UNIQUEIDENTIFIER fields

History

#1 Updated by Giovanni Manghi over 2 years ago

  • Status changed from Open to Feedback

Better submit the patch as a pull request on github, otherwise here it won't be noticed/reviewed.

#2 Updated by Kristian Thy over 2 years ago

Giovanni Manghi wrote:

Better submit the patch as a pull request on github, otherwise here it won't be noticed/reviewed.

Will do later tonight.

#3 Updated by Kristian Thy over 2 years ago

  • Status changed from Feedback to Open

#4 Updated by Kristian Thy over 2 years ago

  • % Done changed from 0 to 100
  • Status changed from Open to Closed

Also available in: Atom PDF