DB (Input Agent Cron)
Introduction: You can find a description of this phase in section Phase 1 (Introduction).
The input data is read from a database using an SQL query.
If the Input Agent starts a job, it will process all the datasets that the SQL query returns. The result set of the database query is stored in CSV format as a backup file. See also document type Database.
(1) Alias for the connection to the database. A database alias stands for a configured connection to a database system (see configuration file ./etc/database.xml and section DatabaseService). In addition, all variables with prefix MSG_CALL_ defined in the profile are available for selection. This makes it possible to pass the alias from a triggering profile or with an HTTP call. Note: For easy testability, the variables should be defined with a default value.
(2) SQL query to read the data from the database. Only a single SELECT statement can be specified. Note: See also system variable VAR_TRIGGER_VALUE. Note: You can also use variables in the query, for example select * from @MSG_CALL_var__myTableName@ where company=@1:s@. Note: See also the placeholder for the last run . Note: Comments can only be specified with the syntax /*comment*/, otherwise an error will occur.
(3) Tests the SQL statement in (2). If the SQL statement contains a * for the names of the columns, the actual names of the columns are read from the database and entered in (2).
(4) The columns of the SQL statement are created as fields below a new node in the source structure. Existing mapping instructions are retained. The data types and field lengths of the columns are taken from the database definition.
(5) See (6).
(6) It is possible to access variable values in the WHERE clause of the SQL query. For this, the variable name is put in @ characters. Example: Parameter value @MSG_CALL_PARAM1@ (see section HTTP Request Parameters) is assigned to parameter number 1. In (2), the string variable can then be accessed with WHERE ID = @1:s@. Details are explained in section DefaultSQLCron (SQLCron). Since the evaluation of the variables takes place before phase 3 (mapping), only variables that are passed on by a calling profile or an external trigger make sense here. If a variable is specified that was not declared in this profile, the placeholder will not be resolved.
(7) Causes all read data of the table to be deleted. If isolation level Read Committed is set, data can simultaneously be inserted by another program. Attention: This checkbox should only be used with extreme caution. If the number of deleted lines is different from the number of lines read, no job is started and an error is generated.
A WHERE condition can be used to optimise the deletion process. The WHERE condition from (2) is used with the same parameters. This is especially important if the data types do not match exactly.
(8) If this checkbox is set, the content of BLOB data fields (binary data) will be encoded with Base64 before passing to the profile. Note: Otherwise, binary data may cause an error when the source tree is created. Read-in fields can be decoded with the function decode Base64(a, b).
(9) If this option is selected, data will be supplied to the profile even if the SQL query does not generate a result set. The specified replacement values will then be transferred to the profile. The data must be in CSV format (delimiter character comma).