nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Account for apostrophes in attribute values in Arcade FeatureSetByRelationshipName function query

FrankMartin1

We have data that includes values that contain apostrophes. When using the Arcade FeatureSetByRelationshipName function is fails because the sql query where clause that is created is structured like this:

name = 'Billy Bob's Cavern'

The single apostrophe in the value needs to be escaped by a second apostrophe to be an expectable where clause:

name = 'Billy Bob''s Cavern'

Can the FeatureSetByRelationshipName be updated to account for this situation? Is there another way to handle this short of changing the value to remove the apostrophe?

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

JustinReynolds

It is also not escaped in the url encoded request. You would see ...%27Pricilla%27%27s....

In the browser it is escaped. In the runtime it is not. So the bug seems to be in the runtime... which is then being reported via Field Maps.

I think I was running into this before with Filter. Where SQL escapes correctly in the browser. So testing it in the authoring app works... but we get an error in Field Maps. In this case I had the option of removing the single quote with replace in my $feature and removing them by hand from the dataset containing the features I would be fetching to get around this.

Have you submitted a bug for issue in the ArcGIS Runtime?

FrankMartin1

Yes, I agree. I think it's a bug. Here is the log from the Field Maps application. This function is being used in expression to retrieve attributes from a specific feature to prepopulate attributes in an inspection report (related table). You can see that the apostrophe is not escaped in the where clause. FYI, I replaced some of the information with xyz for security.

Name: expr/park-or-preserve-name

Error: Error Domain=com.esri.arcgis.runtime.services.error Code=500 "Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')]." UserInfo={NSURL=https://xyz.xyz/maps/rest/services/Hosted/Parks_xyz_Caves/FeatureServer/0/query?outSR=3857&returnGeometry=true&returnZ=true&where=%281%3D1%29%20AND%20%28caves%20%3D%20'Pricilla's%20Well%20Cave'%29&outFields=%2A&returnDistinctValues=false&resultRecordCount=1&f=json&returnM=true, responseJSON={ error = { code = 500; details = ( ); message = "Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')]."; }; }, AGSCredentialErrorKey=username: xyz

token: GH_VLtRuJDZjSejMCR4klNuXZw_K4UU98HqJ-H-7QzPeMv8p6_D7bNwIXUhuYrSwNckjMkl89iAehiTGe4Xpm-ySRQkHxm30a7z9pgMJtwQrHM2sIVZYl2YaeegJeb4c_ziJMSc42nevHo4Au599YVlLXrwnFUo0DGqiWJO0w6WNnfK_g_0X4gApSOc7dIyDJtut0jJDpDY2LMT6q6HzVMHZAE5giYaZe8VJlCz1dMnIH9N-H3BMmkxdojv6Jy3I8hiVer58Y1JqOyyz0HdzAH7Qg5r7Nwkk5qjj_acxQdQ.

tokenURL: xyz

tokenExpiration: 2023-02-03 23:24:10 +0000 , NSLocalizedDescription=Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')].}

JustinReynolds

Ah. I see what you mean. FeatureSetByRelationshipName takes an inputFeature. And you are passing $feature.

The docs for this function state that this feature must be coming form a feature service... that isn't very clear to me as to whether or not you must use $feature or if you could getaway with Feature(<some modified feature>).

You could try to handle this by creating a modified Feature from $feature. Loop through your features attributes and remove apostrophes. Then create a Feature object from that modified feature dict. Then pass that to FeatureSetByRelationshipName. I'm not convinced that something other than $feature will work, but might be worth a shot.

Maybe I'm reading your question wrong. Maybe it won't be possible if you are needing to match on the field that contains apostrophe...

Testing this in the ArcGIS Playground... I can see the payload escaping the apostrophe.

By the way, this sounds like a bug.

FrankMartin1

Thanks @JustinReynolds,

Since this function uses the feature which is immutable as an argument, i don't think the value can be changed. I can see in the Field Maps log that the query fails because the apostrophe isn't escaped. Since the apostrophe is a legal character, I was hoping that the Arcade function, FeatureSetByRelationshipName, would account for this since it uses a string to match the feature.

Thanks again for your idea.

JustinReynolds

Try substituting an Arcade variable in the SQL def.

var features = FeatureSet('{"fields":[{"alias":"name","name":"name","type":"esriFieldTypeString"},{"alias":"ELEV_m","name":"ELEV_m","type":"esriFieldTypeInteger"}],"spatialReference":{"wkid":4326},"geometryType":"esriGeometryPoint","features":[{"geometry":{"spatialReference":{"wkid":4326},"x":-151.0063,"y":63.069},"attributes":{"name":"sponge bob' + `'` +  's","ELEV_m":6168}}]}')

var name = "sponge bob's"
var sql = "name=@name";

var ffset = Filter(features, sql)
ffset

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.