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.

Image RemovedImage Added

Solution

1. Table

1. Hover over the Table and from the popup menu click 

   Insert excerpt
   _database_exporter _database_exporter nopanel true

   .
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 

   Insert excerpt
   _finish _finish nopanel true

    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 

      Insert excerpt
      _finish _finish nopanel true

       your changes.

Learn More

See Datasource for all options available for a Datasource.

See SQL Updates for help creating update statements.

See SQL Insert for help creating insert statements.

• Datasource 
• SQL UPDATE Statement (w3schools.com)
• SQL INSERT INTO Statement (w3schools.com)