Replicate Multiple Sage 300 Accounts via the CData Sync CLI

CData Sync for Sage 300 is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple Sage 300 connections. In this article we show how to use the CLI to replicate multiple Sage 300 accounts.

Configure Sage 300 Connections

You can save connection and email notification settings in an XML configuration file. To replicate multiple Sage 300 accounts, use multiple configuration files. Below is an example configuration to replicate Sage 300 to SQLite:

Windows

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
 <DatabaseType>SQLite</DatabaseType>
 <DatabaseProvider>System.Data.SQLite</DatabaseProvider>
 <ConnectionString>User=SAMPLE;Password=password;URL=http://127.0.0.1/Sage300WebApi/v1/-/;Company=SAMINC;</ConnectionString>
 <ReplicateAll>False</ReplicateAll>
 <NotificationUserName></NotificationUserName>
 <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
 <TaskSchedulerStartTime>09:51</TaskSchedulerStartTime>
 <TaskSchedulerInterval>Never</TaskSchedulerInterval>
</CDataSync>

Java

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
<DatabaseType>SQLite</DatabaseType>
 <DatabaseProvider>org.sqlite.JDBC</DatabaseProvider>
 <ConnectionString>User=SAMPLE;Password=password;URL=http://127.0.0.1/Sage300WebApi/v1/-/;Company=SAMINC;</ConnectionString>
 <ReplicateAll>False</ReplicateAll>
 <NotificationUserName></NotificationUserName>
 <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
</CDataSync>

Sage 300 requires some initial setup in order to communicate over the Sage 300 Web API.

• Set up the security groups for the Sage 300 user. Give the Sage 300 user access to the option under Security Groups (per each module required).
• Edit both web.config files in the /Online/Web and /Online/WebApi folders; change the key AllowWebApiAccessForAdmin to true. Restart the webAPI app-pool for the settings to take.
• Once the user access is configured, click https://server/Sage300WebApi/ to ensure access to the web API.

Authenticate to Sage 300 using Basic authentication.

Connect Using Basic Authentication

You must provide values for the following properties to successfully authenticate to Sage 300. Note that the provider reuses the session opened by Sage 300 using cookies. This means that your credentials are used only on the first request to open the session. After that, cookies returned from Sage 300 are used for authentication.

• Url: Set this to the url of the server hosting Sage 300. Construct a URL for the Sage 300 Web API as follows: {protocol}://{host-application-path}/v{version}/{tenant}/ For example, http://localhost/Sage300WebApi/v1.0/-/.
• User: Set this to the username of your account.
• Password: Set this to the password of your account.

Configure Queries for Each Sage 300 Instance

Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the Sage 300 API. The statement below caches and incrementally updates a table of Sage 300 data:

REPLICATE OEInvoices;

You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple Sage 300 accounts into the same database:

You can use a different table prefix in the REPLICATE SELECT statement:

REPLICATE PROD_OEInvoices SELECT * FROM OEInvoices 

Alternatively, you can use a different schema:

REPLICATE PROD.OEInvoices SELECT * FROM OEInvoices

Run Sync

After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:

Windows

Sage300Sync.exe -g MyProductionSage300Config.xml -f MyProductionSage300Sync.sql

Java

java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar Sage300Sync.jar -g MyProductionSage300Config.xml -f MyProductionSage300Sync.sql