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

[Michael Smith]

Always reply back to the list, others can assist also.

You need to set the DEBUG 3 at the layer level in your mapfile and then the
actual queries sent to Oracle will be displayed in the log file (as well as
any spatial bind values). Then you can run those queries outside of
mapserver and verify whether you are getting the rows of data you expect or
not.

Mike

----
Michael Smith
US Army Corps
Remote Sensing GIS/Center
michael.smith at usace.army.mil


From:  Björn Danielsson <bjorn.danielsson at falubo.se>
Date:  Friday, January 15, 2016 at 2:40 AM
To:  Michael Smith <michael.smith.erdc at gmail.com>
Subject:  Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle

> Hi Michael and thanks for your quick response.
>  
> I did 3 runs with debuglevel 3 and the outcome from the debugger does not say
> me much.
>  
>   DEBUG              3
>   CONFIG             "MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"
>  
> I also activated the 'debugger' in my php scripts that writes a lot, among
> other things the database calls, to a file.
>  
> The three runs and the resulting debug files are called A,B and C. From
> mapserver debug 'ms_error_A.txt', and from php debug 'php_debug_A.log'. Look
> for 'filter = ' in the php debug files.
>  
> A, B and C refer to the examples in my original mail.
>  
> Run A shows the example:
>      $filter = "([ID]='".$sessid."')";
> with the layers 'AVD' and 'X' where 'AVD' works fine and 'X' does not.
>  
> Run B and C is from the same php script with different contents in filter.
> Look for layers 'AVD' and 'AVD-L'. Actually the same table and column. Layer
> 'AVD' shows polygons and layer 'AVD-L' the polygons' outlines.
> Run C works and run B does not.
> The column SKIFTE in run B is surrounded by parenthesis because it may be like
> (SKIFTE='A' OR SKIFTE='C' OR ...) because mapserver obviously no longer
> supports the 'in'-statement. I have tried without parenthesis but that did not
> change anything.
>  
> I can't see any pattern in this.
>  
> Björn Danielsson
>> -----Original Message-----
>> From: Michael Smith <michael.smith.erdc at gmail.com>
>> To: Björn Danielsson <bjorn.danielsson at falubo.se>,
>> "mapserver-dev at lists.osgeo.org" <mapserver-dev at lists.osgeo.org>
>> Date: Thu, 14 Jan 2016 09:35:31 -0500
>> Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle
>>  
>> 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
>> <mailto:mapserver-dev-bounces at lists.osgeo.org> > on behalf of Björn
>> Danielsson < bjorn.danielsson at falubo.se <mailto: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
>> <mailto: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-users/attachments/20160115/d1221519/attachment.htm>