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

Tue Aug 5 05:29:43 PDT 2014

Michel,

One difference now in the driver is that an WFS IsLike now translates to a
REGEXP_LIKE query. It handles regex patterns that the WFS IsLike filter
supports.

Mike

-- 
Michael Smith

US Army Corps
Remote Sensing GIS/Center

On 8/5/14, 8:16 AM, "Michel.Dastous at bentley.com"
<Michel.Dastous at bentley.com> wrote:

>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