Salesforce Connector

Query Salesforce with SFDC()

Use the SFDC() table function to issue SELECT queries against Salesforce.com objects.

Connect’s SFDC() table function returns result sets that you can query using SELECT, just as you can with existing Connect tables or result sets from SHOW commands.

SFDC() Syntax

To select data from a Salesforce.com table, use:

SFDC(SOQL='soql_query_string',
UserName='username',
Password='password',
[, ReadAll=true|false]
[, Environment='Sandbox|Production']
[, Retries=#]
[, RetryInterval=#minutes]
[, RowLimit=#]
[, BatchSize]
[, ConnectionTimeoutInMillis=#]
[, QueryTimeoutInMinutes=#]
[, ReadTimeoutInMinutes=#])

Where the first three arguments are required:

• SOQL: Takes a Salesforce.com SOQL query string.
• UserName: The Salesforce.com username. The user must have appropriate permissions in Salesforce.com to use the Salesforce.com APIs, and also have access to the object referenced in the TableName parameter.
• Password: The password+token for the Salesforce.com account.

Optional arguments:

• ReadAll: When true (default), data is available in Connect only after all data has been read from Salesforce.com.

This setting enhances durability, as it ensures all data can be read from Salesforce.com before proceeding, but it reduces perceived performance, as Connect cannot present any data until Salesforce.com has returned all of it to Connect.

When false, data is delivered as it is fetched from Salesforce.com, but if a failure occurs during the fetch, a simple exception is returned, with no recovery or retries.

• Environment: Allows Connect to establish a session with either your production or sandbox Salesforce.com endpoints.
• Retries: The Retries parameter controls how many times the function will retry if a failed connection occurs during query execution and data retrieval from Salesforce.com. The default is 3 retries.
• RetryInterval: Specifies the number of minutes to wait for each retry.. The default is 0 minutes.
• RowLimit: Restricts the number of rows returned by a SELECT query issued to Salesforce.com.  The default value is null.
• BatchSize: Sets the number of rows returned by each fetch request performed against Salesforce.com. Default is 200. Maximum allowed value is 2000. A larger number does not always imply faster.
• ConnectionTimeoutMillis: The amount of time the SFDC() function will wait to establish a connection and authenticate to Salesforce.com. Default is 60000 milliseconds.
• QueryTimeoutInMinutes: The amount of time the SFDC() function allows to call the Salesforce.com query() and queryMore() methods. Default is 10 minutes. Consider increasing this value if you expect a given SOQL query to exceed 10 minutes to fetch the entire result set.
• ReadTimeoutInMinutes:The amount of time Connect will spend fetching a single block (as defined by BatchSize) of data from Salesforce.com. Default is 10 minutes.
  
  This setting is useful in testing queries against large Salesforce.com objects. We recommend it when designing queries against SFDC tables with large numbers of rows.
  
  NOTE: Your query could pull hundreds of thousands, or even millions, of rows from Salesforce.com into Connect if you don’t use valid Where expressions in the SOQL parameter. Use RowLimit to enforce an explicit ceiling.

SFDC() Examples

In our examples, we’ll learn ways to issue a simple SELECT and INSERT into a Connect table using the SFDC() table function.

 

SFDC() Simple SELECT

Our first example is a query of our Opportunity table in Salesforce.com.

NOTE: Enter your own valid Salesforce.com credentials and information (for the Salesforce.com TableName, UserName, and Password) to query your Salesforce.com objects.

select 'From SALESFORCE >>>>>> ' as row_source, 
Id, AccountId, StageName, 
Amount, CloseDate, 
OwnerId, CreatedDate
from SFDC(SOQL='select Id, AccountId, 
StageName, Amount, 
CloseDate, OwnerId,
CreatedDate 
from Opportunity 
where CreatedDate >= 2014-01-08T22:38:04Z 
and StageName = ''Closed Won''',
UserName='sfdc_username',
Password='sfdc_password_and_token',
Environment='Production',
ReadAll=true,
Retries=2,
RetryInterval=1,
RowLimit=100,
QueryTimeoutInMinutes=2);

 

SFDC() INSERT into Connect Table

We’ll start by creating a variable to hold our connection string details:

set v_sfdc_cnx = 'UserName=''username'',Password=''password'', Environment=''Production'', RowLimit=100';

Enter your own valid SFDC info (for the Salesforce.com UserName and Password).

Now let’s use INSERT to create a Connect table on the fly from a call to the SFDC table function.

insert into Delta(TableName='delta.local_sfdc_opportunity', Overwrite=true)
select 'Now in DELTA >>>>>>' as row_source, 
Id, AccountId, 
StageName, Amount, 
CloseDate, OwnerId, 
CreatedDate
from SFDC(SOQL='select Id, AccountId, 
StageName, Amount, 
CloseDate, OwnerId, 
CreatedDate 
from Opportunity 
where CreatedDate >= 2014-01-08T22:38:04Z and StageName = ''Closed Won''',
{:v_sfdc_cnx});

 

Now we can query our local delta table instead of repeatedly hitting Salesforce.com, at least until a newer result set is required:

select * 
from delta.local_sfdc_opportunity;



 

Insert, Update, Deleting data from Salesforce

Utilizing the same SFDC() function, we can also Insert, Update and Delete data objects that reside in Salesforce. Functionality is only applicable when the function is used with the TableName parameter and does not include a SOQL parameter.

Because Salesforce does not support direct insert/update/delete through the SOQL language, the SFDC function uses unique identifiers to modify the data. Also, any Salesforce security will be enforced so the Salesforce credential utilized must have privileges to modify records.

Note – Some tables do not allow direct modification through the PartnerWSDL SOAP API. If you receive exceptions double check that the table you wish to modify can be modified using the Salesforce SOAP API.

Insert into SFDC()

Insert command will allow a Connect user to add a new row of data to applicable Salesforce Objects.

insert into SFDC(TableName={Name of table being updated},CredentialName={Name of Salesforce Credential}) (Comma separated list of fields being inserted) values (Comma separated list of values being inserted);

Insert Example:

insert into SFDC(TableName='Note', CredentialName='sfdc') (Title, Body, ParentId) values ('Title-Test', 'Body-Test', '0012800000lNrJvAAK');

Update SFDC()

Update command will allow a Connect user to modify an existing row (or rows) of data to applicable Salesforce Objects.

Update SFDC(TableName={Name of table being updated},CredentialName={Name of Salesforce Credential}) set {update criteria}

Update Example:

update SFDC(TableName='Note', CredentialName='sfdc') set Body = 'Body-Updated' where Title = 'Title-Test';

Delete from SFDC

Delete command will allow a Connect user to delete an existing row (or rows) of data to applicable Salesforce Objects.

delete from SFDC(TableName={Name of table being updated},CredentialName={Name of Salesforce Credential}) set {delete criteria}

Delete Example:

delete from SFDC(TableName='Note', CredentialName='sfdc') where Title = 'Title-Test';