[mapserver-dev] WFS and IsLike filtering issue (Against Oracle)

[Michel.Dastous at bentley.com]

Mapserver developers,

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
by the following code:

  if (lp->connectiontype != MS_OGR) {
    strlcat(szBuffer, " escape '", bufferSize);
    szTmp[0] = pszEscape[0];
    if (pszEscape[0] == '\\') {
      szTmp[1] = '\\';
      szTmp[2] = '\'';
      szTmp[3] = '\0';
    } ...

Resulting in the following oracle error:
Error: ORA-01425: escape character must be character string of length 1
. 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.

In the previous query if "escape '\\'" is replaced by "escape '\' " the error is solved.

We already know that escape value needs to be escaped depending  on the Oracle version, please see https://community.oracle.com/thread/1126967, 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.

Please let me know your thoughts about this...


For reference here is Sample WFS request using an IsLike filter:

<?xml version="1.0" encoding="iso-8859-1"?>
<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">
  <Query typeName="eB:STATE_ROUTES">
    <PropertyName>OBJECTID</PropertyName>
    <PropertyName>ROAD_TYPE</PropertyName>
    <PropertyName>ROUTE_PREFIX</PropertyName>
    <PropertyName>ROUTE_NUMBER</PropertyName>
    <PropertyName>ROUTE_SUFFIX</PropertyName>
    <PropertyName>NE_ID</PropertyName>
    <PropertyName>msGeometry</PropertyName>
    <ogc:Filter>
      <ogc:PropertyIsLike wildCard="%" singleChar="_" escape="\">
        <ogc:PropertyName>ROUTE_NUMBER</ogc:PropertyName>
        <ogc:Literal>%834%</ogc:Literal>
      </ogc:PropertyIsLike>
    </ogc:Filter>
  </Query>
</GetFeature>

Thanks for your help,


Michel D'Astous
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20140804/e7b15efc/attachment.html>