FDO 3.5 ArcSDE Provider Oracle ORA-00920

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

FDO 3.5 ArcSDE Provider Oracle ORA-00920

maland
The following C# code:

<snippet>
select.SetFilter("( XROW = 0 OR XROW = 1) AND YROW = 0");
IFeatureReader reader = select.Execute();
  while (reader.ReadNext())
</snippet>

results in:
Stream fetch failed.
   at OSGeo.FDO.Commands.Feature.IReaderImp.ReadNext()

Nesting down the inner exceptions reveals the
"Invalid SQL syntax (-42)" and "ORA-00920: invalid relational operator"

I'm tried this both in 3.5rc1 and 3.4.1. We are using Oracle 11.1.

It seems like the ArcSDE provider don't allow me to have combinations of OR and AND operators. Trying the exact same filter using i.e. SHP provider runs perferctly.

Any ideas what is going on?

Regards
Henning Maland
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

Jackie Ng
I think something must be wrong with the expression handling in the ArcSDE provider

I know I had problems in the past using aggregate functions using string expressions, but was able to work around it by using the actual API classes.

Example:

http://code.google.com/p/fdotoolbox/source/detail?r=1054&path=/trunk/FdoToolbox.Base/Controls/FdoDataPreviewCtlPresentation.cs

Perhaps something is similar wrong with the filters?

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

maland
Unfortunately using the API classes gives me the same result. I tried the following:

              ComparisonCondition pCompareX0 = new ComparisonCondition(new Identifier("XROW"), ComparisonOperations.ComparisonOperations_EqualTo, new Int32Value(0));
              ComparisonCondition pCompareX1 = new ComparisonCondition(new Identifier("XROW"), ComparisonOperations.ComparisonOperations_EqualTo, new Int32Value(1));
              ComparisonCondition pCompareY0 = new ComparisonCondition(new Identifier("YROW"), ComparisonOperations.ComparisonOperations_EqualTo, new Int32Value(0));

              BinaryLogicalOperator pOrOperationX = new BinaryLogicalOperator(pCompareX0, BinaryLogicalOperations.BinaryLogicalOperations_Or, pCompareX1);
              BinaryLogicalOperator pAndOperation = new BinaryLogicalOperator(pOrOperationX, BinaryLogicalOperations.BinaryLogicalOperations_And, pCompareY0);
              select.Filter = pAndOperation;



- Henning
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

maland
I've been able to find the SQL generated by the ArcSDEFilterToSql class, and there is obviously a problem with the parantheses matching. Example of SQL generated:

