The Thinkwise Platform has powerful functionality to connect to external applications using OAuth. Customers often use external applications, for example a payment system. In that case they often want to import the data to the Thinkwise database, as the external system is their single source of truth. We can use the Thinkwise Platform to synchronize this data. With the support of system flows we can even automate this to automatically collect the latest data.
In this blog I will use Exact Online as the external application to synchronize the projects from Exact to a database.
Creating the data model
I made a very simple model for demo purposes. As a developer you can extract a lot more data from the API.
The data model contains two tables, the oauth_token_info table to save the OAuth access and refresh tokens. The project table contains the information we will extract from the Exact Online API.
App Registration
To call the API first we need to register an application in Exact Online. To do that you can go to the Exact Online App Centre and then go to “Manage my apps”, please note that an account is required to register an application. Here you can register an app by giving it a name and a redirect URI. In my case I used the name “tw_auth_demo” and the redirect URI “https://test.local/oauth-callback”. The Thinkwise platform assumes the redirect URI is “http://localhost/oauth-callback” but Exact Online forces https and doesn’t allow localhost.
After registering the app, you get a “Client ID” and a “Client secret”, we will copy this information to the Thinkwise Software Factory. Open the screen “Project overview” and select the right project and project version. You will see a tab for “OAuth servers”. Here we will add the information from the Exact Online App registration.
Remember when I mentioned the issue with the “Redirect URI”? We will also resolve this right now. Go to the “Runtime configurations” tab and then the “Extended properties” tab. Here we will add the extended property “oauthredirecturi” with the value of your Redirect URI.
Logging in to the API
Now that we have most of the setup done, we can start creating some process flows. The first thing we want to do is obtaining the OAuth access and OAuth refresh tokens. Let’s start by creating the trigger for our process flow. To do that we create a task (I called mine “task_exact_online_oauth_login”), with task logic type set to “None” and we don’t add parameters. Add the task to the “oauth_token_info” table by using the “Table tasks” tab.
Next step is creating the process flow. I called mine “exact_online_oauth_login”. I added 3 process variables that I will use in this flow.
The flow starts with the “Start table task” process action (with the task “task_exact_online_oauth_login”) we just created. This is the trigger for starting the flow. The next step is the “OAuth login connector”. This is responsible for showing the login screen and will give us the token information. Here, it is important to setup the output parameters to fill in the process variables that we have created earlier.
After creating the process flow it should look like the one in the picture below.
Now we will go to the “Functionality” screen to give an implementation for the OAuth login connector procedure. We will create a Control procedure called “prc_exact_online_oauth_login_insert_token_information” with the Code group “PROCESSES” and provide it with a template. This template will write the token information we got from the OAuth login connector to the database.
INSERT INTO oauth_token_info(oauth_server_id, date_created, date_last_updated, access_token, refresh_token, access_token_expires_in)
VALUES
(
'exact_online',
SYSDATETIME(),
SYSDATETIME(),
@var_access_token,
@var_refresh_token,
@var_expires_in
)
Remember to assign it to the “prc_exact_online_oauth_login_oauth_login_connector_exact_online” program object.
Now we have enough to start testing this part. After deploying all the code to the database (and creating a “Menu” in the Software Factory), we can start the application. When we start the task we have just created from the “oauth_token_info” table, the login screen of Exact Online appears. After logging in we should see a record in the oauth_token_info with the access and refresh token information. These tokens should be treated like a password. Leaking them will give potential unauthorized users the ability to get and change data on your behalf! This access token can be used for 10 minutes (600 seconds). So in the next chapter we will explain how to use this token in the HTTP Connector and explain how to extend the lifetime of the access token.
Calling the external API
It is time to start with the interesting part, the process flow for getting the data from the Exact Online API. To do this we create a process flow which contains some steps to load the access token from the database. It optionally refreshes the access token by using the OAuth refresh token connector and call the API using the HTTP Connector.
I called the process flow “exact_online_get_projects” and added 5 process variables to use in this process flow.
The first step in this process flow is a Decision, which will load the OAuth tokens from the database. This Decision will have two arrows, one for the OAuth refresh token connector and the other one will go to the HTTP Connector.
The OAuth refresh token connector has one input parameter. We will assign the variable “var_refresh_token” to this parameter. In the “Output” tab we assign the variables for the Access token, Expires in and Refresh token. In Exact Online, refresh tokens can only be used once. So we need to save the new token information. Saving the token information to the database we will do in the process procedure after finishing the process flow.
The HTTP Connector is the next step, this step will call the Exact Online API. The HTTP Connector requires multiple input parameters. We will give it a constant value for the URL: “https://start.exactonline.nl/api/v1/3153115/project/Projects?$select=ID,Code,Description”, the HTTP method should be “GET” and the headers we will give a constant value of
c { "Key": "Accept", "Value": "application/json" }, { "Key": "Authorization", "Value": "Bearer {var_access_token}" } ]Notice how the variable “var_access_token” is placed between curly braces; this is something Indicium will automatically replace for us with the variable.
We also want to save the content of the response to a variable. So in the “Output” tab we will assign the Content to “var_http_content_response”. This variable will hold the JSON returned by the API.
In the process procedure which we will create later, we will write the data from the Exact Online API to the database. We will also parse the JSON returned from the API.
After following the steps, your process flow should look like the one below.
If you want Indicium to automatically call this process flow, you can add a “Process schedule”.
Just like last time, we will go to the “Functionality” screen and create a Control procedure called “prc_exact_online_get_projects” with the Code group “PROCESSES”.
Create a template with the following contents and assign it to the “prc_exact_online_get_projects_load_oauth_tokens” program object. This procedure will load the tokens from the database.
DECLARE @date_last_updated date_time;
DECLARE @access_token_expires_in oauth_expires_in;
SELECT
@var_access_token = t1.access_token,
@var_refresh_token = t1.refresh_token,
@date_last_updated = t1.date_last_updated,
@access_token_expires_in = t1.access_token_expires_in
FROM oauth_token_info t1
WHERE oauth_server_id = 'exact_online'
-- There was no refresh token. So we stop the process flow
IF @var_refresh_token is NULL
BEGIN
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 0
SET @load_oauth_tokens_download_projects = 0
RETURN;
END
-- We remove 30 seconds from the expire time, so we update the token before it is expired.
DECLARE @calculated_expire_date date_time = DATEADD(second, @access_token_expires_in -30, @date_last_updated)
IF SYSDATETIME() < @calculated_expire_date
BEGIN
-- When not expired, we disable the refresh token connector step and enable the download projects step
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 0
SET @load_oauth_tokens_download_projects = 1
END
ELSE
BEGIN
-- The token is (almost) expired, first go to the oauth_refresh_token_connector
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 1
SET @load_oauth_tokens_download_projects = 0
END
Create another template and assign it to the “prc_exact_online_get_projects_oauth_refresh_token_connector_exact_online” program object. This template updates the new token information to the database.
UPDATE oauth_token_info
SET
date_last_updated = SYSDATETIME(),
access_token = @var_access_token,
access_token_expires_in = @var_expires_in,
refresh_token = @var_refresh_token
WHERE oauth_server_id = 'exact_online'
The last template is the most interesting one, it will parse the JSON and write it to the database. So we will create another template and assign it to the “prc_exact_online_get_projects_download_projects” program object.
MERGE INTO project as p
USING (
SELECT item.ID as id,
item.Code as code,
item.Description as description
FROM OPENJSON(@var_http_content_response, '$.d') as results
CROSS APPLY OPENJSON(results.value)
WITH (ID VARCHAR(255), Code project_code, Description project_description) as item
) as item
ON (p.id = item.ID)
WHEN MATCHED THEN
UPDATE SET p.code = item.Code,
p.description = item.Description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (item.ID, item.code, item.description);
The process flow is now implemented, and we can start deploying all the code to the database from the Creation screen.
Testing the process flow
To test if everything works, I use Insomnia to start the process flow, if you added a “Process schedule” the data should already start showing up in the “Project” screen.
My Indicium instance is running locally, so I executed a POST request to “https://localhost:5001/iam/oauth_demo/exact_online_get_projects” using my credentials (which you can add in the “Basic” tab. More information about starting system flows is written in this blog post: https://community.thinkwisesoftware.com/news-updates-21/test-system-flow-during-development-by-indicium-api-2463.
After clicking on Send, you will see a 204 No Content. That is the expected result. When you refresh the project table in the Windows GUI (or directly with a query on the database), you should see your projects.
I hope this post explains how to use the OAuth Login and OAuth Refresh token connector and how you can use them with the HTTP Connector. This technique can be used by a lot of external applications to synchronize data from the external application to the database, but also from the database to the external application.
