Read Data (execute SOQL)

Operation Name

Read Data (execute SOQL)

Function Overview

This operation is to read data, using the query() or queryAll() call of the SOAP API.

You an directly input SOQL(Sforce Object Query Language).
Therefore you must understand SOQL syntax.

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.

Data Model

The data model of this component is the Table Model type or XML 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.  
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].
  
SOQL Required Available Enter SOQL.
    For syntax details, please refer to the Salesforce API documents.
Schema definition Required - Sets the schema from the results of the SOQL.
Rows of each combination can be manipulated with the below mentioned buttons.
  • [Add]:
    Add a row.
  • [Move Up]:
    Move the selected row upward by one row.
  • [Move Down]:
    Move the selected row downward by one row
  • [Remove]:
    Delete the row.
  • The order of the SOQL does not need to match that of the Schema definition.
  • You can automatically reflect the results from response of executed SOQL. For more details refer to [Execute SOQL to set schema]
Schema definition/Schema name Required Available Input the schema name that is to be obtained from the SOQL response.
Usually this will be a field name specified in the SELECT clause.
  • An empty result will be included if a schema name is inputted that is not included in the response.
  • Differentiates between uppercase and lowercase.
  • When a parent-child relationship is included, the data model becomes XML. In this case you cannot use variables. In addition you cannot use the below characters.
  • If no alias is set for the aggregate function schema, the result will automatically be called "expr0". The ending character will start with 0 and increase by one for each aggregate function used.
  • When a schema that includes a parent-child relationship is specified the [Relationship Name.Schema Name].
  • When a schema that includes a parent-child relationship is used, the relationship name is specified by [Schema definition/child relationship name].
Schema definition/Child relationship name Optional Available When the schema includes a parent-child relationship, input the relationship name.
  • Omitted except when the schema uses a parent-child relationship.
Schema definition/Type Required Not Available Select a data type.
  • An exception will be thrown if the conversion to the selected data type fails.
  • For data type details, please refer to the Salesforce API documents.
Property Action
Item name Description Supplement
Extraction test A query is performed, based on the specified schema and search conditions. The number of results obtained will be displayed.
  • Cannot be performed in the following instances:
    • If the SOQL has not been specified.
    • If a variable has been used for the SOQL.
Execute SOQL to set schema Automatically reflect the results from response of executed SOQL.
  • Cannot be performed in the following instances:
    • If the SOQL has not been specified.
    • If a variable has been used for the SOQL.
  • When the extracted results include an ID, the ID will be set to the beginning of the schema.
  • The schema is not set correctly if not even a single record is returned. When using a relation query, the relationship record must also be included in the filter results. If no record exists, please set the schema manually.
  • In the following case the type is not set. Please manually set the type:
    • Includes GROUP BY clauses and functions.
    • Fields with more than one reference.
  • Response may be delayed by a filter that returns a very large set of results.
    We recommend you to temporarily modify the SOQL filter before executing.
Option Settings
Item name Required/Optional Use of Variables Description Supplement
Read records that have been deleted(queryAll) Required - Specify whether or not to read the data that has been deleted from Salesforce.
(If it is to be read, a reading, using the queryAll service, will take place.)
  • [Unread]:(default)
    Does not read the data deleted in Salesforce.
  • [Read]:
    Reads the data deleted in Salesforce.
  • Valid only when selecting API 14.0 or later. Invalid when API 7.0, and a reading will not take place.
  • Please be aware that, in the deleted record reading process by the data reading (query), data which has been deleted from the recycle bin can still be read for about 24 hours after deletion.
Batch size Required Available Enter the batch size for obtaining the data reading results from Salesforce.
  • The default value is "2000."
  • API specification will not allow value less than 200. If the value is set, the behavior will depend on the specification of the API.
  • A number of 200 or greater, up to 2000, 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 read data operation.
  • According to the Salesforce adapter specifications, if you set a batch size to 2000 when reading 10,000 records, the result data set will be obtained in 5 parts.
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].		  
File Output Settings
Item name Required/Optional Use of Variables Description Supplement
Base64 field output form Required - Specify the data output format of fields with data type Base64.
  • [Base64 data]:(default)
    Pass the Base64 data read from the output schema as is.
  • [File output]:
    Output as a file.
    Pass the file path output in the output schema.
  • When the read schema does not contain Base64 data type fields, please select [Base64 Data].
