topic Re: SQL Syntax in ArcGIS Enterprise Questions

SQL Syntax

BledarBirbo1 — Thu, 28 May 2015 14:16:51 GMT

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.

Re: SQL Syntax

ChristianWells — Thu, 28 May 2015 15:18:39 GMT

Hi Bledar,

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

Re: SQL Syntax

BledarBirbo1 — Thu, 28 May 2015 15:22:27 GMT

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

Re: SQL Syntax

BledarBirbo1 — Mon, 01 Jun 2015 11:41:55 GMT

Hi Chris.

Did you had a chance to check this ?

Thanks.

Re: SQL Syntax

BillDaigle — Mon, 01 Jun 2015 20:36:07 GMT

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.

Re: SQL Syntax

VinceAngelo — Tue, 02 Jun 2015 16:36:16 GMT

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

- V

Re: SQL Syntax

BillDaigle — Tue, 02 Jun 2015 16:58:49 GMT

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

Re: SQL Syntax

VinceAngelo — Tue, 02 Jun 2015 17:07:16 GMT

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.

Re: SQL Syntax

BledarBirbo1 — Tue, 02 Jun 2015 20:29:56 GMT

Hi Bill

Thanks for your reply.

The data is not copied to the server.

Re: SQL Syntax

BledarBirbo1 — Tue, 02 Jun 2015 20:31:07 GMT

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

Thanks Angelo.