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.
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“
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 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/.../