[mapserver-users] WFS fails if table name includes a dot character

[Rahkonen Jukka]

Puneet Kishor wrote:

> On Oct 9, 2012, at 7:51 AM, Rahkonen Jukka <Jukka.Rahkonen at mmmtike.fi> wrote:

>> Hi,
>>
>> It may be an odd idea to use dots in table/feature type names, but because I made such a test and faced a problem so I report it here.
>> Source table is in PostGIS and its name is "dot.test".  WFS DescribeFeatureType leads to following error
>>
>> [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
>>
>>
>> -Jukka Rahkonen-
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users


> If using non-standard characters, double quote the entity name. So, try
> SELECT * FROM "dot.test" WHERE ...
> To be completely unambiguous, prefix the schema name. So, if the table is in schema foo, try
> SELECT * FROM "foo.dot.test" WHERE ...

Hi,

SQL is not written by me but generated by Mapserver.  Originally I had in the dot.test mapfile 

 DATA "the_geom from dot.test using unique gid using srid=3067"

Changing that to
 DATA "the_geom from "dot.test"' using unique gid using srid=3067"
 gives an error
 loadLayer(): Unknown identifier. Parsing error near (dot.test):(line 10)

Changing that to:
DATA "the_geom from 'dot.test' using unique gid using srid=3067" 
gives another error
 msPostGISLayerGetItems(): Query error. Error (ERROR:  syntax error at or near "'dot.test'"
Brackets are not accepted either as [dot.test].

So I am not sure how it should be done correctly. Obviously double quotation marks should be somehow escaped inside DATA.

I think that in a way it would be exact and correct to make Mapserver to create SQL to use always delimited identifiers so that 
DATA "the_geom from table" 
would yield SQL queries for PostGIS in format 
SELECT "the_geom" FROM "table"
but that would have a side effect that table and attribute names inside DATA would be case sensitive. But perhaps they already are?

 By the way, isn't the ANSI SQL way for giving schema and table names in delimited format like
SELECT * FROM "foo"."dot.test" WHERE ..?

-Jukka Rahkonen-

> Puneet Kishor