Bug report #10615
MSSQL table attributes are not correct in qgis
| Status: | Closed | ||
|---|---|---|---|
| Priority: | Normal | ||
| Assignee: | - | ||
| Category: | Data Provider/MSSQL | ||
| Affected QGIS version: | 2.2.0 | Regression?: | No |
| Operating System: | windows | Easy fix?: | No |
| Pull Request or Patch supplied: | No | Resolution: | invalid |
| Crashes QGIS or corrupts data: | No | Copied to github as #: | 19019 |
Description
For MSSQL 2008 R2 point feature layer; the attribute values displayed in QGIS 2.2 are not correct.
My MSSQL table is defined as:
x(decimal(18,6), null)
y(decimal(18,6), null)
z(varchar(50), null)
geom(geometry,null)
where x and y contain the latitude and longitude.
Geometry column created and populated as follows:
ALTER TABLE [table]
ADD geom geometry
GO
UPDATE [table]
SET geom = geometry::Point(x,y,4326)
History
#1
Updated by Nathan Woodrow almost 10 years ago
What isn't correct?
Have you tested master because I have changed some stuff since 2.2.
#2
Updated by matt veenstra almost 10 years ago
The attributes in sql server are as follows:
z x y
a -122.347123 47.569034
b -122.346273 47.590282
c -122.357531 47.586916
d -122.358341 47.575575
e -122.360000 47.600000
The attributes table in qgis shows:
z x y
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000
When using the identify features tool, clicking on each point displays the following same attributes for each point:
a, -122.347123, 47.569034
The locations of the points are correct.
If I save the layer to a .csv file, the values in the csv file are correct.
I haven't had a chance to try qgis master.
#3
Updated by Jan Lippmann almost 10 years ago
Do you use a primary key column in the table?
Can you send the create statements for the table or a complete mssql server bak backupfile? So that i could test.
#4
Updated by matt veenstra almost 10 years ago
- File test1.bak added
I did not use a primary key because I was just trying a quick and dirty trial...
Adding a primary key results in qgis displaying the correct attribute values.
I understand that not having a primary key is not acceptable database design; however, I didn't expect the resulting behavior in Qgis... I suggest either not allowing connecting to tables that do not have a primary key or provide a message window warning similar to "unexpected behavior can result from tables that do not have a primary key".
For what it's worth, I attached backup file with tables test_1 (no primary key) and test_3 (with a primary key).
Thanks.
#5
Updated by Nathan Woodrow almost 10 years ago
We need a primary key because everything is done using the ID in QGIS. SQL Server doesn't provide a auto id for us so we have to hope that it's set. If it's not set we fall back to using a int column but it must be unique or else we can't follow what is going on.
A pain I know, would be a lot better if there was just a ROWID column we always knew was there as a fall back.
#6
Updated by Jürgen Fischer almost 10 years ago
- Target version changed from Version 2.2 to Future Release - Lower Priority
#7
Updated by Nathan Woodrow almost 9 years ago
- Resolution set to invalid
- Status changed from Open to Closed
As of 2.8 having a unique key column is now enforced to stop this from happening. I will be adding better error messages in the future.