[mapserver-dev] Problem with filter in Oracle

[Björn Danielsson]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20160114/080002cb/attachment.html>