Recipes by Category

App Distribution (2) Bundle logic, interface and services for distribution. App Logic (37) The Apex programming language, workflow and formulas for logic. Collaboration (5) The Salesforce Chatter collaboration platform. Database (29) Data persistence, reporting and analytics. Integration (33) Web Service APIs and toolkits for integration. Security (9) Platform, application and data security. Tools (4) Force.com tooling User Interface (36) Visualforce MVC and metadata-drive user interfaces. Web Sites (12) Public web sites and apps with optional user registration and login.
Beta Feedback
Cookbook Home » Batchable Class Pattern to Reduce Development Effort for Reporting DML Exceptions

Batchable Class Pattern to Reduce Development Effort for Reporting DML Exceptions

Post by Thomas Snyder  (2012-01-23)

Status: Unverified
Level: intermediate

Problem

DML exceptions are commonly encountered when running batch jobs due to constant influx of additions and changes to triggers, workflows, and validation rules. These exceptions can cause your batch jobs to automatically abort. Also, it can be almost impossible in large runs to determine the root cause of these exceptions and identify the associated records.

Solution

Provide a simple means to store the DML exceptions and the associated records without the need to write a handler for each DML statement. Use a helper class (DMLResult) to gather the exceptions and records that caused the exceptions and finally create a report of the exceptions and send it via email.

  • Step 1: create a batchable class that is Database.Stateful.
  • Step 2: instantiate DMLResults class in start() Method.
  • Step 3: proxy all DML statements through DMLRresults class.
  • Step 4: invoke the batchOnFinish method in final() Method.
Here is an simple example to demonstrate the use of the DMLResults class.
global class batchRefresh implements Database.Batchable<Sobject>, Database.Stateful  { 
	global final string query; 
	global DMLResults unsuccessfuls; 

        global batchRefresh(String q){ 
            Query=q; 
            } 
        global Database.QueryLocator start(Database.BatchableContext BC){ 
			unsuccessfuls= new DMLResults(); 
			return Database.getQueryLocator(query); 
		} 
        global void execute(Database.BatchableContext BC, LIST<SObject> scope){ 
			unsuccessfuls.add(Database.update(scope,false),scope); 
        } 
        global void finish(Database.BatchableContext BC) { 
			unsuccessfuls.batchOnFinish(BC.getJobId()); 
        } 
} 

Executing the statement below will 'refresh' (fire triggers, workflow, and validation rules) all account records. If any DML exceptions are encountered they will be returns via email in a report showing the record ids and the exception.

Database.executeBatch('Select Id from Account',200);

Discussion

The DMLResults class is a generic collection for all DML Result types (Database.Saveresult, Database.Upsertresult, Database.Deleteresult, Database.Undeleteresult) and provides methods for concatenating the results including mixed Result types.

public class DmlResults { 
    
    public static integer resultLimit    = 1000; 
    public static boolean includeSuccess = false; 
    public LIST<DMLResults.Result> results {get; private set;} 
    { results = new LIST<DMLResults.Result>(); } 
    
    public LIST<string> messages = new LIST<string>();	//message(s) to be drop into the email.

	/**
	* constructor
	* @param lst a LIST of [Saveresult | Upsertresult |Deleteresult | Undeleteresult]
	* @param records the LIST of sobjects that were included in the dml statement.
	*/
    public DMLResults() {} 
    public DMLResults(LIST<object> lst) { this(lst,null);} 
    public DMLResults(LIST<object> lst,  LIST<sobject> records) { 
        integer cnt=0; 
        for (Object o : lst) { 
            try { 
                Result  r; 
                if (o instanceof Database.Saveresult )         r= new Result((Database.Saveresult) o, records[cnt]); 
                else if (o instanceof Database.Upsertresult )  r= new Result((Database.Upsertresult) o, records[cnt]); 
                else if (o instanceof Database.Deleteresult)   r= new Result((Database.Deleteresult) o, records[cnt]); 
                else if (o instanceof Database.Undeleteresult) r= new Result((Database.Undeleteresult) o, records[cnt]); 
                else 
                throw new InvalidResultException('Invalid DML Result.');
                if (includeSuccess || (!r.success) ) { 
                if (results.size()<resultLimit) this.add(r); 
                } 
            }catch(exception ex) { system.debug(ex); }     
            cnt++;     
        }     
    } 

