This page is for data modellers who want to export data to a different database.

Overview

A database exporter allows data held in a table to be written to a database outside your PhixFlow instance. To do this, you must have a datasource configured to communicate with the database.

To add a new database exporter to an analysis model:

1. Go to the model's toolbar → Create group.
2. Click

   Insert excerpt
   _database _database nopanel true

    to expand the menu.
3. Drag a 

   Insert excerpt
   _database_exporter _database_exporter nopanel true

    onto the analysis model.

To add an existing database exporter to an analysis model, in the model diagram toolbar:

1. Go to the model toolbar → List group.
2. Click

   Insert excerpt
   _database _database nopanel true

    to expand the menu.
3. Click 

   Insert excerpt
   _database_exporter _database_exporter nopanel true

    to open the list of available database exporters.
4. Drag a database exporter into the analysis model.

Insert excerpt
_property_tabs _property_tabs name basic-h nopanel true

Basic Settings

Inputs

A list of input pipes for this Database Exporter.

This section has a toolbar with 

The grid contains a list of input pipes that connect to this datasource.

Input Multiplier

Advanced

Anchor
databaseExporterQueryString databaseExporterQueryString

Statement Expression Query String

Query String Description

Database Exporters can be set up to update a database using either a plain SQL query or PL/SQL. If you want to use PL/SQL tick the box Uses PL/SQL.

Plain SQL Query

Enter an insert or update query just as you would to edit the database directly. The only important difference when creating queries in PhixFlow is that expressions must be enclosed in curly braces i.e. start with a '{' and end with '}'. This includes referencing an attribute name from the input table, which is just a simple expression.

Note that when exporting a attribute field of Type 'TrueFalse', this attribute must be mapped correctly to the corresponding underlying database column it is being exported into. An 'if' style expression should be used in the query string to transform the TrueFalse value into the appropriate database column. Mapping to a single character database column - {if(aTrueFalseAttribute,'T','F')}
Mapping to a single numeric database column - {if(aTrueFalseAttribute,1,0)}

The other difference between entering a query in PhixFlow and submitting a query to the database directly is that you must not include the standard SQL statement terminator, e.g. a semi-colon, at the end of your query.

insert into INTL_CODES values({Operator}, {Code})

If we have an input table containing a list of international codes, with attributes Operator and Code, this query will insert all data from the input table into a database table INTL_CODES.

insert into INTL_CODES values({Operator}, {Code}, {toDate(StartDate)})

The table INTL_CODES also includes a date, START_DATE, which we will populate using the attribute StartDate in the input table. In the table, this date is stored as a string in the format YYYYMMDD. In the query above, we have converted this string to a date using a PhixFlow expression.

insert into INTL_CODES values({Operator}, {Code}, to_date({StartDate}, 'yyyymmdd'))

This query performs the same inserts as the previous example, but in this case we are converting the StartDate string value to a date using a database function.

PL/SQL

As for a plain SQL query, you can write PL/SQL just as you would to run against the database directly - but putting any PhixFlow expressions in curly braces i.e. starting with a '{' and ending with '}'.

declare v_count number;
begin
	select count (1)
	into v_count
	from INTL_CODES
	where operator = {Operator} and code = {Code};
 
	if (v_count = 0)
	then
		insert into INTL_CODES
		values( {Operator}, {Code}, {toDate(StartDate)} );
	else
		update INTL_CODES set START_DATE = {toDate(StartDate)}
		where operator = {Operator} and code = {Code};
	end if;
end;

In this example we are loading international codes into a database, as in the plain SQL examples. Here, we want to insert a new value if no value already exists in the table for the Operator/Code combination; otherwise we will edit the existing record with the START_DATE of the current value that we have read from the input table.