Simple Data Replication Module

This articles guides you through the creation of a simple 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/.../