[mapserver-dev] [EXTERNAL] Problem with filter in Oracle

[Michael Smith]

Björn,

Can you turn on DEBUG level 3 and show what queries are being generated?

That will help isolate whats going on with the queries to the backend.

Mike

-- 
Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers
From:  mapserver-dev <mapserver-dev-bounces at lists.osgeo.org> on behalf of
Björn Danielsson <bjorn.danielsson at falubo.se>
Date:  Thursday, January 14, 2016 at  4:30 AM
To:  "mapserver-dev at lists.osgeo.org" <mapserver-dev at lists.osgeo.org>
Subject:  [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
Resent-From:  Michael Smith <michael.smith at usace.army.mil>

> 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/765d6d00/attachment.html>