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

[Michel.Dastous at bentley.com]

Mike,



We are using  Plugin/OracleSpatial connection.  The Oracle version is very important, we reproduce it with Oracle 11g, but you should be able to reproduce it with Oracle 10g and 12g.  Since Oracle 9i, Oracle has change the way it handle the "escape" value, you should able to reproduce the issue when SqplPlus and a simple IsLike SQL request.



I'll try reproducing it with an OGR connection.



Thanks,



Michel



________________________________
From: Smith, Michael ERDC-RDE-CRREL-NH <Michael.Smith at erdc.dren.mil>
Sent: August 4, 2014 8:00 PM
To: Michel Dastous; mapserver-dev at lists.osgeo.org
Subject: Re: [EXTERNAL] [mapserver-dev] WFS and IsLike filtering issue (Against Oracle)

Michael,


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 ( https://github.com/msmitherdc/mapserver/tree/rfc91-filter-transform) 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.


Mike


--
Michael Smith
US Army Corps
Remote Sensing GIS/Center


From: "Michel.Dastous at bentley.com<mailto:Michel.Dastous at bentley.com>" <Michel.Dastous at bentley.com<mailto:Michel.Dastous at bentley.com>>
Date: Monday, August 4, 2014 at 4:32 PM
To: "mapserver-dev at lists.osgeo.org<mailto:mapserver-dev at lists.osgeo.org>" <mapserver-dev at lists.osgeo.org<mailto:mapserver-dev at lists.osgeo.org>>
Subject: [EXTERNAL] [mapserver-dev] WFS and IsLike filtering issue (Against Oracle)
Resent-From: Michael Smith <michael.smith at usace.army.mil<mailto:michael.smith at usace.army.mil>>


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"<http://www.opengis.net/wfs%22>>
  <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