       /**
       * add additional exceptions
       * @param lst a LIST of [Database.Saveresult | Database.Upsertresult | Database.Deleteresult | Database.Undeleteresult]
       * @param records the LIST of sobjects that were included in the dml statement.

       */
    public void add(LIST<object> lst) { add(new DMLResults(lst,null)); } 
    public void add(LIST<object> lst,  LIST<sobject> records) { 
        add(new DMLResults(lst,records)); 
    } 
    public void add(DMLResults.Result r) { 
        if (results.size()<resultLimit) 
            results.add(r); 
    }     
    public void add(DMLResults dmlr) { 
        if (results.size()<resultLimit) { 
        if (results.size()+dmlr.results.size()<resultLimit) 
            results.addAll(dmlr.results); 
        else { 
            for (Result r : dmlr.results) { 
            if (results.size()<resultLimit) 
                this.add(r); 
                else 
                break; 
            } 
        }
		messages.addAll(dmlr.messages);      
        }         
    } 
    public void add(LIST<DMLResults.Result> lst) { 
        if (results.size()<resultLimit) 
        results.addAll(lst); 
    } 
    


	public class Result {
		public sobject record 		{get;set;}
		public Id id 				{get;set;}
		//Database.Error[] errors = new Database.Error[]{};
		public string errors 		{get;set;}
		public boolean success 		{get;set;}
		public string statusCode	{get;set;}

		public Result(Database.Saveresult r) { this(r,null); }
		public Result(Database.Saveresult r, sobject sObj) {
	        if (r.getId()!=null) 
	            id=r.getId(); 
	        else if (sObj!=null && sObj.id!=null) 
	            id=sObj.Id; 
			errors=string.valueOf(r);
			statusCode = (!r.getErrors().isEmpty()) ? string.valueOf(r.getErrors()[0].getStatusCode()) : null;
			success=r.isSuccess();
			record=(sObj!=null) ? sObj : null;
		}
		public Result(database.Deleteresult r) { this(r,null); }
		public Result(database.Deleteresult r,sobject sObj) {
			if (r.getId()!=null) 
	            id=r.getId(); 
	        else if (sObj!=null && sObj.id!=null) 
	            id=sObj.Id; 
			errors=string.valueOf(r);
			statusCode = (!r.getErrors().isEmpty()) ? string.valueOf(r.getErrors()[0].getStatusCode()) : null;
			success=r.isSuccess();
			record=(sObj!=null) ? sObj : null;
		}
		public Result(database.Upsertresult r) { this(r,null); }
		public Result(database.Upsertresult r,sobject sObj) {
			if (r.getId()!=null) 
	            id=r.getId(); 
	        else if (sObj!=null && sObj.id!=null) 
	            id=sObj.Id; 
			errors=string.valueOf(r);
			statusCode = (!r.getErrors().isEmpty()) ? string.valueOf(r.getErrors()[0].getStatusCode()) : null;
			success=r.isSuccess();
			record=(sObj!=null) ? sObj : null;
		}
	    public Result(Database.Undeleteresult r) { this(r,null); } 
	    public Result(Database.Undeleteresult r,sobject sObj) { 
	        if (r.getId()!=null) 
	            id=r.getId(); 
	        else if (sObj!=null && sObj.id!=null) 
	            id=sObj.Id; 
	        errors=string.valueOf(r);
	        statusCode = (!r.getErrors().isEmpty()) ? string.valueOf(r.getErrors()[0].getStatusCode()) : null; 
	        success=r.isSuccess(); 
	        record=(sObj!=null) ? sObj : null; 
	    } 
    } 
    
    
    public string resultsToString() { 
	    string rtn; 
	    rtn='Total DML results: '+String.valueOf(results.size())+'\n'; 
	    if (results.size()>0) { 
	        for(DMLResults.Result r : results) { 
	            if(r.record!=null) 
	                rtn+='Record: '+String.valueOf(r.record)+'\n';     
	            rtn+='Error: '+String.valueOf(r.errors)+'\n\n'; 
	        } 
	    } 
	    return rtn;     
    } 


