Issue with php_mapscript and Oracle

[Bjorn1234]

Hi,

I have a web system that is heavily batabase dependent, and can use both Oracle and Postgres as database according to the user's request. This means that the two databases are identical, the tables are identical, the constraints are identical, the two sets of php-scripts are identical ( except for the database calls ) and so on.

I have noticed a problem with Oracle.
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");

}

}

For layer 'X' filter does not work. Or rather it works but mapserver can't find any record. If the line '$filter = "([ID]='".$sessid."')";' is commented ( and the table contains one record ), it works. It seems that the select based on filter gets incorrect.
Filter for layer 'AVD' works allright.
No hint of errors in the debug file.

I have got other similar funny errors with Oracle filter for other tables. Some column names sometimes do not work ( that is no records are found ). Sometimes they work but not together with certain other columns in the filter. The error is consistent in that way that a certain filter never works, but another, almost similar filter for an other table always works. I have not found any pattern in this.

Björn D

[Jeff McKenna]

You could create a small test case and then send that package for testing to the MapServer-dev list. Mike Smith maintains the Oracle filtering code, and lives on the dev list. Maybe your test package could contain an Oracle test and a spatialite test (so Mike doesn't have to install Postgres).

[Jeff McKenna]

Handled through MapServer ticket ​https://github.com/mapserver/mapserver/issues/5229