Bug report #9029

Spatialite Integer columns cannot be used

Added by Brian Freed over 10 years ago. Updated over 10 years ago.

Category:Data Provider/SpatiaLite
Affected QGIS version:2.0.1 Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:17684


In 2.0, NULL values are written to any Spatialite columns defined as integers when an object is first created.
Subsequent editing of the same object saves integer columns correctly.
Text and real columns do not do this (in 2.0.1-Dufour)

If any integer column has a not null constraint, it's impossible to create new entities on that layer.

Tested Polygons & Linestrings, both projected coords and 4326. Tested creating layer in Spatialite-GUI v3, Spatialite-GUI v4, and directly in QGIS via New Spatialite layer. Same behavior.

Tested Master (Build 45582e5): Unfortunately, it's worse - all column types write NULL on creation.


#1 Updated by Brian Freed over 10 years ago

One exception: integer primary key still writes

#2 Updated by Jürgen Fischer over 10 years ago

Hm, I don't really understand. You're digitizing on a layer with NULL contraints, you aren't entering non-NULL values and cannot save? Isn't that what ought to happen?

#3 Updated by Brian Freed over 10 years ago

No, sorry, I wasn't clear. I have a layer I use for making Atlas Coverages. It's got not nulls and various triggers to force snapping to the correct page size. I've been working backwards for why it's always worked great in 1.8 but doesn't in 2.
So along the way I've tried lots of things.

But in the end:
Create a new spatialite layer. Define some columns as "whole number". That's it - no not nulls, no triggers, just a basic layer.
Now digitize something, and populate all the columns. Save the edits.
Look at the feature: all the integer columns are NULL (despite having entered some number in the form).

I probably shouldn't have clouded the issue by mentioning the not null. Entering all my data, then having Spatialite bomb with a "page_num cannot be null" error, even though I gave it a page_num was just one of the steps along the way. :-)

#4 Updated by Brian Freed over 10 years ago

I just uninstalled the 64-bit version and downloaded the 32-bit installer. It doesn't seem to have the same problem.

I should have specified 64-bit in platform, I guess. It appears to be a 64-bit only bug.

#5 Updated by Matthias Kuhn over 10 years ago

Some fixes have been applied to the spatialite provider (also affecting "whole number" type) between 2.0 and master.
It would be great if it would be possible to check if this is fixed in master.

#6 Updated by Brian Freed over 10 years ago

I re-ran the OSGeo4W 64-bit installer for qgis-dev, and in build 5f5cd4c, Spatialite layers are working fine.
Thanks for the heads up!

#7 Updated by Brian Freed over 10 years ago

Although, it seems like Spatialite version 3 databases don't have editable views either now.
(I'll update the other bug report; previously only version 4 databases had that problem)

#8 Updated by Giovanni Manghi over 10 years ago

  • Resolution set to fixed/implemented
  • Status changed from Open to Closed

Also available in: Atom PDF