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, if you want to specify a variable for the first column returned, you would specify the following SQL statement:

SELECT DEMO.ITEM.ITEM_ID FROM DEMO.ITEM
   WHERE DEMO.ITEM.ITEM_ID = ?

The ? replaces the value in the WHERE clause, and you can then provide input to the SQL statement from process data on the Input tab.

Note: Editing the SQL statement disables the Design tab of the Query Designer wizard. Make sure you have designed the query you need before editing it and inserting question marks for process variables. If your question marks are in the WHERE clause, you can put those conditions into the Design tab in the Where fields of each column. This allows you to keep the Design tab available and still have process variables in your query.
Keep in mind, ? is not valid SQL syntax, so checking the syntax of a query that contains question marks can return an invalid character error in the Query Designer wizard.
Note: With MySQL database, Query Designer wizard does not populate the table structure in the JDBC Query palette.

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 on the Input tab.

Note: Any mappings or expressions you create on the Input tab are not updated when you move or delete prepared parameters. You must manually update the Input tab after changing the prepared parameter field.