Bug report #13140

Save as Geopackage trouble with non SQL characters

Added by Andreas Neumann over 9 years ago. Updated about 9 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Vectors
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:21204

Description

When one uses the "Save As" functionality for vector layers and uses certain characters (like a dash) in the filename one gets an error message and the save fails, e.g. with the following message:

Export to vector file failed.
Error: creation of layer failed (OGR error:sqlite3_exec(CREATE TABLE hl_2016-06 ( fid INTEGER PRIMARY KEY AUTOINCREMENT, geom MULTILINESTRING )) failed: near "-": syntax error)

In this case it looks like the dash is not valid in the SQL CREATE TABLE statement.

It would be good if QGIS would replace such special characters with another character, like an "_" underscore.

geopackage_save_as_troubles_with_some_characters.png - Screenshot showing the Save As dialogue (103 KB) Andreas Neumann, 2015-07-22 06:57 AM

History

#1 Updated by Giovanni Manghi over 9 years ago

  • Status changed from Open to Feedback

Hi Andreas, wrong image attached?

The error seems to be from ogr, so I guess that this should be files as feature request(?).

#2 Updated by Jukka Rahkonen over 9 years ago

I would say that dash sign should not be used in GeoPackage table names but unfortunately the standard does not deny it.

Have a look at the standard https://portal.opengeospatial.org/files/?artifact_id=63378
The conformity tests are all written like:

"SELECT table_name, column_name FROM gpkg_data_columns..."

Notice that identifiers are unquoted.

Next make a test with plain SQL for example with spatialite-gui

create table test-t (id, attribute);

The result is "SQL error near "-": syntax error."

Make a new trial with a small edit:

create table "test-t" (id, attribute);

Now the table gets created.

It looks like those who wrote the GeoPackage validity tests have never tried to run them with table names which require double quotes around the identifier. I made some trials and for example "+" and "&" behave in a similar way.

I think that we should first ask for an opinion from OGC GeoPackage group if dash and other characters should really be supported as table names before starting to make fixes into QGIS or GDAL.

#3 Updated by Jukka Rahkonen over 9 years ago

By this gdal-dev mailing list thread http://thread.gmane.org/gmane.comp.gis.gdal.devel/40845 the problem is solved in GDAL 2.0 and creating tables with dash in the name is possible. Personally I think it is somehow risky, though.

#4 Updated by Giovanni Manghi about 9 years ago

  • Resolution set to up/downstream
  • Status changed from Feedback to Closed

fixed upstream.

Also available in: Atom PDF