topic Re: JS API Query URL Parameters unable to handle apostrophes in ArcGIS JavaScript Maps SDK Questions

JS API Query URL Parameters unable to handle apostrophes

luckachi — Wed, 17 Feb 2021 15:41:17 GMT

Using this example: https://community.esri.com/t5/arcgis-api-for-javascript/passing-url-parameters-to-query-feature-layers/td-p/284882

I pass the species name via the parameter 'cname' (ie: cname=Alewife) It seems to work just fine with names that have parentheses (cname=Phragmites+%28Invasive%29) and dashes (cname=Woodland+forget-me-not) but it doesn't seem to query correctly when an apostrophe appear in the name. I've tried a few different string replaces, splitting etc but nothing seems to be working.

For example, in the console log I have the expr variable being written out and that reads:

Q_NAME = 'Baby's breath'

and in my ArcGIS Server Manager I am receiving the following error message:

An invalid where clause or definition expression has been requested: "Q_NAME = 'Baby's breath'"

but when I change the expression to:

var expr = 'Q_NAME = "' + species + '"';

If receive the following error message:

Geodatabase error: Underlying DBMS error [GIS.Layer][STATE_ID = 0].

I don't quite know what I am doing wrong as the species variable is printing out correctly in the console.log but is not querying the feature layer correctly.

Re: JS API Query URL Parameters unable to handle apostrophes

jcarlson — Wed, 17 Feb 2021 15:57:13 GMT

In a query, 'string' is a string, but "string" is a column named string. For that reason, single and double-quotes are not interchangeable when constructing a query statement.

To escape a single quote in SQL, you simply give it twice, i.e., 'Baby''s breath'. That's two single-quote characters, not a double-quote. I put it in monospace font to make it clearer.

Which poses a problem when the table stores it with one, but a simple replace of ' → '' should do the trick.

To demonstrate that the query functions properly with two single-quotes, here's a query of my county's parcels, with the query statement owner_name like '%''%'.

You'll see that the query successfully evaluates and returns all parcels with a single-quote somewhere in the name.

{ "exceededTransferLimit": false, "features": [ { "attributes": { "owner_name": "PATRICK J & NANCY M O'CONNELL" } }, { "attributes": { "owner_name": "RANDALL ROBINSON OBRA '93 TRUST NORTHERN TRUST CO GRDN & TR" } }, ... ]}

Re: JS API Query URL Parameters unable to handle apostrophes

luckachi — Thu, 18 Feb 2021 16:23:32 GMT

You are awesome @jcarlson - thank you!

Re: JS API Query URL Parameters unable to handle apostrophes

MatthewDriscoll — Wed, 17 Feb 2021 16:36:50 GMT

This is some really good information! I am surprised this has not tripped me up too.