Write Data(Update)

Operation Name

Write Data(Update)

Function Overview

This operation is to update data using the SOAP API update() call.

You must specify each records ID to use update().
However, with this component you may use a field other than ID as the key. In this case query() will be used to lookup the IDs based on the key.
In addition, if no corresponding record is found, it is possible to automatically add it using processing which makes use of create().

For details, please refer to Writing Method and Specifying a Key for Updating.

With regarding to the API specification utilized by this operation, please refer to the version of the API document, selected for the Global Resource, in the below-mentioned link.

"Salesforce Developer Documentation"-(https://developer.salesforce.com/docs)
- SOAP API Developer's Guide

Data Model

The data model of this component is the Table Model type.

Properties

For information about using variables, refer to "variables".

Basic Settings

Item name	Required/Optional	Use of Variables	Description	Supplement
Name	Required	Not Available	Enter the name that appears on the script canvas.
Input Data	Required	Not Available	Select the component on the script canvas.

Required Settings

Item name	Required/Optional	Use of Variables	Description	Supplement
Destination	Required	Not Available	Select Global Resources. [Add...]: Add new global resource. [Edit...]: Global resource settings can be edited by [Edit Resource list].
Table Name	Required	Not Available	Select the name of a table in Salesforce.
Schema definition	Required	-	Set items subject to writing to Salesforce.	Limiting the settings to only items needed to be written to Salesforce (deleting unnecessary items from the Schema Definition) helps improve processing performance.
Schema definition/Key	Required	Not Available	Select whether the target column of the table specified in [Table Name] is a key item for updating. [true]:Specify as a update key item [false]:Do not specify as a update key item	For details, please refer to Writing Method and Specifying a Key for Updating.
Schema definition/Label	Required	Not Available	Display the lable name of the column of the table specified in [Table Name].
Schema definition/API	Required	Not Available	Display the API name of the column of the table specified in [Table Name].
Schema definition/Type	Required	Not Available	Display the data type of the row of the table specified in [Table Name].
Relationship definition	Optional	-	In the case that relationship items exist in the Schema Definition, set items subject to updating by external key. By selecting the external key item of the relation counterpart object, it becomes possible to pass data with an established relationship.	Available on API 14.0 or later versions, and cannot be used on API 7.0. For details, please refer to Relationship Definition.
Relationship definition/Base field	Required	Not Available	Display the API Name of the row of the relation item of the table specified in [Table Name].
Relationship definition/Relationship name	Required	Not Available	Display the relationship name of the row of the relation item of the table specified in [Table Name].
Relationship definition/Related Object	Optional	Not Available	Select the API Name of the relation counterpart object in the relation item column of the table specified in [Table Name].
Relationship definition/Foreign key field	Optional	Not Available	Select the external key item of the relation counterpart object in the relation item column of the table specified in [Table Name].	If omitted, the ID of the relevant record of the relation counterpart object will be passed.

Property Action

Item name	Description	Supplement
Create schema...	The items to be read from Salesforce can be added/deleted. For setting methods, please refer to "Create Schema."

Option Settings

Item name	Required/Optional	Use of Variables	Description	Supplement
Column name type	Required	Not Available	Choose the display type of the column name, for displaying the schema in mapping. [Label]: The label name (item name) defined in Salesforce is displayed. [API] :(default) The API reference name defined in Salesforce is displayed.
Batch size	Required	Available	Specify a batch size for performing the write data operation on Salesforce.	The default value is "200". A number of 1 or greater, up to 200, can be set. An error occurs if any number outside of this range is set. Depending on the operating environment, increasing the batch size generally helps improve the performance of the write data operation. According to the Salesforce adapter specifications, if you set a batch size to 200 when writing 10,000 records, the write operation is performed in 50 installments.
Writing Method	Required	Not Available	Select either to add/update or only to update data to Salesforce. [Update and Insert]: The update process is executed if the input data exist in Salesforce, and the insert process is executed if not. [Update only]:(default) The update process is executed if the input data exist in Salesforce, and no process is executed if not.	For details, please refer to Writing Method and Specifying a Key for Updating.
Handling of null values	Required	Not Available	Select, in the case that the input data is null for the update target field, either to overwrite the field by null or to skip it. [Field value to null]:(default) If the input data is null, the update target field is overwritten by null. [Skip null values]: If the input data is null, the update of the target field is skipped and it remains the same. If the input data is not null, the update target field is overwritten by the input data.
Whether or not to get result per rows	Required	Not Available	Specify whether or not to obtain results, after writing to Salesforce. [Get]:(default) Obtain the results of the write data operation. [Do Not Get]: Do not obtain the results of the write data operation	If [Get] is selected, items specified in the Schema and items showing results can be passed to the subsequent processing as data in Table Model type. Please refer to Schema about the obtained information. Please refer to Usage Examples of Get Results for usage examples.
Base64 field input form	Required	Not Available	Specify the input data type of items in Base 64 data type. [Base64 data]:(default) Base64 Data is inserted. [File path]: File path is inserted.	If you select [file path], please specify absolute path on the DataSpider File System. If you select [file path], the prohibited characters on the DataSpider File System except for the path delimiter "/" cannot be used.

Property Action

Item name	Description	Supplement
Display Table Information...	You can check the table structure of the object you are operating. For more information on how to view the table structure, please refer to Display Table Information	Click [Load All Table Information....] if you would like to check the table structure of other objects.
Load All Table Information...	Retrieve all possible table information After running, you can verify the retrieved information from [Table Information].
Read schema definition from file...	Select a file from the file chooser and read the name of field API on the first line of the file specified in the comma-separated values. Set this as the schema definition.	Please specify "UTF-8" encoding for the selected file.

Large Data Processing Settings

Item name	Required/Optional	Use of Variables	Description	Supplement
Large Data Processing	Required	Not Available	Select a Large Data Processing Settings. [Use the setting in the script]:(default) Apply Large Data Processing Settings settings of script property to adapter. [Disable Large Data Processing]: Large Data Processing is not performed. [Enable Large Data Processing]: Large Data Processing is performed.

Comment

Item name	Required/Optional	Use of Variables	Description	Supplement
Comment	Optional	Not Available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input Schema

The number of columns varies depending on the [Schema definition] settings.

Output Schema

Information regarding the results of updating will appear in the column, set in the [Schema definition].
(Hereafter shown as "Lable/API")

<?xml version="1.0" encoding="UTF-8" ?>
<table>
  <row>
    <column>Label/API set in the [Schema definition]</column>
       :
    <column>update_result_id</column>
    <column>update_result_is_success</column>
    <column>update_result_is_created</column>
    <column>update_result_error_status_code</column>
    <column>update_result_error_message</column>
    <column>update_result_row_number</column>
  </row>
  <row>
    :
  </row>
</table>

Element Name	Column Name(Label/API)	Description	Supplement
row	-	Repeats as many times as the number of processings performed based on the data given to the input schema. For example, if two data records are updated by one input data record, this element would be repeated two times.	If [Do Not Get]is selected in [Whether or not to get result per rows], the repetition will be 0 times. If mismatch data exist, such as that data types do not match, the order of input data and output data will not necessarily match. By specifying [Update_Result_Row_Number/update_result_row_number] as the order condition, the data can be sorted in accordance to the input data in the subsequent Mapper.
column	update_result_id	Salesforce ID is output.
	update_result_is_success	Success Flag is output. [true]:Successfully processed [false]:Process failed. Please check the [update_result_error_status_code] as well as [update_result_error_message].
	update_result_is_created	Create Flag is output. [true]:A record is created. [false]:No record is created.
	update_result_error_status_code	The status code, returned by the Salesforce API, is output.	In the below-mentioned case, the Salesforce adapter will output the code and message. SALESFORCE_ADAPTER_ILLEGAL_DATA_TYPE When the type of global resources is API 23.0 or earlier, this is set when the defined item type and input data type do not match. For example, if the item is defined as "double" but a character string is input (not a number). SALESFORCE_ADAPTER_DATA_NOT_FOUND This is set when the target data is searched in Saleforce but cannot be found, in the case that key in Schema Definition contain items other than ID and also "Update" is selected in [Writing Mode].
	update_result_error_message	The message returned by the Salesforce API is output.
	update_result_row_number	Number is output based on the placement of the node within the data given to the input schema.

Reading Schema by Mapper

The schema will be read automatically.

Large Data Processing

Large Data Processing is supported.

Transaction

Transaction is not supported.

Usage on PSP Script

Cannot be used on PSP Script.

Available Component variables

Component Variable Name	Description	Supplement
insert_count	The number of data that was successful in the addition processing is stored.	The default value is null.
update_count	The number of data that was successful in the updating processing is stored.	The default value is null.
error_count	The number of data that was unsuccessful in the addition or updating processing is stored.	The default value is null. In the case that all the conditions below are satisfied, input data whose update targets do not exist are not included in the count. The number of input data whose update targets do not exist is stored in [not_exist_count]. [Update only] is selected in [Writing method] [Get] is selected in [Whether or not to get result per rows] [Key] contains items other than ID in [Schema definition]
not_exist_count	The number of input data whose updating target data do not exist is stored.	The default value is null. The value is not stored if any of the conditions below is not satisfied. [Update only] is selected in [Writing method] [Get] is selected in [Whether or not to get result per rows] [Key] contains items other than ID in [Schema definition]
read_count	The number of input data is stored.	The default value is null.
server_url	The end point URL after Login is stored.	The default value is null.
session_id	The session Id is stored.	The default value is null.
message_category	In the case that an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	In the case that an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	In the case that an error occurs, the importance of the message code corresponding to the error is stored.	The default value is null.
operation_api_exception_code	The ExceptionCode of the occured error, in a case of API error, is stored.	The default value is null. For any error other than an API Error, the value is not stored. The content to be stored may change according to the version of DataSpider Servista.
operation_error_message	If an error occurs, the error message of the occured error is stored.	The default value is null. The content to be stored may change according to the version of DataSpider Servista.
operation_error_trace	When an error occurs, the trace information of the occured error is stored.	The default value is null. The content to be stored may change according to the version of DataSpider Servista.

Writing Method and Specifying a Key for Updating

A updatig mode can be selected in [Writing method], and a key item that is used as a condition for updating can be specified in [Schema definition/Key]

In this operation, the process to be executed varies depending on the settings in [Writing method] and [Schema definition/Key].

In the case that [Update only] is selected in [Writing method], and "only ID" is true in [Schema definition/Key]
Update will be executed using the ID of input data. The ID can be obtained by inquiring the target object.

In the case that [Update only] is selected in [Writing method], and there is any item whose "other than ID" is true in [Schema definition/Key]
A query search will take place in Salesforce to obtain the ID from the row that matches the input data value.
Update will be executed using the ID obtained by the query search.
If the ID does not exist, Update will not be executed.

In the case that [Update and Insert] is selected in [Writing method]
A query search will take place in Salesforce to ask if the row that matches the input data value already exists.
If the row exists, Update will be executed using the ID obtained by the query search.
If the ID does not exist, Insert will be executed.
In this process, since the data existence judges are performed at the client side, the data processing may be less efficient compared to Upsert which performs the data existence judges at the server side.
If update and insert processing are intended, the use of [Write Data(Upsert)] is recommended.

Usage Examples of Get Results

If [Get] was selected in [Whether or not to get result per rows], the writing results can be passed onto the subsequent processing as Table Model type data.

Example: The updating results after data writing (Upsert) to the "Account" is output to a CSV file.

Number in the Image	Name	Description
(1)	Input Data Image	This is a sample image of data input, for updating to the "Account". Here, the [External Key]is designated as "AccountCode__c".
(2)	Settings for updating to the "Account"	Here, the setting is done using the Data Write (Upsert) icon.
(3)	Setting to obtain or not obtain the data processing results	Here, "Get" is chosen in the [Whether or not to get result per rows] in the [Option Settings] tab.
(4)	Script Image	This is the script implementation image of this description.
(5)	Resulting Data Image	This is the resulting data image obtained output as CSV file when updated to the "Account".

Relationship definition

Regarding to the updating process of the Reference field, it needed to pass the ID of the referred object and update by Mapper in the previous versions.
By taking in this setting, the automatical obtainment and updating of the ID of the record which responds to the external key, becomes possible by passing the value of the external key item of the reference object.

Example: Updating (Insert, Update, Upsert) the "Contact"

Details of the example

Category	Description	Supplement
Specification Requirements	Regarding to the update processing of "Contact", it needs to set the referring object ID in the the "Account" ID item (Reference Item). As for the relationship to the referring "Account", it needs to tie to the record according to the AccountCode of the input data.
Settings	Upon updating to "Contact", by passing the value of the Account Code (External key item created before hand: AccountCode__c) of the "Account", which is the referred object, to Mapper, the ID of the corresponding referred object is automatically obtained and set the relationship to the "Account" ID Item (Reference Item).	The items and some of the settings used in this description are usage examples. This is something that is not always in the Salesforce organization that you are using. Please use this function after adjusting and verifying your settings.

Coordination Image

Number in the Image	Name	Description	Supplement
(1)	The Data Image of the "Account"	This is the data image of the object which is refered by the "Contact".
(2)	The External Key of the "Account"	Pre set "AccountCode__c" as external key.
(3)	Data Input Image	This is the data input image, for updating to the "Contact".	This is the data which has the "Account Code".
(4)	Settings for the updating (Insert, Update, Upsert) to the "Contact".	Here, the "Account ID", "Last Name", "First Name", "E-mail" are the four considered items for updating.
(5)	Relationship Definition Setup	The external key of the referred object in "Foreign key field" should be selected. Here, the external key "Account.AccountCode__c" is selected as the key item for setting the relationship.	If no value is selected, the ID of the corresponding record of the referred object will be passed to Mapper.
(6)	The Updating Results Data Image of the "Contact"	This is the updating results data image of the "Contact".	The relationship is correctly setup to accomodate the record that responded to the "Account Code".

Create Schema

The data items to be read from and/or to be written into Salesforce can be specified in the creation of a schema.
By specifying only the items indispensable for processing, the data traffic to Salesforce can be reduced, generally improving the processing performance.
When writing into Salesforce (adding or updating), it is necessary to make sure that the items "Not Creatable" and "Not Updatable" are not be chosen.

Number in the Image	Name	Description	Supplement
(1)	Selected Fields	Select items for reading from and/or writing to Salesforce.
(2)	UnSelected Fields	Select items not for reading from and/or writing to Salesforce.	You can filter fields by inputting or selecting values from the input above the column name. Text fields will be filtered as Starting With the input value.

Display Table Information

This display shows the available operations on Salesforce tables as well as field properties of the tables.
Also, the field information can be printed out.

Number in the Image	Name	Description
(1)	Table Name	Select the table whose structure to be shown.
(2)	Table Information	Display the available operations on the selected table.
(3)	Length	Display the Number of Digit of item
(4)	External ID	Display whether or not the object item is set as an external ID.
(5)	Createble	Display whether or not it can be set a value when adding data.
(6)	Updatable	Display whether or not it can be set a value when updating data.
(7)	Nullable	Display whether or not it can be set NULL when adding or updating data.
(8)	Default value	Display whether or not Salesforce automatically set a default value when adding data.
(9)	Reference To	Display the referring object name if the item is in reference relationship or master-servant relationship.

Specification Limits

In order to perform data reading and writing with this adapter, API's of Salesforce organization must be published.

All permissions for reading/writing data and other operations, as well as data access control, depend upon the profile, shared rule setting and API specifications in Salesforce.

The objects and fields that can be used may differ by API version. For details, please refer to the Salesforce documentation.

Main exceptions

Exception Name	Reason	Resolution
ResourceNotFoundException Resource Definition is Not Found. Name:[]	[Destination] is not specified.	Specify [Destination].
ResourceNotFoundException Resource Definition is Not Found. Name:[<Global Resource Name>]	The resource definition selected in [Destination] cannot be found.	Verify the global resource specified in [Destination]
java.net.UnknownHostException	This exception occurs when the PROXY server specified in the global resource cannot be found.	Verify the condition of the PROXY server. Or verify [Proxy host] of the global resource specified in the [Destination].
API 23.0 or earlier org.apache.commons.httpclient.HttpConnection$ConnectionTimeoutException API 26.0 or later java.net.SocketTimeoutException connect timed out	A time-out has occurred while connecting to Salesforce.	Verify the network condition and Salesforce server condition. Or check [Connection timeout(sec)] of the global resource specified in the [Destination].
API 23.0 or earlier org.apache.commons.httpclient.HttpRecoverableException java.net.SocketTimeoutException: Read timed out API 26.0 or later java.net.SocketTimeoutException Read timed out	A time-out has occurred while waiting for a responce from the server after connecting to Salesforce.	Verify the network condition and Salesforce server condition. Or check [Timeout(sec)] of the global resource specified in the [Destination].
jp.co.headsol.salesforce.adapter.exception.SalesforceAdapterIllegalArgumentException	Invalid value is set for the property of Salesforce adapter.	Check the error message, and verify the settings.
com.sforce.soap.partner.fault.LoginFault	Login to Salesforce has failed.	Check the ExceptionCode or error message, and refer to the information about this type of error in Salesforce-related documents etc.
com.sforce.soap.partner.fault.UnexpectedErrorFault	An unexpected error has occured while processing to Salesforce.	Check the ExceptionCode or error message, and refer to the information about this type of error in Salesforce-related documents etc.

Notes

This operation cannot perform a rollback when an error occurs, unlike the database adapters. Succeeded data are updated while error data are left not to be updated and the processing log in WARN level is output.
Therefore, if errors exist in units of data (constraint violation and invalid value, etc.), those errors cannot be detected by monitoring exceptions in a script.
If you want to use the data-level error information as data, you need to select [Get] in [Whether or not to get result per rows] and then use the results data for handling error detection etc. in the subsequent processing.

If the Foreign key field in the relationship definition is specified as numeric type, and a value is written beyond the precision of the Java Double data type, the result value will be rounded.
Specify the field as text type to avoid the value from rounding off.

As of the next API version, If a value is written beyond the precision of the Java Double data type, the value will be rounded.
For example, if the value (1,234,567,890,123,456.78) is written to a numerical (16,2) field, it will be saved as (1,234,567,890,123,456.80).
- API 7.0
- API 14.0
- API 18.0
- API 23.0
To write the value without rounding, use an API version newer than the above.

As of the next API version, when [Writing method] is set to [Update only] and [schema definition/key] is true for a non-ID field, or when [Writing method] is set to [Update and Insert], a [MALFORMED_QUERY] error will be thrown when a value is input the field specified to be the key beyond the precision of a Java Integer.
- API 7.0
- API 14.0
- API 18.0
- API 23.0
Choose a field that differs from the key or use an API version newer than the above.

From the next API version, if [Writing method] is set to [Update and Insert] and the relationship field definition is used for the Reference Type, an [INVALID_FIELD] error will be returned when the record's reference type value is set to null or an empty string.
- API 7.0
- API 14.0
- API 18.0
- API 23.0
To write the value as null or an empty string, use an API version newer than the above.