...
A Database Exporter allows data held in a Stream to be written to a Datasource.
For an overview of how Database Exporters can be used when creating models, see Exporting Data to a Database.
Form: Database Exporter Details
...
| Field | Description |
|---|---|
| Details | The main details required for Database Exporter configuration. |
| href="#queryString">Query Query String | The query string used to write Stream data into the Datasource. |
| href="#advanced">AdvancedAdvanced | Details for advanced Database Exporter configuration - in many cases, you do not need to enter anything in this tab. |
| href="#inputMultiplier">Input Input Multiplier | An input multiplier expression. |
| Description | A free text field for you to enter a description of the Database Exporter. |
...
Note that when exporting a stream 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(aTrueFalseStreamAttribute,'T','F')}
Mapping to a single numeric database column - {if(aTrueFalseStreamAttribute,1,0)}
...
As for a plain SQL query, you can write PL/SQL just as you would to run against the database directly - but putting any CenterView expressions in curly braces i.e. starting with a '{' and ending with '}'.
| Code Block |
|---|
...
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 stream.
...
| Field | Description |
|---|---|
| Input Multiplier Expression | 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. |