Exact Online Bulk Download
Are you just looking for an option to download any data taken from the Exact Online APIs? Don’t need to create a mini-website for a specific download. After finishing the signup on Invantive Cloud as described in:
then just use https://access-odata.com, Power Query or Power BI with Invantive Cloud.
Invantive Cloud’s recent history
Invantive SQL handles the data of approximately over ten thousand companies using on-premise products. Invantive Cloud was added in October 2019 as the both secure and online variant of on-premise products such as Invantive Control for Excel and Invantive Data Hub. Invantive invested heavily in scaling up and out on Invantive Cloud since October 2019. Recent additions enable you to create mini websites that actually work with the data in the over 70 supported (cloud) platforms, whether that is download or uploads of data.
In this sample of the new featues on Invantive Cloud I will guide you through:
- the registration of a virtual database with some Exact Online companies;
- the creation of a module that downloads a very simple list in CSV of articles from these Exact Online companies;
- putting a mini-site in front of this module using a CDN.
The same techniques can be applied to create full-fledged data-driven websites using Invantive PSQL. Thanks to the integrated support for a CDN and extensive caching features, such websites scale very well globally. So let’s get started with the registration of a virtual database with some Exact Online companies!
Register Database with Exact Online Companies
I will assume that you have already registered an account, whether that is a 6-month trial of the premium editions or a free consulting subscription.
Also remember to set up your Bridge Online configuration by setting the password.
Execute the following steps to register a virtual database with some Exact Online companies:
- Navigate to the Databases form.
- Choose the “New” button.
- Select “Exact Online” from one of the licensed platforms as shown below.
- Choose the Exact Online country.
- Approve the application.
- The virtual database is registered.
- Change the name to “My Download Database“.
- Press “Save“.
- (optional) Restrict the list of companies used in the startup SQL.
You have now registered a virtual database with some of all available Exact Online companies:
As a next step, we will create an application with a single module that retrieves article information from the Exact Online company and returns it in CSV format.
Create Application module to retrieve Exact Online articles
First we need to create a new application. All modules in an application can be run against a virtual database containing one or multiple (cloud) data containers. Execute the following steps to create the application:
- Go to the Applications menu.
- Choose the “New“ button.
- Define an application as shown in the picture below.
- And press “Save“.
Now we will create a single module that selects the articles, joins them together within a row by a comma and across rows by line feed. Execute the following steps:
- Choose the “Modules“ button from within the new application.
- Choose the “New“ button.
- Define a module as shown in the picture below.
- To ease definition, copy and paste the Invantive PSQL from below.
- And press “Save“.
The associated Invantive PSQL is:
declare
l_output varchar2;
begin
--
-- Create a CSV of all articles in Exact Online.
--
select listagg
( csvencode(itm.code) || ',' || csvencode(itm.description)
, chr(13)
)
into l_output
from exactonlinerest..items@eol itm
;
--
-- Add header.
--
l_output := csvencode('Code') || ',' || csvencode('Description') || chr(13) || l_output;
--
-- Return to user.
--
cloud_http.set_response_body_text(l_output);
cloud_http.set_response_content_type('text/csv');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="transactions.csv"');
end;
Finally, we will execute the module using the database from the database as follows:
- Go back to the definition of the database “My Downloads“.
- Press the “Execute“ button.
- Choose the database “My Download Database“:
- And choose “Execute“.
- A menu is generated with all executable modules.
- Select the first and sole module:
- After some waiting time, a file will downloaded to your PC:
The next step is to create a mini-site for this specific module, requiring a user to log on and then returning the download.
Create mini website for the Exact Online Article CSV Download
Invantive App Online is the preferred platform to execute modules defined in Invantive Cloud.
The first step is to use the App Online URL button to copy the URL to run the module. It will resemble something like:
https://app-online.cloud/apps/execute/app/GUID/database/STRING/module/GUID
It specifies the path to the module to run in combination with the unique identifier for the virtual database with Exact Online.
From curl, you can run the module using the following syntax:
curl \
--basic \
--user user...:password... \
--output c:\temp\results.html \
--url https://app-online.cloud/apps/.../databases/.../modules/.../
A more extensive sample using curl to retrieve data from Invantive Cloud is available at Pre-load data in OData-cache to avoid Power BI timeouts.
The following steps are targeting AWS CloudFront, but similar steps apply to other popular proxies such as CloudFlare or NGINX.
Please remember to define all client IP addresses on the virtual database to allow access. Use ‘*’ to completely disable IP-address validation.
As first step, register a CloudFront distribution such as on alternate domain name mydownload.acme.com:
Then define app-online.cloud as the origin:
Finally define the behavior to forward the authorization to Invantive App Online:
Once deployed, you can download the CSV by accessing mydownload.acme.com and entering your Invantive Bridge Online credentials.