A Database Exporter allows data held in a Stream to be written to a Datasource.
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
Basic Settings
| Field | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Name | Name Enter the name of the Database Exporterdatabase exporter. | |||||||||
| Enabled | Tick when
database exporter is ready to be used. | |||||||||
| Datasource | The Datasource that this Database Exporter Select a datasource (database) to which this database exporter will write to. | |||||||||
| Statement Expression | The Enter an expression to generate the query that will be used to write writes data to the target datasource. See databaseExporterQueryString for Database Exporter Query String, below, for details. | |||||||||
| PL/SQL | If ticked, a
block to write to the target datasource. |
Inputs
A list of input pipes for this Database Exporter.
This section has a toolbar with
and Insert excerpt _list_show _list_show nopanel true
; see standard buttons.Insert excerpt _refresh _refresh nopanel true
The grid contains a list of input pipes that to which this user belongs. To add a user group to the list:
- Click
to list all the user groups in the repository.Insert excerpt _user_group _user_group nopanel true - Drag user groups into this list. This automatically adds the user to the user group.
To remove a user from a user group, use the toolbar button
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
For external users, the list of user groups shown for the user is read-only. The user groups are the PhixFlow equivalents that map to the Active Directory user groups; see Configure Active Directory Integration for details.
Input Multiplier
| Field | Description | |
|---|---|---|
| Input Multiplier | This expression works in the same way as the Input Multiplier Expressions on Streams and File Exporters. A complete export is carried out for each value in the list of values returned by the Input Multiplier Expression. If the Input Multiplier Expression evaluates to an empty list then no export will be carried out. If no Input Multiplier Expression is set, a single export will be carried out. | Analysis Models|
| A list of Analysis Models containing this Database Exporter. | ||
Advanced
Fields to Update in Source Stream
| Field | Description |
|---|---|
| Record Export Time | If required, select an attribute from the input Stream from the drop down list. If an attribute is selected, when the exporter writes a record to the target Datasource the selected attribute in the input record will be updated with the export time. If a record in the input Stream is not exported, this attribute will be left blank. If a record is exported - but the export is rolled back - the attribute will be updated, and set back to a blank record. Warning: if a record fails to export then this attribute will still be populated, even in the case that the export transaction in the target Datasource is rolled back. |
| Record Export Status | If required, select an attribute from the input Stream from the drop down list. If an attribute is selected, when the exporter writes a record to the target Datasource the selected attribute in the input record will be updated with the export status (success or fail). If a record in the input Stream is not exported, this attribute will be left blank. If a record is exported - but the export is rolled back - the attribute will be updated, and set back to a blank record. Warning: if a record fails to export then this attribute will still be populated, even in the case that the export transaction in the target Datasource is rolled back. |
| Record Response | If required, select an attribute from the input Stream from the drop down list. If an attribute is selected, when the exporter writes a record to the target Datasource the selected attribute in the input record will be updated with any export error messages. If a record in the input Stream is not exported, this attribute will be left blank. If a record is exported - but the export is rolled back - the attribute will be updated, and set back to a blank record. Warning: if a record fails to export then this attribute will still be populated, even in the case that the export transaction in the target Datasource is rolled back. |
| Exporter Performance Settings | |
| Parallel Exporters | If required, you can specify that the exporter should use a number of parallel exporters. Each buffer of records read from the input pipe will be divided across the parallel exporters. The restriction applied to this is that if Group By fields are specified in the input Pipe to the exporter, all records with the same Group By key will be exported by the same exporter. (This is to help reduce contention in the target Datasource, writing to tables or calling APIs.) If no value is specified here then only a single exporter will be used. The Commit Size - also set on this tab - is per exporter i.e. each exporter will commit separately whenever it exports the number of records specified as the commit size |
| Errors Before Rollback | If required, you can set the maximum number of errors found, while exporting across all exporters, before the exporter will rollback all uncommitted transactions from all exporters. Up to this number of errors, any errors reported from the exporter will appear as warnings in the log entries. The export will not be considered failed unless the number of errors is above this threshold. |
| Commit Size | Tells the exporter to commit records in batches of the specified size. If no value is specified, all records will be committed as a single batch. |
| Errors Before Stopping | If required, you can set the maximum number of errors found, while exporting across all exporters, before the exporter will stop attempting to export records to the Datasource. This is useful where you have set Errors Before Rollback (also configured on this tab), but wish to continue to try to export records to get view of how successful the remaining export would have been. Any records that the exporter attempts to export after the Errors Before Rollback limit has been passed will always be rolled back, even if they are successful. If no value is entered, the exporter will stop when the Errors Before Rollback limit is reached. |
| Timeout (secs) | Tells the exporter to timeout each export operation after a number of seconds. If an export operation does time out, the operation will be abandoned and an error will be reported. Each record that is timed out is considered to be a single error when assessing whether the entire export should be stopped and/or rolled back - see Error Before Rollback below. If no value is specified, the duration of an export operation is unlimited. |
| Datasource Instance Expression | The target datasource for this exporter may list multiple Datasource Instances. Each datasource instance is identified by a unique string. This expression should evaluate to a string which allows the Database Exporter to determine the instance to use. If no value is entered then the exporter will assume that there is only one instance and will use that one by default. If there is more than one instance and no expression is provided here then an error will be thrown during analysis. |
| Description | |
| Description | Description of the database exporter. |
| Anchor | ||||
|---|---|---|---|---|
|
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 Stream - which is just a simple expression.
...
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 '}'.
...