[gdal-dev] SQLite-SQL problem with some layer names

[Even Rouault]

Hi Jukka,

> 
> I have problems in using the SQLite dialect if layer names contains numbers
> or underscores. I am on Windows.

This is just an issue with proper escaping of double quotes in the shell. Identifiers that start 
with a number (and possibly underscore I haven't checked) need to be put between double 
quotes per the SQL standard. And as the SQL statement itself is put between double quotes, 
you need to escape those internal double quotes with \

ogrinfo  -dialect sqlite 3d.shp -sql "select * from \"3d\""

Works on both Linux (bash) and Windows cmd

> 
> ogrinfo -dialect sqlite 3d.shp -sql "select * from 3d"
> INFO: Open of `3d.shp'
>       using driver `ESRI Shapefile' successful.
> ERROR 1: In ExecuteSQL(): sqlite3_prepare(select * from 3d):
>   unrecognized token: "3d"
> 
> Same happens with a proper SQL with double quotes around the identifier
> ogrinfo -dialect sqlite 3d.shp -sql "select * from "3d""

My understanding is that the parsing of the above is the concatenation of:
select * from
3d
(empty string)

so equivalent to no quoting of the identifier.

> 
> However, with single quotes it works
> ogrinfo -dialect sqlite 3d.shp -sql "select * from '3d'"

SQLite tolerates single quoting around identifiers, but that cannot be used for column names 
(otherwise you get a fixed string)

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170412/bd37ce56/attachment.html>