SQL Syntax

[BledarBirbo1]

Hi.

I have an oracle table with SDO geometry and i am publishing this as a Feature service. I have registered the Database connection on the ArcGIS server and it publishes without any warnings.

When i query the layer from arcmap (on the MXD that i use to publish) it will accept Oracle SQL syntax as query definition.

When i query the feature layer from arcmap (after adding it as a feature layer from arcgis server) it will not accept Oracle SQL Syntax.

What exactly is happening to the SQL Query on ArcGIS server ?

Using ArcGIS Server 10.2.2

Thanks.

[ChristianWells]

Hi Bledar,

Can you please post a snippet of the SQL you are using?

[BledarBirbo1]

Hi Chris.

Here is the query that i tried.

((STATUS_DESCRIPTION like 'Ε%') And ((INITIAL_SUBMISSION_TIME >= TO_DATE('2015/01/01', 'yyyy/mm/dd')) And (INITIAL_SUBMISSION_TIME < TO_DATE('2015/05/01', 'yyyy/mm/dd'))))

As you see its ORACLE specific.

Thanks

[BledarBirbo1]

Hi Chris.

Did you had a chance to check this ?

Thanks.

[VinceAngelo]

You may also be running into protections added to prevent SQL injection attacks.  Here's the 10.3 documentation page on the topic.

- V

[BillDaigle]

Looks like the standardized query functionality was added a 10.2.  Although I'm able to run Oracle specific commands, I'm working on a 10.1 server.  I suspect the same would not be true on 10.2+.

[VinceAngelo]

You may be able to prevent Oracle-specific queries in your 10.1 SP1 instance if you apply the ArcGIS 10.1 SP1 for Server Security Patch.

[BledarBirbo1]

Probably  "standardized query" is preventing this but i will check it tomorrow and let you know.

Thanks Angelo.

[BillDaigle]

Bledar,  you may have already done this, but it might be worth double checking to make sure the data did not get copied to the server.  I've been bitten by this before.

We have a 10.1 server with Oracle SDE and I am able to apply standard oracle queries to the service layers (via REST or ArcMap).  I just tried a "TO_DATE" call and a wildcard call without a problem. 

[BledarBirbo1]

Hi Bill

Thanks for your reply.

The data is not copied to the server.