3

I have a SpatiaLite Database with OSM-Data from planet OSM (imported with the OSM import tool from QGIS 2.0)

Everything is fine so far. I can create, export, delete tables and draw them in QGIS... but when I create a new column in my table with:

ALTER TABLE planet_22_points 
ADD xlabel integer DEFAULT 0

It will appear in the "DB Manager" and in "QspatiaLite" but NOT in QGIS! So I tried to add a column with QGIS using the attribute table. This actually seems to work, but after saving the table, zack! the columns I was creating before using the SQL command just appears! And that massage comes up:

Could not commit changes to layer planet_22_points

Errors: SUCCESS: 1 attribute(s) added.
ERROR: the count of fields is incorrect after addition/removal of fields! 

enter image description here

QGIS 2.0.1, SQLite version: 3.7.9; SpatiaLite Library: 4.1.0

  • Which version of the spatialite tools (command line or QSpatiaLite) are you using? Are you possibly hitting hub.qgis.org/issues/9029 ? – BradHards Feb 4 '14 at 9:33
  • I see that removing and reloading the spatialite layer in QGIS then shows all the columns. Seems to be a refresh problem. – Micha Feb 4 '14 at 9:37
  • same issue here it seems - QGIS 2.6.1, SpatiaLite 4.2.0 - mod_spatialite 7 on maverick. reloading doesn't fix. – fluidmotion Apr 1 '15 at 22:41
  • actually may be more related to hub.qgis.org/issues/8923 – fluidmotion Apr 2 '15 at 2:47
2

though this question has been out there awhile, i wanted to post what seems to have worked for me (which sounded almost identical to the OP question). Following the bug report from http://hub.qgis.org/issues/8923 to https://groups.google.com/forum/#!topic/spatialite-users/FOq1DPRDkJw, i tried the following (python implementation):

    conn=sqlite3.connect(dbPath)
    conn.enable_load_extension(True)
    cursor=conn.cursor()
    cursor.execute('SELECT load_extension(\'/usr/local/lib/mod_spatialite.so\');')  
    sql = 'SELECT UpdateLayerStatistics(\'myTableName\');'
    cursor.execute(sql)

and the new column magically appeared in the QGIS attribute table.

2

Just in case, I had this same problem and I found another solution.
Execute these two commands into the spatialite_gui terminal (wait a few seconds):

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics();

Restart QGIS (or reload your project), enjoy !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.