"INVALID_QUERY_LOCATOR" error when querying Salesforce through TIBCO Cloud Integration - Connect
Article ID: KB0072812
Updated On:
| Products | Versions |
|---|---|
| TIBCO Cloud Integration - Connect ( Scribe ) | - |
Description
Query locators are generated by the Salesforce system in certain cases and if they are used after they have expired or been released this may cause API integrations to throw errors.
While trying to execute requests of data towards Salesforce.com, with the TIBCO Scribe Online Connector For Salesforce Salesforce, the following errors are logged in the Agent log files:
--------
Message: Execute process failed:
Exception Type : Exception
Message : INVALID_QUERY_LOCATOR: invalid query locator
StackTrace : at Scribe.Adapter.Salesforce.Behavior.HandleException.CatchException(Exception ex, String query)
at Scribe.Adapter.Salesforce.Services.SalesforceService.RetrieveMultiple(String objectName, String query, String queryLocator, Boolean includeDeletedItems)
at Scribe.Adapter.Salesforce.DataAccess.SalesforceDataAccess.<RetrieveDataInternal>d__28.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Scribe.Process.ProcessStep4AddnewUserRelationshipstoGroups.Execute(Dictionary`2 settings, IEnumerable`1 dataEntityInput)
at Scribe.Core.Processor.ProcessSupport.ExecuteCallProcess(Guid callingProcessId, Guid processId, Dictionary`2 settings, IEnumerable`1 dataEntityInput)
--------
Overview of Query Locators
Query locators represent a server-side cursor for a query. Typically a query locator is returned when not all the records requested in a query fit into the returned data set. This way a queryMore() can be issued with the provided query locator to get additional rows. For example, consider a query API call where the batch size is set to 2,000 and the organization has 2000+ accounts. In this scenario, SELECT id FROM account will return 2,000 records and a query locator to retrieve the remaining records.
About the Query Locator Issue
Normally you see this error when one Salesforce user is using more than 10 query cursors globally (with or without Scribe). Salesforce limits each user to 10 query cursors. This happens mostly when a lot of your packages run at the same time (other tools that use the same Salesforce account are also taken into account) and, thus, try to open more than 10 query cursors. In this case please try re-scheduling your work for the same user account or use multiple users.
This issue can also happen when your query/sub-query takes a longer time as the query Locator value expires automatically after 15 minutes of inactivity. In your flow also, you have the main query and some subqueries using Fetch, which could also be a problem.
From Salesforce Documentation
"Each Salesforce user can have up to 10 query cursors open at a time. This is a hard-coded limit that cannot be increased. If 10 query locator cursors are opened when a client application attempts to open a new one, then the oldest cursor is released. If a call is attempted to an expired or released cursor, salesforce.com will return the INVALID_QUERY_LOCATOR error. To avoid this error, ensure that the client code is not holding open more than 10 query locator cursors. If multiple processes are running using the same salesforce.com user, make sure that their combined query locator cursor count remains at 10 or below."
While trying to execute requests of data towards Salesforce.com, with the TIBCO Scribe Online Connector For Salesforce Salesforce, the following errors are logged in the Agent log files:
--------
Message: Execute process failed:
Exception Type : Exception
Message : INVALID_QUERY_LOCATOR: invalid query locator
StackTrace : at Scribe.Adapter.Salesforce.Behavior.HandleException.CatchException(Exception ex, String query)
at Scribe.Adapter.Salesforce.Services.SalesforceService.RetrieveMultiple(String objectName, String query, String queryLocator, Boolean includeDeletedItems)
at Scribe.Adapter.Salesforce.DataAccess.SalesforceDataAccess.<RetrieveDataInternal>d__28.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Scribe.Process.ProcessStep4AddnewUserRelationshipstoGroups.Execute(Dictionary`2 settings, IEnumerable`1 dataEntityInput)
at Scribe.Core.Processor.ProcessSupport.ExecuteCallProcess(Guid callingProcessId, Guid processId, Dictionary`2 settings, IEnumerable`1 dataEntityInput)
--------
Overview of Query Locators
Query locators represent a server-side cursor for a query. Typically a query locator is returned when not all the records requested in a query fit into the returned data set. This way a queryMore() can be issued with the provided query locator to get additional rows. For example, consider a query API call where the batch size is set to 2,000 and the organization has 2000+ accounts. In this scenario, SELECT id FROM account will return 2,000 records and a query locator to retrieve the remaining records.
About the Query Locator Issue
Normally you see this error when one Salesforce user is using more than 10 query cursors globally (with or without Scribe). Salesforce limits each user to 10 query cursors. This happens mostly when a lot of your packages run at the same time (other tools that use the same Salesforce account are also taken into account) and, thus, try to open more than 10 query cursors. In this case please try re-scheduling your work for the same user account or use multiple users.
This issue can also happen when your query/sub-query takes a longer time as the query Locator value expires automatically after 15 minutes of inactivity. In your flow also, you have the main query and some subqueries using Fetch, which could also be a problem.
From Salesforce Documentation
"Each Salesforce user can have up to 10 query cursors open at a time. This is a hard-coded limit that cannot be increased. If 10 query locator cursors are opened when a client application attempts to open a new one, then the oldest cursor is released. If a call is attempted to an expired or released cursor, salesforce.com will return the INVALID_QUERY_LOCATOR error. To avoid this error, ensure that the client code is not holding open more than 10 query locator cursors. If multiple processes are running using the same salesforce.com user, make sure that their combined query locator cursor count remains at 10 or below."
Issue/Introduction
Query locators are generated by the Salesforce system in certain cases and if they are used after they have expired or been released this may cause API integrations to throw errors.
Resolution
We would recommend trying to reduce the Query Page Size set to 200 instead of 2000 within the Connector For Salesforce configuration window.
This setting reduces the number of records returned per page within a query and we have seen setting this value to a lower value can resolve the Query Locator error.
If this issue is not resolved by setting lower page size in the salesforce connection-level then would recommend reviewing the Salesforce source environment to see if the issue is related to the number of query cursors that are open at one time for the user you are using in the connection. Unfortunately, this error is being returned from the Salesforce API, so there is a limited configuration we can adjust on the Scribe side besides possibly removing the additional Fetch block or using native query instead of query block to see if that changes the behavior.
This setting reduces the number of records returned per page within a query and we have seen setting this value to a lower value can resolve the Query Locator error.
If this issue is not resolved by setting lower page size in the salesforce connection-level then would recommend reviewing the Salesforce source environment to see if the issue is related to the number of query cursors that are open at one time for the user you are using in the connection. Unfortunately, this error is being returned from the Salesforce API, so there is a limited configuration we can adjust on the Scribe side besides possibly removing the additional Fetch block or using native query instead of query block to see if that changes the behavior.
Additional Information
Feedback
Yes
No
Powered by
