3

I want to fetch 19k records from my org using Tooling API in Java. I have used LIMIT and OFFSET values in SOQL query to maintain sequence, the problem I'm facing is OFFSET is not processing values more than 2000.

In first run LIMIT=2000 and OFFSET=0, and in the 2nd run it will be LIMIT=2000 and OFFSET=2001.

The error I got is:

java.lang.RuntimeException: Failed : HTTP error code : 400

  • From the error code in your question, it doesn't look like your error is related to the problem you describe. A 400 HTTP Error Code is a "Bad Request". You will likely need to debug this on your end, and edit your question to provide relevant information to the error code. – battery.cord Apr 10 '17 at 15:21
  • Already i have debugged and tested with multiple values of OFFSET. JAVA code is working till OFFSET=2000 after that it is throwing error. With POSTMAN Tool it is showing following error: [{"message":"Maximum SOQL offset allowed is 2000","errorCode":"NUMBER_OUTSIDE_VALID_RANGE"}] @battery.cord – Rajshree Jadhav Apr 10 '17 at 15:33
  • 3
    Thats a much more useful error, it shows that there isnt a syntax error causing your error to be rejected. It looks like you won't be able to pull all 19k rows via OFFSET, if you checked the documentation, the max value for OFFSET is 2k. Have you tried using other methods, such as queryMore? – battery.cord Apr 10 '17 at 15:43
  • 1
    @battery.cord Can you post the usage of queryMore as an answer. It is probably the best option for paging through multiple records from the Tooling API. – Daniel Ballinger Apr 10 '17 at 21:10
  • Thanks @battery.cord the queryMore method worked for me. – Rajshree Jadhav Apr 12 '17 at 11:35
3

While OFFSET is limited to a total of 2,000 records, query & queryMore can be configured to return 2,000 records each call.

To do this, you need to first set the batch size for your connection via:

connection.setQueryOptions(2000);

Then, you need to call query, and return the first 2,000 records. The return value of query is a QueryResult, which contains the results, as well as a QueryLocator. The QueryLocator can be provided to the queryMore call to essentially perform an identical function as OFFSET, but supports a much higher number of records.

QueryResult = connection.queryMore(QueryLocator someQueryLocator);

From here, you repeat calls to queryMore, using the QueryLocator returned in the QueryResult from each call. Once the value of QueryResult.done is equal to true, you've retrieved all of the results from Salesforce, and can continue doing whatever your java app was doing.


I've included the example code from the documentation below, which uses query, and queryMore to fetch a large number of records from the database.

public void queryRecords() {
   QueryResult qResult = null;
   try {
      String soqlQuery = "SELECT FirstName, LastName FROM Contact";
      qResult = connection.query(soqlQuery);
      boolean done = false;
      if (qResult.getSize() > 0) {
         System.out.println("Logged-in user can see a total of "
            + qResult.getSize() + " contact records.");
         while (!done) {
            SObject[] records = qResult.getRecords();
            for (int i = 0; i < records.length; ++i) {
               Contact con = (Contact) records[i];
               String fName = con.getFirstName();
               String lName = con.getLastName();
               if (fName == null) {
                  System.out.println("Contact " + (i + 1) + ": " + lName);
               } else {
                  System.out.println("Contact " + (i + 1) + ": " + fName
                        + " " + lName);
               }
            }
            if (qResult.isDone()) {
               done = true;
            } else {
               qResult = connection.queryMore(qResult.getQueryLocator());
            }
         }
      } else {
         System.out.println("No records found.");
      }
      System.out.println("\nQuery successfully executed.");
   } catch (ConnectionException ce) {
      ce.printStackTrace();
   }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.