Exporting to a Database

Owned by Anthony George
Last updated: Jul 13, 2022 by Fiona Sargeant (Unlicensed)

Overview

Exporting data from a database requires three modelling objects:

  1. Table: The location(s) in PhixFlow which holds the data required for export.
  2. Data Exporter: sends data to the datasource using a SQL command.
  3. Datasource: Provides the connection to the database.

Solution

1. Table

  1. Hover over the Table and from the popup menu click  Database Exporter.
  2. The Data Exporter is created and a property editor opens on the right. Alternatively a Data Exporter can be dragged onto the canvas from the Toobar.

2. Database Collector

  1. In the property editor complete the details:
    1. Name, Provide a name that is indicative of the data being exported.
    2. Enabled, Tick this option.
    3. Datasource, This option specifies where the data will be written. You can use an existing Datasource or see step 3 below for creating a new one.
    4. Statement Expression, is the SQL statement that will be used to insert or update the data in your database. Attributes from your table are referenced with pipe.attribute.
      • Insert Example: INSERT INTO MyTable values ({in.Name})
      • Update Example: UPDATE MyTable SET Name = {in.Name} WHERE UID = {in.UID}
  2. Save  Apply and Close your changes.

3. Setting up the Data source

A Datasource is a connection, it can be used to collect and export data.

Existing Datasource;

  1. click on the Datasource icon in the toolbar.
  2. From the list that appears, drag the required Datasource onto your canvas.
    1. If you have specified a required Datasource in your data exporter it will automatically connect to it.

New Datasource;

  1. From the toolbar drag a Datasource onto the canvas.
  2. In the properties window that opens on the right set the following:
    1. Name, Indicative of the datasource you are connecting to.
    2. Database Driver, Select the appropriate database.
    3. Datasource instance, add a new one:
      • Enter a Name.
      • Enter the URL for the database.
      • Specify the Username and Password.
      • Tick the Enabled check box when it is ready to use.
    4. Tick the Enabled check box when it is ready to use.
    5. We recommend you tick Protect during import to prevent this datasource being updated or deleted during import.
    6. Other settings are optional.
    7. Save  Apply and Close your changes.

Learn More