topic "Unable to perform query operation.", "Invalid query" in ArcGIS REST APIs and Services Questions

"Unable to perform query operation.", "Invalid query"

LuisDelValle — Tue, 28 Sep 2021 08:33:03 GMT

Hi there,

I have a backend component that is sending a few requests to our ArcGIS server (Note: both Ids 2 and 8 are pointing to Views in the DB). The first request below is successful and data is retrieved

URL: {baseUrl}/rest/services/{environment}/ContactView/FeatureServer/2/query?where=1%3D1&f=pjson&resultOffset=0&resultRecordCount=500&outFields=*&returnGeometry=false
Response: 
{
 "objectIdFieldName": "ObjectId",
 "globalIdFieldName": "",
 "fields": [
  {
   "name": "Name",
   "alias": "Name",
   "type": "esriFieldTypeString",
   "length": 255
  }
 ],
 "features": [ 
    // Data
 ],
 "exceededTransferLimit": true
}

However, the second request fails

URL: {baseUrl}/rest/services/{environment}/ContactView/FeatureServer/8/query?where=1%3D1&f=pjson&resultOffset=0&resultRecordCount=500&outFields=*&returnGeometry=false
Response:
{
 "error": {
  "code": 400,
  "message": "Unable to complete operation.",
  "details": [
   "Unable to perform query operation.",
   "Invalid query"
  ]
 }
}

The interesting thing from the second request is that if I remove the values from resultOffset and resultRecordCount, e.g.

resultOffset=&resultRecordCount=

The request is successful and comes back with data.

Not sure what would be the issue - hope someone can shed some light on this

UPDATE: a bit more information about this, the DB View in question doesn't seem to have any problem, if I query the View directly in the DB, the View will return data. It is only when I use resultOffset and resultRecordCount in my REST request that makes this fails.

Does anyone know how ArcGIS tranlsate the REST query into a SQL statement? Would it be something like the sql below?:

select * from MyView
ORDER BY (Select null)
OFFSET 0 ROWS FETCH NEXT 500 ROWS ONLY

If I run that sql above directly in the DB, I get data back from the View

Thanks in advance,

Luis

Re: "Unable to perform query operation.", "Invalid query"

LongDinh — Tue, 28 Sep 2021 04:30:52 GMT

Hi @LuisDelValle,

Have you tried assigning the outFields parameter to a known field or * in your GET request to see if the result changes?

Also try the setting true the returnCountOnly and returnIDsOnly parameters to see if the service is connecting correctly.

Is your second table (id:8) a spatial or non-spatial view?

Re: "Unable to perform query operation.", "Invalid query"

LuisDelValle — Tue, 28 Sep 2021 06:17:31 GMT

Hi @LongDinh ,

Thanks for your quick response. Both tables are non-spatial views.

I have tried assigning the outFields parameter but the result doesn't change. If I set returnCountOnly and returnIDsOnly the request is successful demonstrating that the service is connecting correctly.

It seems that only if I set resultOffset and resultRecordCount the request will fail.

Thinking that there might be something in the definition of the View? I can share that if needed

Re: "Unable to perform query operation.", "Invalid query"

LongDinh — Tue, 28 Sep 2021 22:50:18 GMT

Does resultOffset and resultRecordCount work with returnIDsOnly set to True?

Thinking that there might be something in the definition of the View? I can share that if needed

This will help. Are you you applying a definition query on top of the view before publishing or is this the actual definition in the Database? Are you creating the view from arcpy/ArcGIS Pro/ArcMap or through a DBMS management software?

I suspect it might be something to do with the indexing on the table. I found this BUG report which notes that setting the resultOffset and resultRecordCount with returnIDsOnly returns inconsistent results.

Re: "Unable to perform query operation.", "Invalid query"

LuisDelValle — Wed, 29 Sep 2021 00:18:56 GMT

Hi @LongDinh ,

The view is being created with ArcGIS Pro but as far as I know a definition query hasn't been applied on top of the view - I will check into that and share my findings and if anything changes after that.

Thanks a lot for sharing that article - including the exceededTransferLimit property in the query is something that is not being done right now in the backend component - I will also check with that and share my findings.

I'm including the definition of the view. It looks messy but it is basically a series of UNION ALL of five tables for something we call internally Features (I have changed the name of the tables to FeatureOne, FeatureTwo, etc) with joins to other two tables we call Organisation and OrganisationFeatureLayer (the last one an associative table)

Re: "Unable to perform query operation.", "Invalid query"

LongDinh — Wed, 29 Sep 2021 00:53:57 GMT

I was not expecting such a large definition 😅 If ArcGIS Pro has validated it, I have confidence it is an acceptable table for publishing.

The exceededTransferLimit property is a query result response property. You would generally perform the large query (e.g. where 1=1) and if exceededTransferLimit=True in your result json response, your application should then:

Get the total query record count using returnCountOnly, then
Paginate using the resultOffset and resultRecordCount parameters where resultOffset is your REST service's maxRecordCount property and the total query record count, respectively.

This is workflow is described in ArcGIS REST APIs - Services reference - Query (Feature Service/Layer)

Re: "Unable to perform query operation.", "Invalid query"

LuisDelValle — Wed, 06 Oct 2021 00:01:51 GMT

Hi @LongDinh ,

It took me a while to get back to this post but here I am. Problem solved.

The issue was when we were creating the view using ArcGIS Pro, there was something missing in that process that created and issue (my theory) with the definition of the REST API for that view, that ultimately created that problem when we were sending the request with resultOffset and resultRecordCount.

When we created the View with a script using arcpy, that issue was solved and sending a request with resultOffset and resultRecordCount is now working.

I'm marking one of your comments as the solution as you initially asked/suggested about how we were creating the view.

Thanks a lot mate

Re: "Unable to perform query operation.", "Invalid query"

LongDinh — Wed, 06 Oct 2021 00:04:07 GMT

Glad to hear you reached a solution!

Happy to help wherever I can 🙂