Quantcast

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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

jratike80
Hi,

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

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""

However, with single quotes it works
ogrinfo -dialect sqlite 3d.shp -sql "select * from '3d'"
Even this one, yields a correct result
ogrinfo -dialect sqlite 3d.shp -sql "select 'fixed_string'  from '3d'"

Layers without numbers or underscores in names behave right
ogrinfo -dialect sqlite test.shp -sql "select * from "test"'"

Is there something that could be fixed in the code, or should I add a line to two into the documentation http://www.gdal.org/ogr_sql_sqlite.html?

-Jukka Rahkonen-
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite-SQL problem with some layer names

Even Rouault-2

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


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite-SQL problem with some layer names

jratike80

Hi,

Hi,


I had a try with editing the .dox file that is used for generating http://www.gdal.org/ogr_sql_sqlite.html with changeset #37971. Could you check if the text makes sense and appears right?


-Jukka-


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Loading...