Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
Thanks for the response. I think I will conclude the same as you, and report back to the server developers that this is a bad idea. Odd-Ragnar -Original Message- From: Rahkonen Jukka [mailto:jukka.rahko...@mmmtike.fi] Sent: 8. oktober 2012 22:22 To: gdal-dev@lists.osgeo.org Subject: Re: [gdal-dev] OGR SQL: Excecuting select where column name contains . Even Rouault wrote: I can confirm that it is not illegal per se because the following works. I hope that the backslashes and everything go through properly. ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select \nom.dept\ from \tows:dot.test\ It is an open WFS server and you can test it with ogrinfo and from your code too. However, I will remove the dot.test feature type sooner or later. Verify and make your code to work in a similar way than ogrinfo. Jukka, I didn't do any test up to now, but now that I've tested with your above server, here are my findings and conclusions. To start, I'm wondering to which extent did you check the result of the above ogrinfo command. It works for me on GDAL 1.9.2 and GDAL trunk on Linux and Windows, but the output is what Odd-Ragna actually observes, something like : FIELD_1: String (0.0) I feel ashamed and made one more test to compansate my lazy interpretation that if TinyOWS is sending something back it is then all right. HTTP POST tends to be more reliable when making complicated WFS requests so I tried that method by sending directly to the server the following GetFeature: POST http://188.64.1.61/cgi-bin/tinyows HTTP/1.1 Content-Type: text/xml User-Agent: Jakarta Commons-HttpClient/3.0-beta1 Host: 188.64.1.61 Connection: Keep-Alive Content-Length: 404 wfs:GetFeature xmlns:ogc=http://www.opengis.net/ogc; xmlns:gml=http://www.opengis.net/gml; xmlns:wfs=http://www.opengis.net/wfs; service=WFS version=1.0.0 maxFeatures=1000 outputFormat=GML2wfs:Query xmlns:tows=http://www.tinyows.org/; typeName=tows:dot.testogc:PropertyNametows:nom.dept/ogc:PropertyNameogc:PropertyNametows:the_geom/ogc:PropertyName/wfs:Query/wfs:GetFeature TinyOWS gives an error ?xml version=1.0 encoding=UTF-8? ServiceExceptionReport xmlns=http://www.opengis.net/ogc; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation=http://www.opengis.net/ogc http://schemas.opengis.net/wms/1.1.1/OGC-exception.xsd; version=1.2.0 ServiceException code=NoMatching locator=GetFeature propertyname values and typename values don't match/ServiceException /ServiceExceptionReport There may be something wrong with TinyOWS but it must be noted that it has passed all the CITE tests for WFS 1.0.0 and 1.1.0. If so well tested server fails with dots in the field names it is very probable that there are lots of other servers and clients which will fail too. Conclusion: Do not use dots in names if you hope it to work. -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
The problem is OGRSQL (ogr_gensql.cpp) which adds the string FIELD_%d when it doesn't know how to handle column names with dot character in them. Because the dot character is a special/reserved character it needs to be enclosed in '', , ``, [], ..., all depending on which dialect is used (MS SqlServer, Oracle, MySQL, SqlLite, ...) So it seems that to me that this is not legal in OGRSQL Odd-Ragnar -Original Message- From: Even Rouault [mailto:even.roua...@mines-paris.org] Sent: 8. oktober 2012 22:42 To: gdal-dev@lists.osgeo.org Cc: Rahkonen Jukka Subject: Re: [gdal-dev] OGR SQL: Excecuting select where column name contains . There may be something wrong with TinyOWS but it must be noted that it has passed all the CITE tests for WFS 1.0.0 and 1.1.0. If so well tested server fails with dots in the field names it is very probable that there are lots of other servers and clients which will fail too. The CITE tests don't necessarily test all corner cases of implementations. They test the requirements and the protocol of the standard, so there are likely many holes in the test coverage. For example, I believe that a WFS/WMS implementation can pass the CITE tests but may not behave properly with the annoying axis ordering problem when using real world datasets (for example when serving a shapefile that uses the standard long/lat ordering and whose coordinates must be swapped when going from/to WFS 1.1) I've looked at WFS 1.1 spec, and at page 17 (§7.3 Property names), I see that the definition of the property name allows a dot character. So this is theoretically valid... ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
Even Rouault even.rouault at mines-paris.org writes: I've looked at WFS 1.1 spec, and at page 17 (§7.3 Property names), I see that the definition of the property name allows a dot character. So this is theoretically valid... TinyOWS is now theoretically fixed https://github.com/mapserver/tinyows/issues/17 I had also a test with Mapserver and it did handle dot.attribute names OK. However, I had to rename my dot.table because Mapserver got lost when querying the table for getting the schema. [Tue Oct 9 10:56:02 2012].214191 msPostGISLayerGetItems(): Query error. Error (ERROR: schema dot does not exist LINE 1: select * from dot.test where false limit 0 ^ ) executing SQL: select * from dot.test where false limit 0 [Tue Oct 9 10:57:12 2012].923891 msPostGISLayerGetItems(): Query error. Error (ERROR: schema dot does not exist LINE 1: select * from dot.test where false limit 0 ^ ) executing SQL: select * from dot.test where false limit 0 I do not have Geoserver in my hands right now but I believe that this has been enough to prove that dots in attribute and feature type names will make so many clients and servers to fail that it should be considered as Bad Practice. -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
I see you have been busy discussing how to handle column names which contain
dot .
My problem is that I can't get any reasonable values from the query I make,
whether I use str1.str2 or 'str1.str2' in my expression.
In my C++ code this is:
a) const char *sSqlQuery = SELECT \str1.str2\ FROM 'layers:layer1'\
b) const char *sSqlQuery = SELECT 'str1.str2' FROM 'layers:layer1'\
The result is the same, and I get no errors, but the column name returned to me
is FIELD_1, and all values are str1.str2
What am I missing here?
When I run an ordinary query like:
const char *sSqlQuery = SELECT \str\ FROM 'layers:layer1'\
then I get the correct result.
I've debugged a bit and found that
OGRGenSQLResultsLayer::OGRGenSQLResultsLayer(), in ogr_gensql.cpp, generates
the FIELD_1 name:
if( strlen(psColDef-field_name) == 0 )
{
CPLFree( psColDef-field_name );
psColDef-field_name = (char *) CPLMalloc(40);
sprintf( psColDef-field_name, FIELD_%d, iField+1 );
}
Please help, or confirm if I'm trying to do something illegal
Odd-Ragnar
-Original Message-
From: Even Rouault [mailto:even.roua...@mines-paris.org]
Sent: 5. oktober 2012 18:39
To: Rahkonen Jukka
Cc: gdal-dev@lists.osgeo.org
Subject: Re: [gdal-dev] OGR SQL: Excecuting select where column name contains
.
To follow SQL, it is recommended to you double-quote characters,
which might require SHELL escaping since they are already in a
double-quoted
string, like :
Yes, that's a good theory but it is not easy to use it on Windows.
Does anybody in the world know how should this one be escaped? I have
already tried to use ^ as inner double quotes but without success, as
well as having two ^^ and three ^^^.
Doh! my webmail client has changed what I have typed ! To escape double-quote
in Windows shell, you need to put a backslash character before them.
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
I can confirm that it is not illegal per se because the following works. I hope that the backslashes and everything go through properly. ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select \nom.dept\ from \tows:dot.test\ It is an open WFS server and you can test it with ogrinfo and from your code too. However, I will remove the dot.test feature type sooner or later. Verify and make your code to work in a similar way than ogrinfo. Jukka, I didn't do any test up to now, but now that I've tested with your above server, here are my findings and conclusions. To start, I'm wondering to which extent did you check the result of the above ogrinfo command. It works for me on GDAL 1.9.2 and GDAL trunk on Linux and Windows, but the output is what Odd-Ragna actually observes, something like : FIELD_1: String (0.0) OGRFeature(tows:dot.test):0 FIELD_1 (String) = nom.dept MULTIPOLYGON ((([..]) By studying the code, I have come to the conclusion that whatever the way you use to quote or not quote the field name, the presence of a dot in a field name, let's say nom.dept, is interpreted by OGR as the dept field of layer nom. As layer nom doesn't exist, OGR then interprets it as a string literal, hence the observed result. In fact, there's a subtle difference : - if you don't quote nom.dept, then OGR will know that it is supposed to be a field. Then as it doesn't find it, it will report an error : Unrecognised field name nom.dept - if you quote it, as there's an ambiguity, it will first try to identify it as a field, and as this fails, it fallbacks to interpret is as as string litteral. There's however a way to workaround this. You can use : ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select \foo.nom.dept\ from \tows:dot.test\ foo The layer name tows:dot.test will get an alias foo, and when OGR interprets foo.nom.dept is will interpret it as the nom.dept field of layer foo, which works now since foo is indeed a valid alias for tows:dot.test ! The bad news is that, at least for the TinyOWS server, it doesn't like the request that is sent : http://188.64.1.61/cgi- bin/tinyows?SERVICE=WFSVERSION=1.1.0REQUEST=GetFeatureTYPENAME=tows:dot.testPROPERTYNAME=%28nom.dept,the_geom%29 This generates : ERROR 1: Error: cannot parse ?xml version='1.0' encoding='UTF-8'? ExceptionReport xmlns='http://www.opengis.net/ows' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.opengis.net/ows http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd' version='1.0.0' language='en' Exception exceptionCode='NoMatching' locator='GetFeature' ExceptionTextpropertyname values and typename values don't match/ExceptionText /Exception /ExceptionReport TinyOWS doesn't like the nom.dept field. It propably forwards it directly as a SQL request to PostgreSQL, which also interprets nom.dept as the dept field of table nom. So you're still stuck at the end of the day. Not sure how other WFS servers would react and if it is supposed to work. Conclusion: don't use dot character in field names ! -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
Even Rouault wrote: I can confirm that it is not illegal per se because the following works. I hope that the backslashes and everything go through properly. ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select \nom.dept\ from \tows:dot.test\ It is an open WFS server and you can test it with ogrinfo and from your code too. However, I will remove the dot.test feature type sooner or later. Verify and make your code to work in a similar way than ogrinfo. Jukka, I didn't do any test up to now, but now that I've tested with your above server, here are my findings and conclusions. To start, I'm wondering to which extent did you check the result of the above ogrinfo command. It works for me on GDAL 1.9.2 and GDAL trunk on Linux and Windows, but the output is what Odd-Ragna actually observes, something like : FIELD_1: String (0.0) I feel ashamed and made one more test to compansate my lazy interpretation that if TinyOWS is sending something back it is then all right. HTTP POST tends to be more reliable when making complicated WFS requests so I tried that method by sending directly to the server the following GetFeature: POST http://188.64.1.61/cgi-bin/tinyows HTTP/1.1 Content-Type: text/xml User-Agent: Jakarta Commons-HttpClient/3.0-beta1 Host: 188.64.1.61 Connection: Keep-Alive Content-Length: 404 wfs:GetFeature xmlns:ogc=http://www.opengis.net/ogc; xmlns:gml=http://www.opengis.net/gml; xmlns:wfs=http://www.opengis.net/wfs; service=WFS version=1.0.0 maxFeatures=1000 outputFormat=GML2wfs:Query xmlns:tows=http://www.tinyows.org/; typeName=tows:dot.testogc:PropertyNametows:nom.dept/ogc:PropertyNameogc:PropertyNametows:the_geom/ogc:PropertyName/wfs:Query/wfs:GetFeature TinyOWS gives an error ?xml version=1.0 encoding=UTF-8? ServiceExceptionReport xmlns=http://www.opengis.net/ogc; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation=http://www.opengis.net/ogc http://schemas.opengis.net/wms/1.1.1/OGC-exception.xsd; version=1.2.0 ServiceException code=NoMatching locator=GetFeature propertyname values and typename values don't match/ServiceException /ServiceExceptionReport There may be something wrong with TinyOWS but it must be noted that it has passed all the CITE tests for WFS 1.0.0 and 1.1.0. If so well tested server fails with dots in the field names it is very probable that there are lots of other servers and clients which will fail too. Conclusion: Do not use dots in names if you hope it to work. -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
There may be something wrong with TinyOWS but it must be noted that it has passed all the CITE tests for WFS 1.0.0 and 1.1.0. If so well tested server fails with dots in the field names it is very probable that there are lots of other servers and clients which will fail too. The CITE tests don't necessarily test all corner cases of implementations. They test the requirements and the protocol of the standard, so there are likely many holes in the test coverage. For example, I believe that a WFS/WMS implementation can pass the CITE tests but may not behave properly with the annoying axis ordering problem when using real world datasets (for example when serving a shapefile that uses the standard long/lat ordering and whose coordinates must be swapped when going from/to WFS 1.1) I've looked at WFS 1.1 spec, and at page 17 (§7.3 Property names), I see that the definition of the property name allows a dot character. So this is theoretically valid... ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
Even Rouault wrote: Selon Jukka Rahkonen jukka.rahko...@mmmtike.fi: This should definitely work too, but it does not ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select code_reg from tows: france INFO: Open of `WFS:http://188.64.1.61/cgi-bin/tinyows' using driver `WFS' successful. ERROR 1: SQL Expression Parsing Error: syntax error Sorry for the undue judgement. This is the correct way to do it on Windows ogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select 'nom.dept' from 'tows:dot.test' It is just always as hard for me to remember the right syntax. OGR SQL follows SQL on this : column and table/layer names are identifiers. So they must start with a letter or underscore, and subsequent characters must be letters, underscores or digits. If the identifier is more complex than that, it must be quoted. To follow SQL, it is recommended to you double-quote characters, which might require SHELL escaping since they are already in a double-quoted string, like : ogrinfo poly.shp -sql select eas_id from poly Yes, that's a good theory but it is not easy to use it on Windows. Does anybody in the world know how should this one be escaped? I have already tried to use ^ as inner double quotes but without success, as well as having two ^^ and three ^^^. gdal_devogrinfo WFS:http://188.64.1.61/cgi-bin/tinyows -sql select nom.dept from tows:dot.test INFO: Open of `WFS:http://188.64.1.61/cgi-bin/tinyows' using driver `WFS' successful. ERROR 1: SQL Expression Parsing Error: syntax error OGR currently also accepts quoting of column/table names with single-quote character (which is normally reserved for string literals), but this can lead to some ambiguity. If you use 'foo', OGR will currently choose the column name foo if it exists, instead of the string literal 'foo' as standard SQL would mandate. -Jukka- ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
To follow SQL, it is recommended to you double-quote characters, which might require SHELL escaping since they are already in a double-quoted string, like : Yes, that's a good theory but it is not easy to use it on Windows. Does anybody in the world know how should this one be escaped? I have already tried to use ^ as inner double quotes but without success, as well as having two ^^ and three ^^^. Doh! my webmail client has changed what I have typed ! To escape double-quote in Windows shell, you need to put a backslash character before them. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
[gdal-dev] OGR SQL: Excecuting select where column name contains .
Is it possible to make OGRDataSource:: ExecuteSQL() to work with a column name containing .? Here is an example: select column.name.one, column.name.two from 'wfs:layerName' I have tried to enclose the column name in [] brackets, but that was invalid as well. Any ideas on how to get around this? Odd-Ragnar ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL: Excecuting select where column name contains .
Hi, You haven't said which platform and binding/command line you are using. In linux command line you would probably replace . with \. Also column name should probably enclosed in quotes, not brackets. Etienne On Thu, Oct 4, 2012 at 8:29 AM, Odd Ragnar Lydersen odd-ragnar.lyder...@powel.no wrote: Is it possible to make OGRDataSource:: ExecuteSQL() to work with a column name containing .? Here is an example: select column.name.one, column.name.two from 'wfs:layerName' I have tried to enclose the column name in [] brackets, but that was invalid as well. Any ideas on how to get around this? Odd-Ragnar ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev ___ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