SELECT  XROW, YROW FROM EXAMPLE_POINTS WHERE YROW = 0  AND  ( ( ( (XROW)  =  (0)  ( OR  ( (XROW)  =  (1) ) ) )

Hopefully the authors of the FDO ArcSDE provider can have a look at this?


Regards
Henning
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

oyvind idland
I get the same problem here.

It seems to me that the SQL generator goes a bit too far in wrapping everything in parenthesis ?

Unfortunately it seems that the SDE provider is currently broken.


-- Oyvind


On Fri, Oct 1, 2010 at 10:27 AM, maland <[hidden email]> wrote:

I've been able to find the SQL generated by the ArcSDEFilterToSql class, and
there is obviously a problem with the parantheses matching. Example of SQL
generated:

SELECT  XROW, YROW FROM EXAMPLE_POINTS WHERE YROW = 0  AND  ( ( ( (XROW)  =
(0)  ( OR  ( (XROW)  =  (1) ) ) )

Hopefully the authors of the FDO ArcSDE provider can have a look at this?


Regards
Henning
--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/FDO-3-5-ArcSDE-Provider-Oracle-ORA-00920-tp5587468p5590547.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users


_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

maland
I have debugged the ArcSDE provider source and identified the problem. It seems to be related to the "nesting" in function ArcSDEFilterToSql::ProcessBinaryLogicalOperator. It happens when both operands are "attribute"-style filters. The parenthesis matching is broken. Where can I post the bugfix proposal so it can be included in the next release?

Regards
Henning Maeland

oyvind idland wrote
I get the same problem here.

It seems to me that the SQL generator goes a bit too far in wrapping
everything in parenthesis ?

Unfortunately it seems that the SDE provider is currently broken.


-- Oyvind


On Fri, Oct 1, 2010 at 10:27 AM, maland <hma@powel.com> wrote:

>
> I've been able to find the SQL generated by the ArcSDEFilterToSql class,
> and
> there is obviously a problem with the parantheses matching. Example of SQL
> generated:
>
> SELECT  XROW, YROW FROM EXAMPLE_POINTS WHERE YROW = 0  AND  ( ( ( (XROW)  =
> (0)  ( OR  ( (XROW)  =  (1) ) ) )
>
> Hopefully the authors of the FDO ArcSDE provider can have a look at this?
>
>
> Regards
> Henning
> --
> View this message in context:
> http://osgeo-org.1803224.n2.nabble.com/FDO-3-5-ArcSDE-Provider-Oracle-ORA-00920-tp5587468p5590547.html
> Sent from the FDO Users mailing list archive at Nabble.com.
> _______________________________________________
> fdo-users mailing list
> fdo-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>

_______________________________________________
fdo-users mailing list
fdo-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

Jackie Ng
Make a trac ticket and attach a patch for the source file in question

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

Romica Dascalescu
In reply to this post by maland
Hi Henning,

I had a look at your change and it's OK, however I would propose some additional changes to remove class FilterAnalyzer and flags mFilterAnalyzed, mUseNesting.
This will simplify the code and will make possible use less brackets in a filter. The same method is used in FDO & SQLite filter ToString in order to avoid too many brackets.
I attached a code change (ArcSDE.patch) to the ticket (http://trac.osgeo.org/fdo/ticket/719).
Could you please have a loot at the code change proposed and test it if possible and let me know your opinion?

Regards,
Romy.

________________________________________
From: [hidden email] [[hidden email]] on behalf of maland [[hidden email]]
Sent: Wednesday, November 03, 2010 4:34 AM
To: [hidden email]
Subject: [fdo-users] Re: FDO 3.5 ArcSDE Provider Oracle ORA-00920

I have debugged the ArcSDE provider source and identified the problem. It
seems to be related to the "nesting" in function
ArcSDEFilterToSql::ProcessBinaryLogicalOperator. It happens when both
operands are "attribute"-style filters. The parenthesis matching is broken.
Where can I post the bugfix proposal so it can be included in the next
release?

Regards
Henning Maeland


oyvind idland wrote:

>
> I get the same problem here.
>
> It seems to me that the SQL generator goes a bit too far in wrapping
> everything in parenthesis ?
>
> Unfortunately it seems that the SDE provider is currently broken.
>
>
> -- Oyvind
>
>
> On Fri, Oct 1, 2010 at 10:27 AM, maland <[hidden email]> wrote:
>
>>
>> I've been able to find the SQL generated by the ArcSDEFilterToSql class,
>> and
>> there is obviously a problem with the parantheses matching. Example of
>> SQL
>> generated:
>>
>> SELECT  XROW, YROW FROM EXAMPLE_POINTS WHERE YROW = 0  AND  ( ( ( (XROW)
>> =
>> (0)  ( OR  ( (XROW)  =  (1) ) ) )
>>
>> Hopefully the authors of the FDO ArcSDE provider can have a look at this?
>>
>>
>> Regards
>> Henning
>> --
>> View this message in context:
>> http://osgeo-org.1803224.n2.nabble.com/FDO-3-5-ArcSDE-Provider-Oracle-ORA-00920-tp5587468p5590547.html
>> Sent from the FDO Users mailing list archive at Nabble.com.
>> _______________________________________________
>> fdo-users mailing list
>> [hidden email]
>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>
>
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>
>

--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/FDO-3-5-ArcSDE-Provider-Oracle-ORA-00920-tp5587468p5700469.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users