Parameters in stored queries
A stored query is a query that is stored in the database and identified by a name. The qualifying tuples are assembled dynamically when the query is run. To make stored queries reusable, you can use parameters in the query definition that are resolved at run time.
For example, you have defined custom properties to store customer
names. You can define queries to return the tasks that are associated
with a particular customer, ACME Co. To query this information, the where clause
in your query might look similar to the following example:
String whereClause =
"TASK.STATE = TASK.STATE.STATE_READY
AND WORK_ITEM.REASON = WORK_ITEM.REASON.REASON_POTENTIAL_OWNER
AND TASK_CPROP.NAME = 'company' AND TASK_CPROP.STRING_VALUE = 'ACME Co.'";To make this query reusable so that you can also search for the
customer, BCME Ltd, you can use parameters for the values of the custom
property. If you add parameters to the task query, it might look similar
to the following example:
String whereClause =
"TASK.STATE = TASK.STATE.STATE_READY
AND WORK_ITEM.REASON = WORK_ITEM.REASON.REASON_POTENTIAL_OWNER
AND TASK_CPROP.NAME = 'company' AND TASK_CPROP.STRING_VALUE = '@param1'";The @param1 parameter is resolved at run time
from the list of parameters that is passed to the query method.
The following rules apply to the use of parameters in queries:
- Parameters can only be used in the where clause.
- Parameters are strings.
- Parameters are replaced at run time using string replacement. If you need special characters you must specify these in the where clause or passed-in at run time as part of the parameter.
- Parameter names consist of the string @param concatenated with an integer number. The lowest number is 1, which points to the first item in the list of parameters that is passed to the query API at run time.
- A parameter can be used multiple times within a where clause; all occurrences of the parameter are replaced by the same value.