<html><body>
<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);">Hi,</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);"> </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);">I have hard to believe that
rendering has anything to do with this.</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);">Oracle and postgres share
the same map file and it works fine with postgres.</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);">Map file:</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);">
<div> LAYER<br />
NAME "X"<br />
GROUP "X"<br />
CONNECTIONTYPE postgis<br />
CONNECTION "host=localhost port=5432
dbname=fwt_prod user=c2 password=C2"<br />
# CONNECTIONTYPE oraclespatial<br />
# CONNECTION "c2/c2a@//192.168.0.104/fwt"<br />
STATUS ON<br />
TYPE POLYGON<br />
MINSCALEDENOM 2000<br />
MAXSCALEDENOM 5000000<br />
# DEBUG 5</div>
<div> CLASS<br />
STYLE<br />
OUTLINECOLOR 255 0 0<br />
END <br />
END<br />
END</div>
<div> </div>
<div>It all worked for Oracle in mapserver version 5.0.2. It did not work in
6.0.3 and not in 7.0.</div>
<div> </div>
<div>I created a map file with DATA and FILTER hardcoded in order to bypass
php:</div>
<div>
<div> LAYER<br />
NAME "X"<br />
GROUP "X"<br />
CONNECTIONTYPE oraclespatial<br />
CONNECTION "c2/c2a@//192.168.0.101/fwt"<br />
DATA "KARTOBJ_SDO FROM c2.KARTA$TMP2"<br />
FILTER "([ID='BJDA1')"<br />
STATUS ON<br />
TYPE POLYGON<br />
MINSCALEDENOM 2000<br />
MAXSCALEDENOM 5000000<br />
DEBUG 5</div>
<div> CLASS<br />
STYLE<br />
OUTLINECOLOR 255 0 0<br />
END <br />
END<br />
END</div>
</div>
</div>
<div style="font-family: tahoma; color: rgb(0, 0, 0); font-size: 13.3333px;
font-weight: 400; font-style: normal; background: none 0% 0% / auto repeat
scroll padding-box border-box rgba(0, 0, 0, 0);">
<div> </div>
<div>Same result. Does not work with filter active, works when the filter
line is commented.</div>
<div> </div>
<div>I have an even more weird example from another script. All three
examples use the same layer, only the filter is different:</div>
<div> </div>
<div>Works -></div>
<div>[Wed Jan 20 08:31:38 2016].604000 msOracleSpatialLayerTranslateFilter.
String: [ORGID]='BSD' AND [FV]='FV1' AND
[BEV]='1' </div>
<div> </div>
<div> </div>
<div>Works -></div>
<div>[Wed Jan 20 08:37:56 2016].674000 msOracleSpatialLayerTranslateFilter.
String: [ORGID]='BSD' AND [FV]='FV1' AND
[SKIFTE]='3232' </div>
<div> </div>
<div> </div>
<div>Does not work -></div>
<div>[Wed Jan 20 08:32:17 2016].418000 msOracleSpatialLayerTranslateFilter.
String: [ORGID]='BSD' AND [FV]='FV1' AND [BEV]='1'
AND ([SKIFTE]='3232') </div>
<div> </div>
<div> </div>
<div>I origininally thought that perhaps three columns in the filter were
max, but other scripts work fine with both four and five columns.</div>
<div> </div>
<div>Reserved words somewhere ?</div>
<div> </div>
<div>Björn D</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 <michael.smith.erdc@gmail.com><br />
To: Björn Danielsson <bjorn.danielsson@falubo.se><br />
Cc: <mapserver-dev@lists.osgeo.org><br />
Date: Mon, 18 Jan 2016 07:43:31 -0500<br />
Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle<br />
<div>No, there is no check on the number of records. In both queries, the
spatial filter is passed but in the second one, there is no other
filter. </div>
<div> </div>
<div>If both queries are returning rows in sqlplus, then I think you need to
look at the rendering side. Perhaps you have something that is preventing
the image from drawing if ID is set?</div>
<div> </div>
<div>You can also increase the DEBUG value to 5 and see the rows produced by
each query but if both are producing rows in sqlplus, they should in
mapserver. </div>
<div> </div>
<div>And the :ordinates bind value does include the sdo_ordinate_array type
being passed. </div>
<div> </div>
<div>First query: </div>
<div> </div>
<div>SELECT ID, rownum, KARTOBJ_SDO FROM c2.KARTA$TMP2 WHERE ID =
'1eokfraqi19niup7402g3iflp7' AND SDO_FILTER( KARTOBJ_SDO,
MDSYS.SDO_GEOMETRY(2003, :srid,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates),'querytype=window')
= 'TRUE'</div>
<div> </div>
<div>Second query: </div>
<div>SELECT rownum, KARTOBJ_SDO FROM c2.KARTA$TMP2 WHERE SDO_FILTER(
KARTOBJ_SDO, MDSYS.SDO_GEOMETRY(2003, :srid,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates),'querytype=window')
= 'TRUE'</div>
<div> </div>
<div>Mike</div>
<div> </div>
<div> </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>
Björn Danielsson <<a href="mailto:bjorn.danielsson@falubo.se">
bjorn.danielsson@falubo.se</a>><br />
<span style="font-weight:bold">Date: </span> Monday, January 18, 2016 at
3:33 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">Cc: </span> <<a
href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a>
><br />
<span style="font-weight:bold">Subject: </span> Re: [EXTERNAL]
[mapserver-dev] Problem with filter in Oracle</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 Michael,</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">Sorry, I did not notice that I only replied to
your private address.</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">Anyway, I followed your advice and set DEBUG 3 in
layer 'X', and made two runs: one with filter set and one with
filter not set. The debug output for both runs is in the included
ms_error.txt-file.</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">The one with filter set produce an empty image.
The one with filter not set works fine.</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 took the statement from 'Using this Sql to
retrieve data', replaced :srid with -1 and :ordinates with
SDO_ORDINATE_ARRAY(393339.5,6773950.0,394169.5,6774780.0) and ran the
statement in sqlplus.</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">Both examples worked.</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">So I am a bit at loss. The only thing I can see in
the debug file is that :ordinates is not declared in 'Bind values'
but the rectangle's extents are. But I don't know what goes on
behind the scene so that might be correct. It feels strange though that this
would affect the filter. Or can it be that oracle spatial detects that the
table ( when filter not set ) contains only one record and therefore skip
the sdo-filtering altogether?</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">Björn D</span></div>
<div style="font-family: tahoma; color: rgb(0, 0, 0); font-size: 13.3333px;
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>
<blockquote style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"><span
id="OLK_SRC_BODY_SECTION">-----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-users@lists.osgeo.org">
mapserver-users@lists.osgeo.org</a>><br />
Date: Fri, 15 Jan 2016 07:04:55 -0500<br />
Subject: Re: [EXTERNAL] [mapserver-dev] Problem with filter in Oracle<br />
</span>
<div><span id="OLK_SRC_BODY_SECTION">Always reply back to the list, others
can assist also.</span></div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></div>
<div><span id="OLK_SRC_BODY_SECTION">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.</span></div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></div>
<div><span id="OLK_SRC_BODY_SECTION">Mike</span></div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></div>
<div>
<div>
<div>
<div><span id="OLK_SRC_BODY_SECTION">----</span></div>
<div><span id="OLK_SRC_BODY_SECTION">Michael Smith</span></div>
</div>
<div><span id="OLK_SRC_BODY_SECTION">US Army Corps</span></div>
<div><span id="OLK_SRC_BODY_SECTION">Remote Sensing GIS/Center</span></div>
<div><span id="OLK_SRC_BODY_SECTION"><a
href="mailto:michael.smith@usace.army.mil">michael.smith@usace.army.mil</a>
</span></div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></div>
</div>
</div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></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 id="OLK_SRC_BODY_SECTION"><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</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"> </span></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;"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">Hi Michael and thanks for your quick
response.</span></span></div>
<div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></div>
<div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">I did 3 runs with debuglevel 3 and the outcome
from the debugger does not say me much.</span></span></div>
<div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></div>
<div style="font-size: 13.3333px; font-family: tahoma; color: rgb(0, 0, 0);
font-weight: 400; font-style: normal;"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">
DEBUG
3<br />
CONFIG
"MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"</span></span>
</div>
<div style="font-family: tahoma; color: rgb(0, 0, 0); font-size: 13.3333px;
font-weight: 400; font-style: normal;">
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">I also
activated the 'debugger' in my php scripts that writes a lot, among
other things the database calls, to a file.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">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.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">A, B
and C refer to the examples in my original mail.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Run A
shows the example:</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
$filter =
"([ID]='".$sessid."')";</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">with
the layers 'AVD' and 'X' where 'AVD' works fine and
'X' does not.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Run B
and C is from the same php script with different contents in filter.</span>
</span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Look
for layers 'AVD' and 'AVD-L'. Actually the same table and
column. Layer 'AVD' shows polygons and layer 'AVD-L' the
polygons' outlines.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Run C
works and run B does not.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">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.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">I
can't see any pattern in this.</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
Björn Danielsson</span></span></div>
<blockquote style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"><span
id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">-----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 />
</span></span>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
style="font-family: Calibri;
font-size: 15px;">Björn,</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
style="font-family: Calibri;
font-size: 15px;">Can you turn on DEBUG level 3 and show what queries are
being generated? </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
style="font-family: Calibri;
font-size: 15px;">That will help isolate whats going on with the queries to
the backend.</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
style="font-family: Calibri;
font-size: 15px;">Mike</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></div>
<div>
<div>
<div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
-- </span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Michael
Smith</span></span></div>
</div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">Remote
Sensing/GIS Center</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">US Army
Corps of Engineers</span></span></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 id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION">
</span></span></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"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">Hi,</span></span></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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span> </span></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 id="OLK_SRC_BODY_SECTION"><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></span>
</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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span> </span></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 id="OLK_SRC_BODY_SECTION"><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></span>
</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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span> </span></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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">Thera are no problems with postgres.</span></span>
</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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span> </span></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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">With Oracle there is a funny problem with
filter.</span></span></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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">Look at this:</span></span></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 id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span> </span></span>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> if ($oLayerK->name == "AVD"
&& $avd == "") {<br />
$oLayerK->set("status",MS_OFF);<br />
continue;<br />
}</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">A mapfile with two layers, 'AVD' and
'X'.</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">The filter for layer 'AVD' works
fine.</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">For example:</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">This works</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> $filter =
"([ORGID]='".$orgid."' AND
[FV]='".$fv."' AND
[BEV]='".$bev."')";</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">But this does not work</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span>
</div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><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>
</span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">I am at loss here.</span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION"> </span></span></span></div>
<div><span id="OLK_SRC_BODY_SECTION"><span id="OLK_SRC_BODY_SECTION"><span
id="OLK_SRC_BODY_SECTION">Björn Danielsson</span></span></span></div>
</div>
</div>
</div>
</blockquote>
</blockquote>
</div>
</div>
</div>
</blockquote>
</blockquote>
</div>
</div>
</div>
</blockquote>
</blockquote>
</div>
</body></html>