This articles guides you through the creation of an elementary application module on Invantive Cloud to replicate data from Exact Online to a table re-created in SQL Server every time the module is executed. Advanced and/or scheduled replication is available using Invantive Data Replicator and/or Invantive Data Hub.
Instructions
Prepare Database with Exact Online and SQL Server
Execute the following steps to create a (virtual) database which allows direct access to SQL Server and Exact Online:
- Create a database with Exact Online as data container on https://cloud.invantive.com/app/setup/databases/new.
- Open the database on Invantive Cloud.
- Click on “Data Containers“.
- Check that the Exact Online data container has alias “eol“.
- Add a data container to the database using SQL Server.
- Check that the SQL Server data container has alias “sqlserver“
Application module
Execute the following steps:
- Create a new application on https://cloud.invantive.com/app/development/applications/new.
- The name can be “Sample“ and description “Sample replication“.
- Click on “Modules”.
- Add a new module with code “EOL2SQLSERVER” and description “Replicate data from Exact Online to SQL Server“
- The source code specifies that a HTML page is to be printed after copying the data from Exact to SQL Server:
declare
l_output varchar2;
begin
l_output := '';
l_output := l_output || '<h2>Replicate</h2>';
--
-- Copy the Accounts from all Exact Online companies
-- to the SQL Server database as a new table.
--
create or replace table eol_accounts@sqlserver
as
select *
from exactonlinerest..accountsbulk@eol
;
--
cloud_http.set_use_template(true);
cloud_http.set_response_body_text(l_output);
end;
Execute Replication
Execute the following steps to execute the replication:
- Go back to the new created application.
- Click on “Execute“.
- Choose the database you have created.
- Click on “Execute“.
- Click on the module.
- After some time, depending on the amount of accounts in Exact Online, a HTML page is rendered.
Now check your SQL Server database and see the new table.
Execute Exact Online / SQL Server integration using Curl
The module can be executed interactively from Invantive Cloud. With App Online, you can also run the module using a browser or curl.
Execute the following steps to run the module:
- Determine the URL to use on App Online as follows.
- Run the application.
- Choose the database.
- Find the module you wish to run from App Online.
- Select the link “Copy Link“ at the bottom right corner of the module.
- The link is now on your clipboard.
- Paste the link in a new browser window.
- Enter your Bridge Online user and password (which is shared with App Online).
- In case an authentication error occurs please ensure that on the database the correct IP addresses are whitelisted.
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.