[gdal-dev] SQLite dialect in OGRDataSource::ExecuteSQL

[Even Rouault]

Le mardi 10 février 2015 18:20:12, Pierluigi Guasqui a écrit :
> Even,
> 
> I set the environment variable CPL_DEBUG=ON and rerun my code. I found
> the statement you mentioned:
> 
>     SQLITE: OGR style SQLite DB found !
>     SQLITE: Spatialite not available. Implementing a few functions
>     OGR2SQLITE: sqlite3_declare_vtab(CREATE TABLE
>     "xrs_2010jan21_mf2_geometry_polygons"("et" FLOAT,"utc" VARCHAR(25), ...
> 
> but then it printed the following error:
> 
>     ERROR 1: Cannot create virtual table for layer
>     'xrs_2010jan21_mf2_geometry_polygons' : CREATE VIRTUAL: invalid SQL
>     statement : CREATE TABLE "xrs_2010jan21_mf2_geometry_polygons"("et"
>     FLOAT,"utc" VARCHAR(25), ...
>     SQLITE: OGR style SQLite DB found !
>     ERROR 1: In ExecuteSQL(): sqlite3_prepare(select * from
>     xrs_2010jan21_mf2_geometry_polygons):
>        no such table: xrs_2010jan21_mf2_geometry_polygons
>     OGR: Unloading VirtualOGR module

Could you paste the full (untruncated) statement ?
And the output of "ogrinfo -al -so yourshapefile.shp" also

> 
> Any idea why I am getting this error?
> 
> Thank you!
> Pierluigi
> 
> Il 10/02/2015 13:42, Even Rouault ha scritto:
> > Le mardi 10 février 2015 12:47:46, Pierluigi Guasqui a écrit :
> >> Hi All!
> >> 
> >> I am using the GDAL/OGR functions to query a shapefile. This is a sample
> >> 
> >> code I have been successfully running so far:
> >>      OGRDataSource *poDS = OGRSFDriverRegistrar::Open(
> >>      "some/path/to/myshapefile.shp" );
> >>      OGRLayer *poLayer = poDS->ExecuteSQL( "select * from myshapefile",
> >>      NULL, NULL );
> >>      // ...
> >>      // fetching result features from "poLayer"
> >>      // ...
> >>      poDS->ReleaseResultSet( poLayer );
> >>      OGRDataSource::DestroyDataSource(poDS);
> >> 
> >> As you can see, I am currently using the OGR SQL dialect, as I am
> >> passing NULL as the third parameter of the OGRDataSource::ExecuteSQL
> >> function.
> >> 
> >> Now I am trying to extend the SQL syntax to be able to use the OGR
> >> SQLite dialect. I have successfully recompiled GDAL 1.11.1 with SQLite
> >> support (version 3.8.5) but when I try to execute the same sample code
> >> (passing "SQLITE" as the 3rd argument of the OGRDataSource::ExecuteSQL
> >> 
> >> function) I am getting the following error:
> >>      In ExecuteSQL(): sqlite3_prepare(select * from myshapefile): no
> >>      such table: myshapefile
> >> 
> >> What is the table name that contains the shapefile features that I can
> >> use in my SQL queries?
> > 
> > Pierluigi,
> > 
> > That should work... If you tried to configure GDAL several times, perhaps
> > you should do a make clean.
> > 
> > Could you try defining CPL_DEBUG=ON as environment variable and rerun
> > your code. You should normally see a statement like the following one to
> > create the SQLite virtual table that matches the shapefile layer :
> > 
> > OGR2SQLITE: sqlite3_declare_vtab(CREATE TABLE "poly"("AREA"
> > FLOAT,"EAS_ID" FLOAT,"PRFEDEA" VARCHAR(16),OGR_STYLE VARCHAR
> > HIDDEN,"GEOMETRY"
> > BLOB_POLYGON_2D_50000))
> > 
> > Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com