<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif;">
<div>Michael,</div>
<div><br>
</div>
<div>Are you using an OGR connection or an OracleSpatial connection in mapserver? I'm working on a rewrite of the WFS Filter Operations on the Oracle backend as part of RFC 91. I can say that with a Oraclespatial connection, the "\" escape value is properly
translated. I tried your PropertyIsLike filter against the branch I'm working on ( <a href="https://github.com/msmitherdc/mapserver/tree/rfc91-filter-transform">https://github.com/msmitherdc/mapserver/tree/rfc91-filter-transform</a>) and had no issues. If
you have any other issues and/or test cases where you've had issues, I'd like to see them so I can test out behavoir before this gets into release 7 of MapServer.</div>
<div><br>
</div>
<div>Mike</div>
<div><br>
</div>
<div>
<div>
<div>
<div>-- </div>
<div>Michael Smith</div>
</div>
<div>US Army Corps</div>
<div>Remote Sensing GIS/Center</div>
</div>
</div>
<div><br>
</div>
<span id="OLK_SRC_BODY_SECTION">
<div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<span style="font-weight:bold">From: </span>"<a href="mailto:Michel.Dastous@bentley.com">Michel.Dastous@bentley.com</a>" <<a href="mailto:Michel.Dastous@bentley.com">Michel.Dastous@bentley.com</a>><br>
<span style="font-weight:bold">Date: </span>Monday, August 4, 2014 at 4:32 PM<br>
<span style="font-weight:bold">To: </span>"<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>" <<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>><br>
<span style="font-weight:bold">Subject: </span>[EXTERNAL] [mapserver-dev] WFS and IsLike filtering issue (Against Oracle)<br>
<span style="font-weight:bold">Resent-From: </span>Michael Smith <<a href="mailto:michael.smith@usace.army.mil">michael.smith@usace.army.mil</a>><br>
</div>
<div><br>
</div>
<div 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">
<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]-->
<div 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="<a href="http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>" schemaLocation="<a href="http://www.opengis.net/wfs">http://www.opengis.net/wfs</a>
<a href="http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">http://schemas.opengis.net/wfs/1.1.0/wfs.xsd</a>" version="1.1.0" xmlns:wfs="<a href="http://www.opengis.net/wfs">http://www.opengis.net/wfs</a>" xmlns:ogc="<a href="http://www.opengis.net/ogc">http://www.opengis.net/ogc</a>"
outputFormat="text/xml; subtype=gml/3.1.1" xmlns:eB="<a href="http://www.bentley.com/en-US/Products/AssetWise">http://www.bentley.com/en-US/Products/AssetWise</a>" resultType="results" xmlns="<a href="http://www.opengis.net/wfs"">http://www.opengis.net/wfs"</a>><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>
</div>
</div>
</span>
</body>
</html>