[mapserver-dev] Problem with filter in Oracle

[Jeff McKenna]

Hi Björn,

Thanks for bringing this issue to everyone's attention.  Be careful that 
most don't know what MapServer versions are associated with MS4W 
releases.  You can check this through the commandline "mapserv -v" call. 
  Here is a summary:

- your initial script worked with MapServer 5.0.2 (MS4W 2.2.7)
- script fails with MapServer 6.0.3 (MS4W 3.0.6) and MapServer 7.0 (MS4W 
3.1.1)

-jeff




On 2016-01-14 5:30 AM, Björn Danielsson wrote:
> Hi,
> I have been using ms4w for quite some time and have discovered problems
> with Oracle when using php_mapscript. I posted this problem at ms4w's
> support and was adviced to turn to you.
> I am supporting a gis-system on the web that is heavily database
> dependent, and can run on either postgres or oracle according to the
> costumer's request. So there are two sets of php-scripts that are
> identical apart from the database calls.
> Thera are no problems with postgres.
> With Oracle there is a funny problem with filter.
> Look at this:
> foreach($aLayersIdx as $layerIndex) {
>    $oLayerK = $oMapK->getLayer($layerIndex);
> if ($bDebug) {
>    fwrite($f,"layer: ".$oLayerK->name."\r\n");
> }
>    if ($oLayerK->name == "AVD" && $avd == "") {
>      $oLayerK->set("status",MS_OFF);
>      continue;
>    }
>    $filter = "";
>    switch ($oLayerK->name) {
>      case "AVD" :
>        $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
> [BEV]='".$bev."' AND [SKIFTE]='".$skifte."' AND [AVD]='".$avd."')";
>        $data = "KARTOBJ_SDO FROM ".$dbowner.".AVD*AVD";
>        break;
>
>      case "X" :
>        $filter = "([ID]='".$sessid."')";
>        $data = "KARTOBJ_SDO FROM ".$dbowner.".KARTA*TMP2";
>        break;
>    }
>
>    $data = str_replace("*","$",$data);
>
>    $oLayerK->set("data", $data);
>    $oLayerK->setFilter($filter);
>    $oLayerK->setConnectionType(MS_ORACLESPATIAL);
>    $oLayerK->set("connection", $conn);
>    $oLayerK->set("status", MS_ON);
>    if ($bDebug) {
>      fwrite($f,"connection: ".$conn."\r\n");
>      fwrite($f,"filter: ".$filter."\r\n");
>      fwrite($f,"data: ".$data."\r\n");
>    }
> }
> A mapfile with two layers, 'AVD' and 'X'.
> Problem is that filter for layer 'X' does not work. Or, rather, it works
> but no record is returned. The mapserver debugger reports no error.
> The filter for layer 'AVD' works fine.
> When the line $filter = "[ID]='".$sessid."')" is commented and the table
> KARTA$TMP2 contains only one record it works and a map layer is
> produced. So it seems that when filter is introduced in the select
> something goes wrong.
> I thought that ID might be a reserved word but after some testing I
> found that filter fails in calls to other tables as well.
> For example:
> This works
>       $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
> [BEV]='".$bev."')";
> But this does not work
>   $filter = "([ORGID]='".$orgid."' AND [FV]='".$fv."' AND
> [BEV]='".$bev."' AND [SKIFTE]='".$skifte."')";
> The problem is consistent in that way that where the problem occurs it
> always occurs, and where it not occurs it never occurs. It is not
> intermittent.
> The php script ( where the snippet came from above ) was developed under
> ms4w version 2.2.7 and at that time no problems occured. I have also
> used version 3.0.6 and 3.1.1 and both versions have problems. So
> somewhere between 2.2.7 and 3.0.6 something happened.
> I am at loss here.
> Björn Danielsson
>
>

-- 
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/