Oracle driver: remove BLOB/CLOB columns instead of changing them to null

[bartvde]

Currently, when a table has a BLOB/CLOB column, the name of the column is subsituted with null, so e.g.:

SELECT OBJECTID, OBJECTID, VOOR, LETTER, CYFER, KALK, ACHTER, SCHOP, HEL, GWT, CODE, OMSCHR, EERSTE_BOD, EERSTE_GWT, KLEUR_BOD, KLEUR_GWT, AREA, PERIMETER, null, SHAPE FROM BODEM.BODEMKAART50 WHERE ( (CODE like '%M%' escape '!') And (ACHTER like '%W%' escape '!') ) AND ROWNUM<=100 AND SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'

However, buy default this will end up in the WFS output as well.

A better way would be to completely remove the column from the list of columns.

Also, while we are at it, we might want to exclude duplicate columns, like OBJECTID which is used twice in the query.

[dmorissette]

I agree with both of your suggestions, but since they imply non-trivial changes, I'd suggest we do them only on 6.0 and not in 5.6.1.

Alan, can you please see what can be done?

[aboudreault]

yep, I'll check this one.

[aboudreault]

bartvde, Is the duplicate OBJECTID only with a WFS query ? I don't see that with my wms test.

[msmitherdc]

alan, its a function of that column being listed as the UNIQUE ID column. It gets added to the column list and if all columns are included in the column list, its gets duplicated.

[aboudreault]

I was not sure if a case where the unique field is not in the layer items list (other than rownum), so I prefered to add the code to detect if it is in or not.

Fixed and committed in r9616.

[aboudreault]

For the record, both problems has been fixed: the BLOB field is now removed and the duplicate unique field removed too.

[msmitherdc]

Looks like an extra comma is being left in when the statement is being generated. If I have a line of

DATA "shape from MVK.LEVEE_CENTERLINE using srid 8265"

I get

[Wed Dec 23 17:37:05 2009].535682 msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error: ORA-00936: missing expression

. Query statement: SELECT rownum _{SHAPE FROM MVK.LEVEE_CENTERLINE WHERE SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE' . Check your data statement.}

[aboudreault]

thansk msmitherdc for the report, I'm going to fix that in the next few days

[aboudreault]

msmitherdc, was you testing the trunk or you only applied the patch of the changeset r9616 ? I had committed another changeset to fix that: r9617.

[msmitherdc]

I was testing trunk. Last applied changeset i tested was r9618.

u4rt9mds@maps:/usr/local/src/ms/mapserver_trunk/mapserver$ svn log maporaclespatial.c

---

r9618 | aboudreault | 2009-12-15 14:40:50 -0500 (Tue, 15 Dec 2009) | 1 line

[aboudreault]

The problem was the sql query generation when there is no items in the list. Fixed and committed in r9644. msmitherdc, may you confirm that it fixes your bug please?

Also, I've notified a small regression since the "duplicate columns in the sql query when using unique" fix. With the following data statement:

DATA "ORA_GEOMETRY FROM popplace USING UNIQUE Name SRID 4326"

Before, the following sql query generated was:

SELECT Name, Name, ORA_GEOMETRY FROM popplace WHERE [...]

and was working. Now with the fix, the sql query is:

SELECT Name, ORA_GEOMETRY FROM popplace WHERE 

which throws the oracle error below:

ORA-00932: inconsistent datatypes: expected %s got %s. Query statement: [...]

not sure if my test is valid but that kind of data statement with the unique parameter was working before.

[msmitherdc]

aboudreault, its working fine for me now. Also data statements I have with USING UNIQUE SRID XXXX are working fine as well.

[aboudreault]

Is your UNIQUE value already in the items list even if you don't use UNIQUE? That's my case and it's not working.

[msmitherdc]

Ahh. I just tried your case and got the same error.

I'm also getting internal server errors when drawing some 4d data. Its working fine at 5.6.

[aboudreault]

bartvde, it looks like the duplicate field is needed for an unknown reason. Do you have any objection if I revert this change ?

[bartvde]

Hi Alan, no go ahead. I assume the null change will remain? That was my main issue here.

[aboudreault]

Absolutely, this change will remain.

[aboudreault]

Finally, both changes will remain, everything is working fine since r9668, a change about the wfs pagination.

msmitherdc, May you confirm if this changeset fixed your seg fault? Your segfault may be caused by something not related to this ticket.

[aboudreault]

Considering this done. Reopen the ticket if needed.