3

I'm using mapserver 6 with mysql 5.5. It work like a charme, almost...

The layer part of my mapfile is

LAYER
    NAME "monsatR_mySQL"
    TYPE POINT
    STATUS ON
    CONNECTIONTYPE OGR
    CONNECTION "MYSQL:monasteres,host=1.1.1.1,port=3306,user=Foo,password=p4ssW0rd"
    DATA "SELECT a.*,b.id_etab,b.nom_usuel FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab"
    DUMP TRUE ## REQUIRED


    METADATA
      "wfs_title" "monastR" ##required
      "wfs_srs"    "EPSG:4326" ##REQUIRED
      "gml_include_items" "all" ## Optional (serves all attributes for layer)
      "gml_featureid"     "FID" ## REQUIRED
      "wfs_enable_request" "*"
      "wfs_getfeature_formatlist" "OGRGeoJSON"
    END
    PROJECTION
      #"init=epsg:3857" ##recommended
      "init=epsg:4326"
    END
    TRANSPARENCY 50
    CLASS
      NAME "monastR français"
    END   
  END #LAYER

As you can seen, the output is a geoJson. My problem come from nom_usuel column where some characteres are single quote like Saint-Pierre de l'Isle... Single quote are misunderstand in geoJson ...

How can I escape single quotes ?

I have seen some information in the mapserver documentation, but I'm not able to build the solution!


As Laurent Jégou proposed I have tried with SELECT a.*,b.id_etab,REPLACE(nom_usuel, '\'',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab

But it returns

<ows:ExceptionReport version="1.1.0" language="en-US" xsi:schemaLocation="http://www.opengis.net/ows http://schemas.opengis.net/ows/1.0.0/owsExceptionReport.xsd"><ows:Exception exceptionCode="NoApplicableCode" locator="mapserv">   <ows:ExceptionText>msWFSGetFeature(): WFS server error. ms_error->code not found
msOGRFileOpen(): OGR error. ExecuteSQL(SELECT a.*,b.id_etab,REPLACE(nom_usuel, ''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab) failed.
MySQL error message:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_e' at line 1 Description: SELECT a.*,b.id_etab,REPLACE(nom_usuel, ''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab
msOGRFileOpen(): OGR error. ExecuteSQL(SELECT a.*,b.id_etab,REPLACE(nom_usuel, ''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab) failed.
MySQL error message:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_e' at line 1 Description: SELECT a.*,b.id_etab,REPLACE(nom_usuel, ''',chr(39)) FROM t_geom_points as a LEFT JOIN t_etab as b ON a.id_etab = b.id_etab</ows:ExceptionText></ows:Exception></ows:ExceptionReport>
1

It's weird because I never had any problem with this feature. If you take a look at https://download.data.grandlyon.com/wfs/grandlyon?SERVICE=WFS&VERSION=1.1.0&request=GetFeature&outputFormat=geojson&typename=pvo_patrimoine_voirie.pvostationvelov you will notice MapServer produces geojson with double-quoted strings which can handle single quoted string without problem. So the question is why you have such a behaviour. Is the OGR driver unable to do something the POSTGIS driver naturally does ? Could you try to dump your data into shapefile and see of it works with this kind of datasource (or another one, but not OGR).

0

You can try to use the MySQL REPLACE() function to escape the offending characters in your query result ?

  • I can't touch the mysql database ;-( – delaye Feb 19 '15 at 16:39
  • You can change the query string, with the DATA parameter, replacing nom_usuel by something like : REPLACE(nom_usuel, '\'', 'something') ? – Laurent Jégou Feb 19 '15 at 16:44
  • aïe <ows:Exception exceptionCode="NoApplicableCode" locator="mapserv"> – delaye Feb 19 '15 at 16:58
  • please consider editing the original answer. or posting another answer. We like long answers not short ones though. – Brad Nesom Feb 19 '15 at 18:47
  • @BradNesom sorry ... now it's done – delaye Feb 20 '15 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.