Activation

Main settings

You can activate the execution of phase 4 in the "Main settings" with checkbox "Activate phase 4".

Phase 2/Extended

Further settings (checkboxes) for phase 4 can be found in "Phase 2/Extended".

Trim SQL values in phase 4	Before writing values to the database, they can be trimmed.
Continue with next record on SQL error	See section Checkbox "Continue with new record on SQL error".

Description

When mapping the data, SQL statements can be generated in parallel. These SQL statements will be executed after the completion of phase 3, provided phase 4 is activated.

To do this, a column name of the database must be assigned to a target structure field. Section "Importing structure via database nodes" describes how a database node in the target structure of the mapping can be created from an existing database table.

Target fields with assigned column names must be below a node that has a database table and a database alias associated with it. As a result, different nodes can be associated with different databases. The contents of fields in nodes with assigned database table names are not written to the database if no column names are entered in the field. Fields of data type BLOB are transferred as a byte array.

Database alias and table name can also be specified with variables. But a node is always written to the same table and the same database within a job. So 'spreading' the data to different databases and tables within a job is not possible that way.

Attributes for nodes in target structure

(1) DB alias: The database alias for this node. The alias can be set with a variable. However, the value must be static within a job. After completion of phase 3, the value is read and then used for all repeats of the node in phase 4.

(2) SQL table/procedure: The name of the table to write into, or the command to call a stored procedure.

(3) Empty table: If this attribute is set to Yes, the table content is deleted at the beginning of phase 4. Important note: The deletion is also carried out if the node is not entered during the mapping, but only if phase 4 is activated and the node is active (see section above). Note: A DELETE is executed (automatic rollback in case of error).

(4) Opt. Pre-SQL execution: See section "Additional SQL statement at start of transaction".

(5) SQL mode: The following modes are possible.

insert	An attempt is made to add the dataset to the existing datasets. The field attribute SQL key will have no relevance.
delete before insert	The SQL key attribute (which internally generates the WHERE condition) is used to find and delete the matching datasets and then the INSERT of the current dataset is executed.
try update before insert	The SQL key attribute (which internally generates the WHERE condition) is used to decide whether there are matching records in the table. Then the UPDATE is executed. If a dataset did not yet exist in the table, an INSERT with the corresponding values is executed instead.
only update	An attempt is made to update the values of the non-key fields in the datasets identified by the SQL key attribute (generates the WHERE condition internally). If no dataset matches these key values, the statement has no effect. The key fields themselves are never changed with an UPDATE.
only delete	An attempt is made to delete the records identified by the SQL key attribute (which internally generates the WHERE condition) from the table. If no dataset matches these key values, the statement has no effect
Stored Procedure	See section "Calling a stored procedure".

Note: Normally, the fields that are defined as primary keys in the database table will also receive the SQL key attribute in the corresponding target structure field, but that is not mandatory. When importing a database node, the key property is also not set automatically. It always has to be set manually.

(6) Transaction control: See section "Transaction control".

(7) Only for SQL: If this option is set, the node and its children will be removed from the target tree at the end of phase 4. The node is also removed if no SQL statements were sent to the database. This option, therefore, has the same effect for a target node as the attribute "Calculation field" for a target structure field. For the subsequent phase 5 and phase 6, the target tree looks as if the node were not present. But those nodes are shown in a mapping test. See also section "Pruning the (partial) target tree". In the structure, this is represented by a database icon.

Attributes for fields in target structure

(8) SQL column name: The name of the column in the database table associated with this target field. Note: Please note that calculation fields are also used for phase 4. If you do not want to use a calculation field for phase 4, please remove the column name.

(9) SQL key: Indicates whether the table column is a primary key in the database. The fields with value Yes are used in UPDATE and DELETE statements. See also (10).

(10) SQL AutoGenKey: If this option is set, you mark that the field in the database is an autoincrement field, i.e. the database manages the value setting itself, see also (9). Fields of this type are not handled until phase 4 during SQL execution, so do not map any other fields to this field and do not use any functions on it. There is only one way to use such fields in phase 3. If you have a subsequent second SQL node in the mapping and want to use the automatically generated value from the first SQL node there as a foreign key, you can copy the value of the AutoGenKey field to the foreign key field using the copy function. But this will also be executed in phase 4 . Note: If you want to use the AutoGenKey construct in several database nodes, this is possible, but only if the fields concerned do not have the same name neither in the target structure, nor in the database, otherwise this will generate an error.

(11) Skip empty SQL value: See section "Writing NULLs and skipping columns".

Notes

The order of the fields under a database node does not necessarily have to match the order of the columns in the database table because the fields are addressed via the entered SQL column name.

If fields are marked as SQL key (9), the values in these fields are used in updates to decide which records to update. Only the values of the columns whose assigned target fields are not marked as SQL keys are then updated in the appropriate datasets.

There only is a guarantee that only a single dataset will be updated in the database if the fields identified as SQL keys really correspond to a UNIQUE KEY or PRIMARY KEY of the database table. In this case, the attribute "Mandatory field" should be set for all those key fields.