	public string resultsToHtml() { 
	    string rtn; 
	    rtn='Total DML results: '+String.valueOf(results.size())+'<br/>'; 
	    rtn+='<table border="1px"><tr style="background:gray;"><th>id</th><th>success</th><th>error(s)</th><th>record</th>'; 
	    for(DMLResults.Result r : results) { 
	        rtn+=String.format('{0}{1}{2}{3}', 
	        	new string[]{String.valueOf(r.id),String.valueOf(r.success),r.errors,String.valueOf(r.record)}); 
	    } 
	    rtn+=''; 
	    return rtn;     
	}     
    
public void batchOnFinish(Id jobId) { batchOnFinish(jobId,true,null); } 
	 public void batchOnFinish(Id jobId, boolean OnlyNotifyOnError) {  batchOnFinish(jobId,OnlyNotifyOnError,null); }
	 public void batchOnFinish(Id jobId, boolean OnlyNotifyOnError, string emailOverride) {
	    boolean html = true; 
	    
	    AsyncApexJob a = [  Select ApexClass.Name, Id, Status, NumberOfErrors, JobItemsProcessed, 
		    					TotalJobItems, CreatedBy.Email 
		            From AsyncApexJob where Id =:jobId 
		            ];
	    if (a.NumberOfErrors>0 || results.size()>0 || (!OnlyNotifyOnError)) { 
	    
	        // Send an email to the Apex job's submitter (or emailOverride) notifying of job completion. 
	        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); 
	        string email = (emailOverride!=null) ? emailOverride : a.CreatedBy.Email;
	        String[] toAddresses = new String[] { email }; 
	        mail.setToAddresses(toAddresses); 
	        mail.setSubject(a.ApexClass.Name+': ' + a.Status); 
	        
	        string s = '';
	        string crlf = (html) ? '<br/>' : '\n';
	        
	        
	        if (messages.size()>0) {
	        	s+=crlf+'Messages:'+crlf+crlf;
		        for (string msg : messages) {
		        	try {
		        		s+=msg.replace('\n',crlf)+crlf;
		        	}catch(exception ex){}
		        }
				 
	        }
	        
	        
	        s += crlf+crlf+'The batch Apex job processed '+a.TotalJobItems+' batches with '+a.NumberOfErrors+' failures.'+crlf+crlf; 
	        //show error table
	        if(html) { 
	            s+=resultsToHtml(); 
	            mail.setHtmlBody(s); 
	        } 
	        else { 
	            s+=resultsToString(); 
	            mail.setPlainTextBody(s); 
	        } 
	        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail }); 
	    } 
	} 
	
	public class InvalidResultException extends Exception {} 
	
	public static DmlResults dmlInsert(LIST<sObject> records) { return dmlInsert(records, false); }
	public static DmlResults dmlInsert(LIST<sObject> records,Boolean opt_allOrNone) {
		return new DmlResults(Database.insert(records, opt_allOrNone),records);
	}
	//one day I hope an upsert wont require a strongly typed object
	//public static DmlResults dmlUpsert(LIST<sObject> records, Schema.Sobjectfield External_ID_Field, Boolean opt_allOrNone) {
	//	return new DmlResults(Database.upsert(records, (Schema.Sobjectfield) External_ID_Field, opt_allOrNone),records);
	//}
	public static DmlResults dmlUpdate(LIST<sObject> records)  { return dmlUpdate(records, false); }
	public static DmlResults dmlUpdate(LIST<sObject> records,Boolean opt_allOrNone) {
		return new DmlResults(Database.update(records,opt_allOrNone),records);
	}
	public static DmlResults dmlDelete(LIST<sObject> records)  { return dmlDelete(records, false); }
	public static DmlResults dmlDelete(LIST<sObject>records,Boolean opt_allOrNone) {
		return new DmlResults(Database.delete(records,opt_allOrNone),records);
	}
	
	
}

The batch class must implement 'Database.Stateful'. Maintaining state is require to keep track of the all the exceptions for all executions. Keep in mind the size of this state will effect the heap size. To keep the memory footprint and email payload down the DMLResults are defaulted to a limit of 1000 results. This can be overwritten by use of the resultLimit property.

In your DML statement be sure to set the optional opt_allOrNone parameter to false so if a record fails, the remainder of the DML operation can still succeed. For example:

myResults.add(Database.upsert(myAccounts,false, Account.UniqueID__c),myAccounts); 

A more complex batch class would typically require you to encapsulate your logic in a fascade class that will return DMLResults in your method(s). For example:

      global void execute(Database.BatchableContext BC, LIST<SObject> scope){ 
	 myResults.add(MyFascade.doSomething(scope)); 
        } 
    global class MyFascade { 
        global DMLResult doSomething(LIST<sobject> sos) {
            DMLResult results = new DMLResult();
            
            //same as results.add(Database.upsert(sos,false),sos) 
            results.dmlUpdate(sos);
            ...
            results.add(MyClass1.doSomething());
            ...
            results.add(MyClass2.doSomething());
            ...
            return results;
        }
    }

Share

Recipe Activity - Please Log in to write a comment

Be the first to comment.

X

Vote to Verify a Recipe

Verifying a recipe is a way to give feedback to others and broaden your own understanding of the capabilities on Force.com. When you verify a recipe, please make sure the code runs, and the functionality solves the articulated problem as expected.

Please make sure:
  • All the necessary pieces are mentioned
  • You have tested the recipe in practice
  • Have sent any suggestions for improvements to the author

Please Log in to verify a recipe

You have voted to verify this recipe.