All kinds of user-defined SQL statements can be executed using the SQL Heater, both on the READ side (source) and the WRITE side (target).
Examples of use
- The SQL Heater can serve as a substitute for the Database Lookup Heater.
- Processed records on the READ side can be updated to mark them as such.
- After processing, records on the READ side can be deleted.
Note: No incoming data is needed for the SQL Heater. However, to form dynamic SQL statements based on the data stream, you can employ placeholder parameters for temporary storage.
Settings
SQL Heater - Configuration Execute on InMemory Adapter table: When this option is checked the query is performed on an existing InMemory Adapter table. Note: The table must have been created and filled with values in a previous processing step of the same Definition. As InMemory Adapter tables are processed exclusively in main memory, a significant speed improvement is attained.
Selecting the options for “Execute when” and “Which side” enable you to determine when and for which database the SQL statement is executed.
Execute when: The following options are available.
Per row/record: The SQL statement is executed before each record is processed
Start: The SQL statement is executed before commencing the Definition run
Finish: The SQL statement is executed after the Definition run has completed
Which side: This determines whether the SQL statement is executed on the READ or WRITE side.
Adapter: When several Adapters are available in the Definition you can select here which Adapter (data source) to run the database lookup against. By default the first Adapter available will be used.
SQL: Enter here the SQL statement to execute. Elements of the SQL statement can include placeholder parameters.
e.g. select id from product_table where product_name = ‘$PRODUCT_NAME$’
You can use the button “Reveal effective SQL statement after parameter substitution” to test how the current parameter values affect the SQL statement. Note: This may require you assign default values for the parameters used.
Inhibit execution in Test Mode: This option prevents the SQL from being executed in Test Mode.
Output return value: If the SQL statement returns a value, for example “id” from the database select in the example above, you can check this option to enable the value to be used as output from the SQL Heater for further processing in FlowHeater (equivalent to the Database Lookup Heater).
Store return value in parameter: This option permits you to select an alternative or additional storage of the return value of the SQL statement in a parameter. Note: If you specify “Start” or “Finish” for the option “Execute when” the return value is not available as an output of the SQL Heater. In order to obtain the value you have to save the value in a parameter. The value delivered can then be accessed for further processing using the Parameter Heater or by reference to a parameter placeholder.
Default value: If you check the option “Return this value when no result is obtained” you will then be permitted to enter a default value to specify what will result when the database lookup finds nothing.
Please also refer to the general information on the use of Heaters (functions)
