Salesforce Batch Apex - Invalid Query Locator

Salesforce Batch Apex - Invalid Query Locator

I recently encountered an "Invalid Query Locator" error while developing a batch Apex class that took me too long to resolve. I was able to trial & error my way through it until I got the code to work but I found it very frustrating that I was unable to find a solution on another developer board or blog. So here's my attempt at helping others that may encounter this problem.

The background for this blog is simple. I needed to build a program that would run on an as-needed basis on the Salesforce platform. The program would query for Account records which would be flagged via Boolean field that was set to true when certain fields on related records were updated either systematically or manually. The number of records that would be processed could vary greatly from day-to-day or month-to-month but it was possible to process hundreds of thousands of records or even millions at any given point in time. For this reason I decided to build a schedulable Apex class that would kick off batchable Apex class(s) and allow me some flexibility on any governor limits.

My initial batchable class, which was kicked off from a scheduled apex job, is over-simplified but listed below:

/*
	Created by: Greg Hacic
	Last Update: 13 June 2010 by Greg Hacic
	Questions?: greg@interactiveties.com
*/
global class batch_field_aggregation implements Database.Batchable<SObject> {
	
	//the ’start’ method is called at the beginning of a batch Apex job
	//use this method to collect the records (of objects) to be passed to the ’execute’ method for processing
	global Database.QueryLocator start(Database.BatchableContext BC) {
		return Database.getQueryLocator([SELECT Custom_Field_1__c, Custom_Field_2__c, Id, (SELECT Custom_Field_1__c, Id, Custom_Field_Etc__c FROM R00N00000000zZOzZZZ) FROM Account WHERE Custom_Boolean__c = true]);
	}
	
	//the ’execute’ method is called after the ’start’ method has been invoked and passed a batch of records
	global void execute(Database.BatchableContext BC, List<SObject> scope) {
		for (Account a : (List<Account>)scope) { //for all accounts from our query
			//some initial processing...
			for (Custom_Object_One__c customObjectOne : a.R00N00000000zZOzZZZ) { //for all related Custom_Object_One__c records
				//other processing...
			}
			//Any additional account object processing...
		}
		//Any final processing...
	}
	
	//the ’finish’ method is called once all the batches are processed
	global void finish(Database.BatchableContext info) {
		//send Greg a message about the batch processing completion
		AsyncApexJob a = [SELECT CreatedBy.Email, CreatedBy.Name, CreatedDate, CompletedDate, Id, JobItemsProcessed, NumberOfErrors, Status, TotalJobItems FROM AsyncApexJob WHERE Id =: info.getJobId()]; //query the AsyncApexJob object to retrieve the current job's information. 
		Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); //instantiate a new message object
		String[] toAddresses = new String[] {'greg@interactiveties.com'}; //denote the email address for receiving the message
		mail.setToAddresses(toAddresses); //to
		mail.setSubject('Apex batch_field_aggregation:'+a.Status); //subject
		mail.setPlainTextBody('The batch Apex job created by '+a.CreatedBy.Name+' ('+a.CreatedBy.Email+') processed '+a.TotalJobItems+' batches with '+a.NumberOfErrors+' failures. The process began at '+a.CreatedDate+' and finished at '+a.CompletedDate+'.'); //body
		Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail }); //send
	}
}

This initial attempt at the processing seemed logical (at least to me) because I was simply going to grab some fields from the Account along with the related object records via relationship query. Then I would iterate over the Accounts and subsequently process my logic by looping through the related object records.

What I found was that when I processed a large number of records within the relationship logic (specifically, the "for (Custom_Object_One__c customObjectOne : a.R00N00000000zZOzZZZ) {" loop) I received an Invalid Query Locator error. Somehow, by looping through a large number of related records, the query locator (or cursor) allocated to the Account results was being dropped.

After a tremendous amount of wasted time and, admittedly, some misunderstanding of the Apex Developer's Guide, I was able to figure out how to resolve the Invalid Query Locator error. That updated but still over-simplified code is below:

/*
	Created by: Greg Hacic
	Last Update: 13 June 2010 by Greg Hacic
	Questions?: greg@interactiveties.com
*/
global class batch_field_aggregation implements Database.Batchable<SObject> {
	
	//the "start" method is called at the beginning of a batch Apex job
	//use this method to collect the records (of objects) to be passed to the "execute" method for processing
	global Database.QueryLocator start(Database.BatchableContext BC) {
		return Database.getQueryLocator([SELECT Custom_Field_1__c, Custom_Field_2__c, Id, (SELECT Custom_Field_1__c, Id, Custom_Field_Etc__c FROM R00N00000000zZOzZZZ) FROM Account WHERE Custom_Boolean__c = true]); //
	}
	
	//the "execute" method is called after the "start" method has been invoked and passed a batch of records
	global void execute(Database.BatchableContext BC, List<SObject> scope) {
		for (SObject s : scope) { //for all objects from our query
			Account a = (Account)s; //Grab the Account sObject
			//some initial processing...
			Custom_Object_One__c[] customObjectOne = a.getSObjects('R00N00000000zZOzZZZ'); //grab all the related Custom_Object_One__c records
			if (customObjectOne != null) { //if there are related Custom_Object_One__c records
				for (Custom_Object_One__c objectOneRec : customObjectOne) { //for all related Custom_Object_One__c records
					//other processing...
				}
			}
			//Any additional account object processing...
		}
	//Any final processing...
	}
	
	//the "finish" method is called once all the batches are processed
	global void finish(Database.BatchableContext info) {
		//send Greg Hacic a message about the batch processing completion
		AsyncApexJob a = [SELECT CreatedBy.Email, CreatedBy.Name, CreatedDate, CompletedDate, Id, JobItemsProcessed, NumberOfErrors, Status, TotalJobItems FROM AsyncApexJob WHERE Id =: info.getJobId()]; //query the AsyncApexJob object to retrieve the current job's information. 
		Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); //instantiate a new message object
		String[] toAddresses = new String[] {'greg@interactiveties.com'}; //denote the email address for receiving the message
		mail.setToAddresses(toAddresses); //to
		mail.setSubject('Apex batch_field_aggregation:'+a.Status); //subject
		mail.setPlainTextBody('The batch Apex job created by '+a.CreatedBy.Name+' ('+a.CreatedBy.Email+') processed '+a.TotalJobItems+' batches with '+a.NumberOfErrors+' failures. The process began at '+a.CreatedDate+' and finished at '+a.CompletedDate+'.'); //body
		Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail }); //send
	}
}

The major difference is the use of the "getSObjects" method. This essentially allows the code to process many related records gathered via the sub-query and without dropping the query locator (or cursor).

Anyway, I hope I saved you some time troubleshooting this error if you received it.