topic Re: ArcGIS Server query in ArcGIS Enterprise Questions

ArcGIS Server query

ShaningYu — Wed, 26 Aug 2015 17:58:42 GMT

I tested a query on a FC in ArcMap and the published mapservice layer in ArcGIS Server simultaneously. In ArcMap, the query is not case-sensitive. However, it is case sensitive in ArcGIS Server. Is there a way to set the query case-insensitive in the published service layer? Thanks.

Re: ArcGIS Server query

ChrisSergent — Wed, 26 Aug 2015 18:16:11 GMT

What is FC? And how are you trying to run the query in against the published service layer? Is it JavaScript? If so, you can use UCase in JavaScript to convert values to uppercase so that they match.

Re: ArcGIS Server query

ShaningYu — Wed, 26 Aug 2015 18:19:42 GMT

FC = feature class. This FC is published as a mapservice layer. I'm using ESRI cmv project template. The serach function provided in that project is case-sensitive. I also posted a thread there: https://community.esri.com/message/546455#546455

Besides I also tested the query string: Upper(FName) like 'theValue'. It does not work.

Re: ArcGIS Server query

ChrisSergent — Wed, 26 Aug 2015 18:38:40 GMT

Is your feature service public? And do you have an example expression that you are trying?

Re: ArcGIS Server query

ShaningYu — Wed, 26 Aug 2015 18:47:00 GMT

Not public. I tested the query string:

(Upper)MON_TYPE LIKE 'theValue'

and

MON_TYPE LIKE Upper('theValue')

but neither works (see Search Tool - Case sensitive ). Thanks.

Re: ArcGIS Server query

KenBuja — Wed, 26 Aug 2015 18:54:27 GMT

You can search using Upper (or Lower) when querying a service. You have make sure what you're querying is in the expected case.

For example, I have a field name Comments that contains strings like "Coral" and "coral".

Both of these statements returns all expected records:

lower(Comments) like '%coral%'

upper(Comments) like '%CORAL%'

but this doesn't return any records

lower(Comments) like '%Coral%'

Re: ArcGIS Server query

ShaningYu — Wed, 26 Aug 2015 19:02:59 GMT

Tested like upper(NAME) LIKE upper('b04') in ArcGIS Server successfully. Thanks.

Re: ArcGIS Server query

TanuHoque — Thu, 27 Aug 2015 16:58:47 GMT

FYI: case sensitivity in query depends on the underlying database and its settings. Clients like ArcMap or ArcGIS Server simply passes the sql where-clause to the underline database....

are you publishing by copying data on to the server and in that process the data gets copied to a different type of database? that may explain why it works in ArcMap but not on ArcGIS Server.

Re: ArcGIS Server query

ShaningYu — Thu, 27 Aug 2015 18:10:20 GMT

It is OK on ArcGIS Server query. My real prolem is in https://community.esri.com/message/546456#546456 I haven't found the solution for the query string in CMV JS application. Thanks.

Re: ArcGIS Server query

TanuHoque — Thu, 27 Aug 2015 18:17:12 GMT

the link you put in your reply takes brings me back to this thread... can you please check the link?

Re: ArcGIS Server query

ShaningYu — Thu, 27 Aug 2015 18:29:52 GMT

Sorry. Should be Search Tool - Case sensitive

Re: ArcGIS Server query

DavidParsons1 — Thu, 27 Aug 2015 18:36:58 GMT

Just be sure to check .. on *very large* record sets .

by using constructs like this :-

lower(Comments) like '%coral%'

upper(Comments) like '%CORAL%'

you may prevent the underlying database from using any indexes on, in this case, the 'Comments' column. The index may be case sensitive, and as soon as the DB sees a function applied to column in may not be able to rely on the index and so will often ignore it, forcing a full table scan and causing a potential performance hit.

May not apply in your case .. just a heads up