Select * from Salesforce using (SOQL) REST Api and BizTalk 2013 R2

Posted on Updated on

Quite recently I was working for a project that needed Salesforce data from cloud back to on premise SQL.

Salesforce Rest Apis enable working with Queries and a SOQL query can be executed under Query resource that returns all the results in a single response, or if needed, returns part of the results and an identifier used to retrieve the remaining results.

Rest Api Call: https://yourInstance.salesforce.com/services/data/v20.0/query/?q=SELECT+name+from+Account -H “Authorization: Bearer token”

As we can see there the SOQL needs to have the field name that we want and a select * is not supported by SOQL.

So the requirement was to fetch all the data, that could have been achieved by specifying all the field names on Query but then the solution would be dependent on Salesforce updates and modification.

The next approach is to query the available fields from Salesforce and dynamically create the query to execute.

Rest Api :

https://yourInstance.salesforce.com/services/data/v20.0/ sobjects/Account/describe

The resultant xml is like:

QueryResultXmlCapture

Now we can iterate over all the <fields> and get all field names for our SOQL query.

C#Capture.JPG

And then build the “Select GetAllFieldsQuery(describe Xml) from Account”

This way for any changes to Salesforce object, the interface doesnot need to change and is not prone to breaks or unexpected result because the results are laways in sync on Salesforce cloud and On premise SQL.

The Rest Api calls are easily done via BizTalk 2013R2 Web-Http adapter. The default response for Api calls is Json and BizTalk 2013R2 is fully equiped with tools to handle Json messages. If however, we intend to have the response as xml, this can be achived by setting Http Header – Accept : application/xml

AdapterPropertiy

A comprehensive Rest Api documentation for Salesforce API_REST.pdf

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s