<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif;"><div>Always reply back to the list, others can assist also.</div><div><br></div><div>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.</div><div><br></div><div>Mike</div><div><br></div><div><div><div><div>----</div><div>Michael Smith</div></div><div>US Army Corps</div><div>Remote Sensing GIS/Center</div><div>michael.smith@usace.army.mil</div><div><br></div></div></div><div><br></div><span id="OLK_SRC_BODY_SECTION"><div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt"><span style="font-weight:bold">From: </span> Björn Danielsson <<a href="mailto:bjorn.danielsson@falubo.se">bjorn.danielsson@falubo.se</a>><br><span style="font-weight:bold">Date: </span> Friday, January 15, 2016 at 2:40 AM<br><span style="font-weight:bold">To: </span> Michael Smith <<a href="mailto:michael.smith.erdc@gmail.com">michael.smith.erdc@gmail.com</a>><br><span style="font-weight:bold">Subject: </span> Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle<br></div><div><br></div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT: #b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;"><div><div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;">Hi Michael and thanks for your quick
response.</div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"> </div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;">I did 3 runs with debuglevel 3 and
the outcome from the debugger does not say me much.</div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"> </div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;">
DEBUG
3<br>
CONFIG
"MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"</div><div style="font-family: tahoma; color: rgb(0, 0, 0); font-size: 13.3333px;
font-weight: 400; font-style: normal;"><div> </div><div>I also activated the 'debugger' in my php scripts that writes a
lot, among other things the database calls, to a file.</div><div> </div><div>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.</div><div> </div><div>A, B and C refer to the examples in my original mail.</div><div> </div><div>Run A shows the example:</div><div> $filter =
"([ID]='".$sessid."')";</div><div>with the layers 'AVD' and 'X' where 'AVD' works
fine and 'X' does not.</div><div> </div><div>Run B and C is from the same php script with different contents in
filter.</div><div>Look for layers 'AVD' and 'AVD-L'. Actually the same
table and column. Layer 'AVD' shows polygons and layer
'AVD-L' the polygons' outlines.</div><div>Run C works and run B does not.</div><div>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.</div><div> </div><div>I can't see any pattern in this.</div><div> </div><div>Björn Danielsson</div><blockquote style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">-----Original
Message-----<br>
From: Michael Smith <<a href="mailto:michael.smith.erdc@gmail.com">michael.smith.erdc@gmail.com</a>><br>
To: Björn Danielsson <<a href="mailto:bjorn.danielsson@falubo.se">bjorn.danielsson@falubo.se</a>>,
"<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>"
<<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>><br>
Date: Thu, 14 Jan 2016 09:35:31 -0500<br>
Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle<br>
<div><span style="font-family: Calibri; font-size: 15px;">Björn,</span></div><div> </div><div><span style="font-family: Calibri; font-size: 15px;">Can you turn on
DEBUG level 3 and show what queries are being generated? </span></div><div> </div><div><span style="font-family: Calibri; font-size: 15px;">That will help
isolate whats going on with the queries to the backend.</span></div><div> </div><div><span style="font-family: Calibri; font-size: 15px;">Mike</span></div><div> </div><div><div><div><div>-- </div><div>Michael Smith</div></div><div>Remote Sensing/GIS Center</div><div>US Army Corps of Engineers</div></div></div><div style="font-family:Calibri; font-size:11pt; text-align:left;
color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none;
PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP:
#b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt"><span id="OLK_SRC_BODY_SECTION"><span style="font-weight:bold">From: </span>
mapserver-dev <<a href="mailto:mapserver-dev-bounces@lists.osgeo.org">
mapserver-dev-bounces@lists.osgeo.org</a>> on behalf of Björn
Danielsson <<a href="mailto:bjorn.danielsson@falubo.se">
bjorn.danielsson@falubo.se</a>><br><span style="font-weight:bold">Date: </span> Thursday, January 14, 2016 at
4:30 AM<br><span style="font-weight:bold">To: </span> "<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>
" <<a href="mailto:mapserver-dev@lists.osgeo.org">
mapserver-dev@lists.osgeo.org</a>><br><span style="font-weight:bold">Subject: </span> [EXTERNAL] [mapserver-dev]
Problem with filter in Oracle<br><span style="font-weight:bold">Resent-From: </span> Michael Smith <<a href="mailto:michael.smith@usace.army.mil">michael.smith@usace.army.mil</a>
></span></div><div> </div><blockquote id="MAC_OUTLOOK_ATTRIBUTION_BLOCKQUOTE" style="BORDER-LEFT:
#b5c4df 5 solid; PADDING:0 0 0 5; MARGIN:0 0 0 5;"><div><div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">Hi,</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION"> </span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">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.</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION"> </span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">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.</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION"> </span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">Thera are no problems with postgres.</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION"> </span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">With Oracle there is a funny problem with
filter.</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION">Look at this:</span></div><div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);"><span id="OLK_SRC_BODY_SECTION"> </span><div><span id="OLK_SRC_BODY_SECTION">foreach($aLayersIdx as $layerIndex)
{<br>
$oLayerK = $oMapK->getLayer($layerIndex);<br>
if ($bDebug) {<br>
fwrite($f,"layer: ".$oLayerK->name."\r\n");<br>
}</span></div><div><span id="OLK_SRC_BODY_SECTION"> if ($oLayerK->name ==
"AVD" && $avd == "") {<br>
$oLayerK->set("status",MS_OFF);<br>
continue;<br>
}</span></div><div><span id="OLK_SRC_BODY_SECTION"> $filter = "";<br>
switch ($oLayerK->name) {<br>
case "AVD" :<br>
$filter = "([ORGID]='".$orgid."'
AND [FV]='".$fv."' AND [BEV]='".$bev."'
AND [SKIFTE]='".$skifte."' AND
[AVD]='".$avd."')";<br>
$data = "KARTOBJ_SDO FROM
".$dbowner.".AVD*AVD";<br>
break;<br>
<br>
case "X" :<br>
$filter =
"([ID]='".$sessid."')";<br>
$data = "KARTOBJ_SDO FROM
".$dbowner.".KARTA*TMP2";<br>
break;<br>
}<br>
<br>
$data = str_replace("*","$",$data);<br>
<br>
$oLayerK->set("data", $data);<br>
$oLayerK->setFilter($filter);<br>
$oLayerK->setConnectionType(MS_ORACLESPATIAL);<br>
$oLayerK->set("connection", $conn);<br>
$oLayerK->set("status", MS_ON);<br>
if ($bDebug) {<br>
fwrite($f,"connection: ".$conn."\r\n");<br>
fwrite($f,"filter: ".$filter."\r\n");<br>
fwrite($f,"data: ".$data."\r\n");<br>
} <br>
}</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">A mapfile with two layers,
'AVD' and 'X'.</span></div><div><span id="OLK_SRC_BODY_SECTION">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.</span></div><div><span id="OLK_SRC_BODY_SECTION">The filter for layer 'AVD'
works fine.</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">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.</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">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.</span></div><div><span id="OLK_SRC_BODY_SECTION">For example:</span></div><div><span id="OLK_SRC_BODY_SECTION">This works</span></div><div><span id="OLK_SRC_BODY_SECTION"> $filter =
"([ORGID]='".$orgid."' AND
[FV]='".$fv."' AND
[BEV]='".$bev."')";</span></div><div><span id="OLK_SRC_BODY_SECTION">But this does not work</span></div><div><span id="OLK_SRC_BODY_SECTION"><span style="font-family: tahoma;
font-size: 13.3333px;"> $filter =
"([ORGID]='".$orgid."' AND
[FV]='".$fv."' AND [BEV]='".$bev."' AND
[SKIFTE]='".$skifte."')";</span></span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">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.</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">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.</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">I am at loss here.</span></div><div><span id="OLK_SRC_BODY_SECTION"> </span></div><div><span id="OLK_SRC_BODY_SECTION">Björn Danielsson</span></div></div></div></div></blockquote></blockquote></div></div></div></blockquote></span></body></html>