vendredi 20 février 2015

escape quote in mapfile layer


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 abble to build the solution!


help would be appreciated


EDIT 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://ift.tt/1ikcaPe http://ift.tt/1LhZrq2"><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>




Aucun commentaire:

Enregistrer un commentaire