SQL Syntax

4164
9
05-28-2015 07:16 AM
BledarBirbo1
Occasional Contributor

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.

0 Kudos
9 Replies
ChristianWells
Esri Regular Contributor

Hi Bledar,

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

0 Kudos
BledarBirbo1
Occasional Contributor

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

0 Kudos
BledarBirbo1
Occasional Contributor

Hi Chris.

Did you had a chance to check this ?

Thanks.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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
Occasional Contributor III

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+.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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.

0 Kudos
BledarBirbo1
Occasional Contributor

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

Thanks Angelo.

0 Kudos
BillDaigle
Occasional Contributor III

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. 

0 Kudos
BledarBirbo1
Occasional Contributor

Hi Bill

Thanks for your reply.

The data is not copied to the server.

0 Kudos