Directory Optional Available Input the directory to which to output.
Click the [browse] button to open a directory selector to choose a directory.
Data Field Optional - Enter the schema name of data type Base64 from the fields contained in the [schema definition].
  • Will be enabled when [File Output] is selected from [Base64 field output form].
  • When the [schema definition] does not contain Base64 data type fields, please select [Base64 Data]
How to set the File Name Optional - Select how to input the output file name.
  • [Select a field]:(default)
    Select the field to be used as the file name from the fields contained in the [schema definition].
  • [Enter String]:
    Input the string to be used as the file name.
  • Will be enabled when [File Output] is selected from [Base64 field output form].
File Name Optional Available Specify the output file name.
Enter the schema name when [How to set File Name] is set to [Select a field].
  • Will be enabled when [File Output] is selected from [Base64 field output form].
  • If characters are included that are incompatible with the DataSpider file system, they will be converted to underscores (_) at output time.
How to set an Additional String Optional - Select the method to append characters to the output file name.
  • [Do not add]:(default)
    Do not append characters to the output file name.
  • [Select a field]:
    Select the field to be used as appended characters from the fields contained in the [schema definition].
  • [Enter String]:
    Input the string to be used as the appended characters.
  • Will be enabled when [File Output] is selected from [Base64 field output form].
Additional String Optional Available Specify the appended string to be added to the output file name.
Enter the schema name when [How to set an Additional String] is set to [Select a field].
  • Will be enabled when [File Output] is selected from [Base64 field output form] and [Select a field] or [Enter String] is selected from [How to set Additional String].
  • If characters are included that are incompatible with the DataSpider file system, they will be converted to underscores (_) at output time.
Additional Position Optional - Select the position of the string to be added to the output file name.
  • [Prefix]:(default)
    Add to the beginning of the file name.
  • [Suffix]:
    Add to the end of the file name. When the file contains an extension, add just before the dot (.).
  • Will be enabled when [File Output] is selected from [Base64 field output form] and [Select a field] or [Enter String] is selected from [How to set Additional String].
Delimiter Optional Available Input the delimiter between the output file name and the additional string.
When a delimiter is not necessary you can leave it out.
  • Will be enabled when [File Output] is selected from [Base64 field output form] and [Select a field] or [Enter String] is selected from [How to set Additional String].
  • If characters are included that are incompatible with the DataSpider file system, they will be converted to underscores (_) at output time.
How to set an Extention Optional - Select how to input the output file name extension.
  • [Include in file name]:(default)
    Specify the file name including extension.
  • [Select a field]:
    Select the field to be used as the file extension from the fields contained in the [schema definition].
  • [Enter String]:
    Input the string to be used as the file extension.
  • Will be enabled when [File Output] is selected from [Base64 field output form].
Extention Optional Available Specify the extension of the output file name.
Enter the schema name when [How to set an Extention] is set to [Select a field].
  • Will be enabled when [File Output] is selected from [Base64 field output form] and [Select a field] or [Enter String] is selected from [How to set Extension].
  • If characters are included that are incompatible with the DataSpider file system, they will be converted to underscores (_) at output time.
  • Uppercase letters will be converted to lowercase at file output time.
  • When the specified string does not contain a dot (.), it will be added automatically.
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

None.

Output Schema

Data model type depends on the [Schema definition].

Reading Schema by Mapper

The schema will be read automatically.

Large Data Processing

Large Data Processing is supported.

Usage on PSP Script

Cannot be used on PSP Script.

Available Component variables

Component variable name Description Supplement
count The number of data read is stored.
  • The default value is null.
object_name The object name is stored.
  • The default value is null.
  • When using aggregate functions or a GROUP BY clause, "AggregateResult" will be stored instead of the object name.
  • The value will not be stored when the number of read records is 0.
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.

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 Supplement
(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

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].
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].
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.InvalidFieldFault The item included in the executed SOQL is invalid. Please check the ExceptionCode or error message, and check for error information in documents related to Salesforce.
com.sforce.soap.partner.fault.MalformedQueryFault The executed SOQL is invalid. Please check the ExceptionCode or error message, and check for error information in documents related to Salesforce.
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