Using context and globalMap variables in SQL queries
It is possible to use parameters passed or created in a job to drive the results of a SQL query. This short recipe shows how.
Getting ready
Open the job jo_cook_ch07_0020_contextInQuery, which is a copy of the job from the previous recipe.
How to do it…
The steps to be performed are as follows:
Open the
tMysqlInputcomponent.Change the final line to
HAVING SUM(o.`orderTotal`) > "+context.minOrderValue.Run the job and you will be prompted for a value.
Enter
10and click on Ok.You should see that the result set contains orders where the sum of the lines is > 10.
How it works…
The SQL statement used by tMysqlInput is held in a string, so can be manipulated just as any other string in Java. Thus, we can alter the value of the string using normal Java conventions; in this case a concatenate (+).
The statement is thus translated at runtime and the value of the context substituted into the SQL query, which returns the customized resultset.
