November 2, 2009

Using Saleforce DML statements or DML database methods?

Salesforce allows you to perform database transactions (insert, update, delete, etc.) using either DML statments or DML database methods. While they perfrom roughly the same functionality (with a few exceptions), DML database methods provide a little more granular control when bulk processing exceptions occur.

The Salesforce documentation is good and extremely detailed but these two approaches overlap in functinality and are sometimes confusing. I've tried to boil the docs down and point out where DML statements and DML database methods are different and yet the same. The choices you make for your code is mostly based upon personal preference but may be dictated depending on the transactions you are performing.

The main difference is how bulk exceptions are processed:

  • DML statements - when an exception is thrown during bulk DML processing, processing stops immediately and jumps to your catch block.
  • DML database methods - allows partial success of bulk DML operations. Records that fail processing can be inspected and possibly resubmitted if necessary.
Here is an Apex example of inserting records using DML database methods. Notice the SaveResult object returned from the insert function and how it is processed outside of the catch block.
List<Contact> contacts = new List<Contact>();

for (Integer i = 0; i < 5; i++)
 contacts.add(new Contact(FirstName='First '+i,LastName='Last '+i,Email='my@email.com'));

try {

	Database.SaveResult[] results = Database.insert(contacts,false);
	if (results != null){
		for (Database.SaveResult result : results) {
			if (!result.isSuccess()) {
				Database.Error[] errs = result.getErrors();
				for(Database.Error err : errs)
					System.debug(err.getStatusCode() + ' - ' + err.getMessage());

			}
		}
	}

} catch (Exception e) {
	System.debug(e.getTypeName() + ' - ' + e.getCause() + ': ' + e.getMessage());
}

DML database methods may also contain two optional parameters:

  1. opt_allOrNone - a Boolean that indicates if the operation should allow partial successes. If false, failing records will not cause subsequent records to fail as well.
  2. DMLOptions - an object that provides extra information that is used during the transaction such as string truncations behavior, assignment rules, email processing and locale information.
A couple of methods only support one approach or the other:
  • convertLead - DML database method only
  • merge - DML statement only

Insert Method

DML Statement Syntax
insert sObject insert sObject[]
DML Database Method Syntax
SaveResult Database.insert(sObject recordToInsert, Boolean opt_allOrNone | database.DMLOptions opt_DMLOptions) SaveResult[] Database.insert(sObject[] recordsToInsert, Boolean opt_allOrNone | database.DMLOptions opt_DMLOptions)

Update Method

DML Statement Syntax
update sObject update sObject[]
DML Database Method Syntax
UpdateResult Update(sObject recordToUpdate, Boolean opt_allOrNone | database.DMLOptions opt_DMLOptions) UpdateResult[] Update(sObject[] recordsToUpdate[], Boolean opt_allOrNone | database.DMLOptions opt_DMLOptions)

Upsert Method

Upsert either creates new sObject records or updates existing sObject records within a single statement, and can also utilize a custom field to determine the presence of existing objects. The custom field must be created with the "External Id" attribute. If the opt_external_id is not specified, then the record id of the sObject is used by default.

DML Statement Syntax

upsert sObject opt_external_id upsert sObject[] opt_external_id
DML Database Method Syntax
UpsertResult Database.Upsert(sObject recordToUpsert, Schema.SObjectField External_ID_Field, Boolean opt_allOrNone) UpsertResult[] Database.Upsert(sObject[] recordsToUpsert, Schema.SObjectField External_ID_Field, Boolean opt_allOrNone)

Delete Method

DML Statement Syntax
delete sObject delete id
DML Database Method Syntax
DeleteResult Database.Delete((sObject recordToDelete | RecordID ID), Boolean opt_allOrNone) DeleteResult[] Database. Delete((sObject[] recordsToDelete | RecordIDs LIST:IDs{}), Boolean opt_allOrNone)

Undelete Method

Restores one or more existing sObject records from the Recycle Bin.

DML Statement Syntax

undelete sObject undelete id undelete sObject[] undelete LIST:id[]
DML Database Method Syntax
UndeleteResult Database.Undelete((sObject recordToUndelete | RecordID ID), Boolean opt_allOrNone) UndeleteResult[] Database.Undelete((sObject[] recordsToUndelete | RecordIDs LIST:IDs{}), Boolean opt_allOrNone)

Merge Method (DML statement only)

Merges up to three records of the same sObject type into one of the records, deleting the others, and re-parenting any related records.
merge sObject sObject merge sObject sObject[] merge sObject ID merge sObject ID[]

ConvertLead Method (DML database method only)

Converts a lead into an account and contact, as well as an opportunity (optional).
LeadConvertResult Database.convertLead(LeadConvert leadToConvert, Boolean opt_allOrNone) LeadConvertResult[] Database.convertLead(LeadConvert[] leadsToConvert, Boolean opt_allOrNone)

With or Without Sharing

Most DML operations run in system context. This ignores the running user's profile permissions, sharing rules, field level security, org-wide defaults and their position in the role hierarchy. If you want to enforce sharing rules for the running user, use the "with sharing" keywords with the class definition.

Setup Object

Some sObjects (called setup objects) will not let you perform DML operations with other sObjects in the same transaction. For example, if you try to insert a new user and a new contact in the same transaction, you'll receive the following exception: "MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object". There is a potential workaround posted here but you can also try to use a @future method to perform the processing of the second sObject.
  • Group3
  • GroupMember
  • QueueSObject
  • User4
  • UserRole
  • UserTerritory
  • Territory

Not DML Supported Objects

This catches people once in awhile, but the following sObjects do not support any type of DML operations:
  • AccountTerritoryAssignmentRule
  • AccountTerritoryAssignmentRuleItem
  • ApexComponent
  • ApexPage
  • BusinessHours
  • BusinessProcess
  • CategoryNode
  • CurrencyType
  • DatedConversionRate
  • ProcessInstance
  • Profile
  • RecordType
  • SelfServiceUser
  • StaticResource
  • UserAccountTeamMember
  • UserTerritory
  • WebLink