Data Exchange

Overview

This functionality is used to replicate data between databases that are accessible on the same network (ps. The Internet is also a network, it doesn't have to be a LAN). Following are some use-cases for this functionality:

  • Export a product list from a master database into client databases.

  • Export Purchase Orders from one database to Sales Orders in another Database.

  • Replicate Customers between databases.

Type: Exchange

The Data Exchange module can be used to exchange data between databases. Records are selected from the Source database and used to update a Target database. The Target SQL will be applied to each record that was selected from the Source database. Connection strings can be set for both the Source and Target database on each item, which allows for bi-directional updating between two databases in one Data Exchange setup. To improve performance, it is recommended that Stored Procedures be used for both the Source SQL and the Target SQL. Both sides are able to read and update values. eg. After selecting a number of records from the Source database, you could update an Audits table in the Source database to flag which records have been exchanged.

The Data Exchange Setup can be accessed by clicking on Menu > Data Exchange. Each Data Exchange record consist of a Header and one or more child Items which contain the Connection strings and SQL.

Type: Update

This action can also be used to periodically update a Target Database by setting Type = Update. When this option is used you only need to set the Target Connection String and the Target SQL.

Header

  • Description

  • Schedule - Click Menu > Edit Schedule to set when the Data Exchange will be executed. See the Scheduler topic for more information.

  • Active - When Active is false, the Scheduler is disabled.

Items

  • Description

  • Source - The Source database - select from FB3.0 or SQL 2014

  • Target - The Target database - select from FB3.0 or SQL 2014

  • Connection String: Source - eg. server=127.0.01; port=3050; database=C:\Temp\QUICKEASY.FDB; username=SYSDBA; password=password;

  • Source SQL - Contains the SQL to select records from the Source database. The fields will be matched to the input parameters on the Update SQL. Right-click the Item and select: Edit Source SQL

  • Connection String: Target - eg. server=127.0.01; port=3050; database=C:\Temp\QUICKEASY.FDB; username=SYSDBA; password=password;

  • Target SQL - Contains the SQL to update the Target database with the records which were selected from the Source database. This SQL must be applied to each record that was selected and it may contain input parameters which have the same name as the fields which were selected from the Source database. Right-click the Item and select: Edit Target SQL

  • Active - Only Active items will be executed when the Exchange is executed. Default = False.