Prepared Parameters
If you want to replace static names in the SQL statement with process variables, you must edit the SQL statement field and replace the variable items with a question mark (?). For example, you might specify the following statement:
UPDATE emp SET ename = ?, phone = ?
WHERE id = ?
The ?
replaces the value, and you can then provide input to the SQL statement from process data on the input tab.
When you add or remove question marks in SQL statements, a warning appears to the right of the Prepared Parameters field ("SQL/ Prepared Parameter Count Mismatch") to alert you to add or remove corresponding prepared parameters. Each prepared parameter corresponds to the question mark in the same position in the SQL statement. That is, the first prepared parameter in the list corresponds to the first question mark, the second prepared parameter in the list corresponds to the second question mark, and so on. The warning is for informational purposes only, you must make sure the parameters in this field correctly correspond to the SQL statement.
You can optionally specify names for each prepared parameter. By default, the prepared parameters are named Prepared_Param_1
, Prepared_Param_2
, and so on.
You must supply the datatype of each parameter to the SQL statement, and this datatype is used in the input schema for the statement. You can use the up or down arrow keys to move a specified datatype to the previous or next parameter. You can also select a contiguous set of parameters to move them up or down, or delete them with the delete button.
At run time, any parameters you place in the SQL statement are replaced with the mapped values for those parameters.
For INSERT and DELETE statements, all prepared parameters appear as required items in the Input tab. For UPDATE statements, all prepared parameters appear as optional in the Input tab, but you must supply a value for at least one column in the statement or a runtime error occurs.