How to call Salesforce REST API via ODBC driver

Introduction

Salesforce is a popular CRM tool which lets you manage your sales, contacts, products and other useful things. In this tutorial, you will learn how to call Salesforce REST API via ODBC driver using ZappySys ODBC PowerPack. You will find out how to register a Connected App in Salesforce, set OAuth authentication and use it for data retrieval. We will cover a topic of how to load leads from Salesforce and use them virtually in any application which supports ODBC. We will not stop with that and touch a possibility of calling any method of any REST API-based Salesforce API; be it insert or simple retrieval.

Prerequisites

Make sure you meet these requirements:

  1. ZappySys ODBC PowerPack installed.
  2. Have a registered, working Salesforce account.

Step-by-Step – Calling Salesforce REST API with ODBC driver

To call Salesforce API, you need to have a Salesforce Connected App with enabled OAuth authentication. It will be used by ODBC Data Source, which we will create in the subsequent step. Then we will configure ODBC Data Source with Salesforce REST API URL and other parameters to access the leads.

How to Find Your Salesforce Instance ID

To call Salesforce API you have to supply Salesforce InstanceID in API Url. If you are not sure what is your Salesforce Instance ID then its very simple. Just look at your Salesforce URL after you login in Browser. First part in Domain would be your InstanceID (e.g. na34 if your URL looks like https://na34.salesforce.com/xxxxxxxxx ). You can also click Setup > Under Administrator > Click Company Profile > Company Information > Look for Instance

How to find your Salesforce Instance ID

How to find your Salesforce Instance ID

Create Salesforce OAuth App

Once you know your Instance ID now let’s move on to next part (Register OAuth App)

  1. Optional step. The first thing you may want to do is obtain a Salesforce Developer Edition Organization.
  2. Login into your account and go to Setup as shown below:
    Accessing Salesforce Setup to create an OAuth App

    Accessing Salesforce Setup to create an OAuth App

  3. Then in the search bar input “app” and open App Manager:
    Searching Salesforce App Manager to create an OAuth App

    Searching Salesforce App Manager to create an OAuth App

  4. Then click New Connected App to create a new OAuth App:
    Creating a Salesforce App to get data via ODBC driver

    Creating a Salesforce App to get data via ODBC driver

  5. Give your App a name and:
    – Enable OAuth Settings
    – Set callback URL to https://login.salesforce.com/services/oauth2/success.
    – Add an OAuth scope you want or simply add “Full access“.
    – Add “Perform requests on your behalf at any time” scope to get a refresh token so that you don’t have to re-authenticate each time requesting an access token:

    Configuring Salesforce App OAuth settings to get data via ODBC driver

    Configuring Salesforce App OAuth settings to get data via ODBC driver

  6. Click “Save”.

Configure Salesforce OAuth refresh token validity

  1. Go to “Manage Connected Apps” menu item and press on your App name:
    Finding Salesforce App to configure its policies

    Finding Salesforce App to configure its policies

  2. Click “Edit Policies” to configure refresh token validity:
    Editing Salesforce App OAuth refresh token issuing policy

    Editing Salesforce App OAuth refresh token issuing policy

  3. Set “All users may self-authorize” and “Refresh token is valid until revoked” options:
    Making Salesforce App OAuth refresh token to never expire

    Making Salesforce App OAuth refresh token to never expire

  4. Click “Save”.

Create an ODBC Data Source using ZappySys JSON Driver

  1. Open ODBC Data Source Administrator
    Finding Windows ODBC Data Source Manager

    Finding Windows ODBC Data Source Manager

  2. Add a new ODBC Data Source based on ZappySys JSON Driver:
    Adding a new ODBC Data Source based on ZappySys JSON Driver

    Adding a new ODBC Data Source based on ZappySys JSON Driver

Configure the Data Source

  1. Start configuring Data Source:
    – Enter URL:
    https://<your_instance>.salesforce.com/services/data/v20.0/query/?q=SELECT+id,name,email+FROM+Lead
    – Select “OAuth” for Connection Type.
    – You can set HTTP Request Method to “GET”.
    – Continue by clicking “Click to Configure”:

    Configuring ODBC Data Source to get leads from Salesforce

    Configuring ODBC Data Source to get leads from Salesforce

  2. Once a window appears, go to Salesforce App Manager and view your App settings, so that you can use them for Data Source configuration:
    Opening Salesforce App OAuth settings to be used in ODBC Data Source

    Opening Salesforce App OAuth settings to be used in ODBC Data Source

  3. Then go back to ODBC configuration, select tab Advanced and set Callback/Return Url to “https://login.salesforce.com/services/oauth2/success“:
    Configuring Callback Url in ODBC Data Source OAuth options

    Configuring Callback Url in ODBC Data Source OAuth options

  4. Then go back to tab General and configure rest options using Salesforce App settings:
    – From Salesforce App settings copy Consumer Key to Client Id
    – Then copy Consumer Secret to Client Secret
    – 
    Set Authorization Url to “https://login.salesforce.com/services/oauth2/authorize”
    – Set Access Token Url to “https://login.salesforce.com/services/oauth2/token”

    Configuring ODBC Data Source with Salesforce App OAuth settings

    Configuring ODBC Data Source with Salesforce App OAuth settings

  5. Click “Generate Token” button to generate the tokens. Once Salesforce login window appears, enter your Salesforce credentials and log in.
  6. Click “OK” to close the window.

Load data from Salesforce using ODBC Data Source

We are ready to retrieve the data. So just open “Preview” tab in Data Source configuration window and hit “Preview Data” button:

Previewing Salesforce data in ODBC Data Source

Previewing Salesforce data in ODBC Data Source

As you see we have no actual lead names because they are present in column “records”. So replace “$” with column name “records” in “SELECT * FROM $” query to select actual lead records from JSON property “records” (which denotes a table in ODBC):

Changing table name in ODBC query to get the actual leads

Changing table name in ODBC query to get the actual leads

Then click “Preview Data” again to get the leads:

Successfully retrieved Salesforce leads using Salesforce REST API and ODBC Driver

Successfully retrieved Salesforce leads using Salesforce REST API and ODBC Driver

You are ready to use ODBC Data Source in an application!

Pagination in Salesforce REST API

Even you are ready to use the configured ODBC Data Source in an external application, the solution is not yet fully implemented. If you have too many leads or records than Salesforce can give you in one HTTP response you won’t get all the records. Thus you will need to use pagination. For that, go to “Pagination” tab and configure it like this:

Configuring ODBC Data Source to paginate through Salesforce results

Configuring ODBC Data Source to paginate through Salesforce results

If you need something more sophisticated then check the article about pagination (initially it was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too).

Using ODBC query in an application

Let’s say you want to access Salesforce Accounts instead of Leads. It seems the most logical thing to do is create another ODBC Data Source and change the URL. However, you can use the same ODBC Data Source and override its properties with ODBC query. Just copy the generated ODBC query, leave the things you want to override and use it in your application:

In the example above, we are overriding “Url” property and accessing Accounts.

Check an article how to import data into Excel using ODBC Data Source with ZappySys to see how to use ODBC Data Source in your application. For more examples, check ODBC PowerPack blog category.

Field names for SELECT Query

One known issue in Salesforce REST API is you cannot use * to fetch all columns in SOQL. So you must specify column names manually. If you like to know all possible column names for given Table name then use below query.

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

For example to get fields for Account table in ns34 instance you can use below URL

https://na34.salesforce.com/services/data/v44.0/sobjects/Account/describe/

 

Error Handling in Salesforce REST API

Sometimes errors occur... they just do and there is nothing you can do! Or can you? Actually, in ODBC PowerPack you can handle them in two ways.

METHOD 1 - Using Error Handling Options

When to use?

You may want to use them when your source is a resource located on the Internet; e.g. a file on a website, a file on an FTP server or just a plain API HTTP response. By default, when a remote server returns an error, data retrieval is stopped, an error is raised and no data is given back to you. This might not be always desirable.

Scenario 1

Imagine a scenario, that there is a web server which each day at 12 AM releases a new JSON file with that day's date as filename, e.g. http://www.some-server.com/data/2018-06-20.json. And, of course, you want to download it and use it daily in your Power BI report. But you have a problem: Power BI report data sources are refreshed each hour and you may get HTTP 404 status code (no file was found) when a file is not released yet. Which consequentially means other data sources won't be updated as well and you will see old and cached data on the report. That's where you could use Continue on any error or Continue when Url is invalid or missing (404 Errors) to avoid an error being raised and let other data sources to be updated.

Scenario 2

Another scenario is when you expect a web server to raise some kind of HTTP error when accessing a URL. You don't want ODBC Data Source to raise an error but instead, you want to get response data. That's where you can use Continue on any error or alike together with  Get response data on error to continue on an error and get the data:

METHOD 2 - Using Connection [Retry Settings]

Another scenario you may run into is a buggy web server. You ask it to give you some file or data and it, like a snotty kid, just doesn't give it to you! You have to ask twice or thrice before it does its job. If that's the case, you have to retry HTTP requests using Connection:

Using various Salesforce APIs

We used Salesforce REST API Search method to access the leads. The SOQL query we used looks identical to SQL in our case, so you can use other table names to access their data too. Or you can even use a more complex SOQL query in the same Search method. The Search method is not the only one that is available in Salesforce REST API, check other REST API methods too (you can even use the ones that insert/update the data).

In addition to Salesforce REST API, Salesforce has many more APIs, such as Salesforce Marketing Cloud API, Salesforce CPQ API, Salesforce IoT REST API, and others. You can use virtually any of them with ZappySys JSON Driver or ZappySys XML Driver since the drivers are highly configurable (you can call any API method with any HTTP method – be it POST, PUT or any other).

Resources

Conclusion

We covered a whole process of how to access Salesforce data: starting from creating a Salesforce application, continuing by authenticating using OAuth and finishing by using an ODBC query in a target application. You learned how to retrieve the fields of Salesforce leads and paginate through the results. Now you are on a good path of using other API methods and even other Salesforce APIs 😉

Posted in JSON File / REST API Driver, ODBC PowerPack and tagged , , , .