<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Mapserver developers,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="text-indent:.5in">We found a small issue when applying a “IsLike” WFS filter on a Oracle 11g. Apparently the escape character, in our case a backslash character, get escaped in the FLTGetIsLikeComparisonSQLExpression():Mapogcfilter.c<o:p></o:p></p>
<p class="MsoNormal">by the following code:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas">
<span style="color:blue">if</span> (lp->connectiontype != MS_OGR) {<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> strlcat(szBuffer,
<span style="color:#A31515">" escape '"</span>, bufferSize);<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> szTmp[0] = pszEscape[0];<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas">
<span style="color:blue">if</span> (pszEscape[0] == <span style="color:#A31515">'\\'</span>) {
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> szTmp[1] =
<span style="color:#A31515">'\\'</span>;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> szTmp[2] =
<span style="color:#A31515">'\''</span>;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> szTmp[3] =
<span style="color:#A31515">'\0'</span>;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"> }
<span style="color:blue">…</span><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Resulting in the following oracle error:<o:p></o:p></p>
<p class="MsoNormal">Error: ORA-01425: escape character must be character string of length 1<o:p></o:p></p>
<p class="MsoNormal">. Query statement: SELECT NE_ID, NE_FT_PK_COL, NE_UNIQUE, NE_BEGIN_MP, NE_LENGTH, NE_DESCR, NE_START_DATE, NE_END_DATE, NE_ADMIN_UNIT, ADMIN_UNIT_CODE, NE_GTY_GROUP_TYPE, LOG_DIRECTION, LEGACY_RIS_IDENTIFIER, ROUTE_PREFIX, ROUTE_NUMBER,
ROUTE_DIRECTION, ROUTE_SUFFIX, ROAD_TYPE, RAMP_NUMBER, RAMP_SUFFIX, OBJECTID, rownum, GEOLOC FROM V_NM_NLT_RTE_RTE_SDO_DT WHERE ("ROUTE_NUMBER" like '%834%' escape '\\') AND SDO_FILTER( GEOLOC, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates
),'querytype=window') = 'TRUE' . Check your data statement.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">In the previous query if “escape '\\'” is replaced by “escape '\' “ the error is solved.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">We already know that escape value needs to be escaped depending on the Oracle version, please see
<a href="https://community.oracle.com/thread/1126967">https://community.oracle.com/thread/1126967</a>, and that’s probably the case for other data sources. So we would like to know what would be the best approach to fix those version specific issues with Oracle.
maporaclespatial.c already parses the VERSION token for determining the oracle version, should we implement the same mechanism in Mapogcfilter.c? Unfortunately the msSplitData function responsible for parsing data parameter in not accessible from Mapogcfilter.c,
and I don’t think duplicating that code in Mapogcfilter.c would be a good solution.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Please let me know your thoughts about this…<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">For reference here is Sample WFS request using an IsLike filter:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><?xml version="1.0" encoding="iso-8859-1"?><o:p></o:p></p>
<p class="MsoNormal"><GetFeature service="WFS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" version="1.1.0" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc"
outputFormat="text/xml; subtype=gml/3.1.1" xmlns:eB="http://www.bentley.com/en-US/Products/AssetWise" resultType="results" xmlns="http://www.opengis.net/wfs"><o:p></o:p></p>
<p class="MsoNormal"> <Query typeName="eB:STATE_ROUTES"><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>OBJECTID</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>ROAD_TYPE</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>ROUTE_PREFIX</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>ROUTE_NUMBER</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>ROUTE_SUFFIX</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>NE_ID</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <PropertyName>msGeometry</PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <ogc:Filter><o:p></o:p></p>
<p class="MsoNormal"> <ogc:PropertyIsLike wildCard="%" singleChar="_" escape="\"><o:p></o:p></p>
<p class="MsoNormal"> <ogc:PropertyName>ROUTE_NUMBER</ogc:PropertyName><o:p></o:p></p>
<p class="MsoNormal"> <ogc:Literal>%834%</ogc:Literal><o:p></o:p></p>
<p class="MsoNormal"> </ogc:PropertyIsLike><o:p></o:p></p>
<p class="MsoNormal"> </ogc:Filter><o:p></o:p></p>
<p class="MsoNormal"> </Query><o:p></o:p></p>
<p class="MsoNormal"></GetFeature><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks for your help,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Michel D’Astous<o:p></o:p></p>
</div>
</body>
</html>