Batchable Class Pattern to Reduce Development Effort for Reporting DML Exceptions

Source code:

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.

Here is an simple example to demonstrate the use of the DMLResults class. [code apex] global class batchRefresh implements Database.Batchable, 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 scope){ unsuccessfuls.add(Database.update(scope,false),scope); } global void finish(Database.BatchableContext BC) { unsuccessfuls.batchOnFinish(BC.getJobId()); } } [/code]

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.

[code apex] Database.executeBatch('Select Id from Account',200); [/code]

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.

[code apex] public class DmlResults { public static integer resultLimit = 1000; public static boolean includeSuccess = false; public LIST results {get; private set;} { results = new LIST(); } public LIST messages = new LIST(); //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 lst) { this(lst,null);} public DMLResults(LIST lst, LIST 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() lst) { add(new DMLResults(lst,null)); } public void add(LIST lst, LIST records) { add(new DMLResults(lst,records)); } public void add(DMLResults.Result r) { if (results.size() lst) { 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())+'
'; rtn+=''; for(DMLResults.Result r : results) { rtn+=String.format('', new string[]{String.valueOf(r.id),String.valueOf(r.success),r.errors,String.valueOf(r.record)}); } rtn+='
idsuccesserror(s)record
{0}{1}{2}{3}
'; 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) ? '
' : '\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 records) { return dmlInsert(records, false); } public static DmlResults dmlInsert(LIST 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 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 records) { return dmlUpdate(records, false); } public static DmlResults dmlUpdate(LIST records,Boolean opt_allOrNone) { return new DmlResults(Database.update(records,opt_allOrNone),records); } public static DmlResults dmlDelete(LIST records) { return dmlDelete(records, false); } public static DmlResults dmlDelete(LISTrecords,Boolean opt_allOrNone) { return new DmlResults(Database.delete(records,opt_allOrNone),records); } } [/code]

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:

[code apex] myResults.add(Database.upsert(myAccounts,false, Account.UniqueID__c),myAccounts); [/code]

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:

[code apex] global void execute(Database.BatchableContext BC, LIST scope){ myResults.add(MyFascade.doSomething(scope)); } [/code] [code apex] global class MyFascade { global DMLResult doSomething(LIST 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; } } [/code]