Using Custom Bind Variables for View Criteria Used Multiple Times
Often you may need to execute the same view object multiple times within the same script.
If your operand values change from query execution to query execution, then named bind variables allow you to append a view criteria once, and use it many times with different values for the criteria expression operands. Just add one or more named bind variables to your view object, and then set the values of these bind variables as appropriate before each execution. The bind variables act as "live" placeholders in the appended filter expression, and their current values are used each time the view object's query is executed.
To add a named bind variable, use the addBindVariable()
function. Pass a
view object or rowset as the first argument and a string value to define the name of the bind
variable as the second argument as shown in the example below. You can name your bind variable
using any combination of letters, numbers, and underscores, as long as the name starts with a
letter. When using a view criteria with a standard object like the
StaffMember
in this example, it is best practice to name your bind
variables with the same suffix (_c
) as custom objects and custom fields use.
def vo = newView("StaffMember")
addBindVariable(vo,"VarLastName_c")
setBindVariable(vo,"VarLastName_c","King")
vo.appendViewCriteria("LastName = :VarLastName_c")
vo.executeQuery()
while (vo.hasNext()) {
def r = vo.next();
// Will return "Steven King" and "Janette King"
}
setBindVariable(vo,"VarLastName_c","Higgins")
vo.executeQuery()
while (vo.hasNext()) {
def r = vo.next();
// Will return "Shelley Higgins"
}
You can reference a named bind variable in the view criteria expression anywhere a literal
value can be used, prefacing its name by a colon (e.g. :VarLastName_c
). After
adding the bind variable, you use the setBindVariable()
function one or more
times in your script to assign values to the variable. Until you explicitly set its value for
the current view object or rowset, your bind variable defaults to having a value of
null
. Accidentally leaving the value null
will result in
retrieving no rows for most filter expressions involving a bind variable operand due to how
the SQL language treats the null
value in comparisons. The current value of
the bind variable is used each time your script executes the view object. In the example
below, this causes the rows for employees "Steven King" and "Janette King" to be returned
during the first view object execution, and the row for "Shelly Higgins" to be returned on the
second view object execution.
By default, the data type of the named bind variable is of type Text
. If you
need to use a bind variable in filter expressions involving number, date, or datetime fields,
then you need to explicitly define a bind variable with the appropriate type for best
performance. To add a bind variable of a specific datatype, pass one of the values
Text
, Number
, Date
, or
Datetime
as a string value to the optional third argument of the
addBindVariable()
function. For example, the following script uses two bind
variables of type Number
and another of type Date
. Notice
that the data type name is not case-sensitive (e.g. Number
,
number
, or NUMBER
are all allowed).
def vo = newView('TroubleTicket_c')
addBindVariable(vo,"VarLowPri_c","number")
addBindVariable(vo,"VarHighPri_c","Number")
addBindVariable(vo,"VarDueDate_c","DATE")
setBindVariable(vo, "VarLowPri_c", 1)
setBindVariable(vo, "VarDueDate_c", 2)
setBindVariable(vo, "VarDueDate_c", today() + 3)
vo.appendViewCriteria("(Priority_c between :VarLowPri_c and :VarHighPri_c) and DueDate < :VarDueDate_c ")
vo.executeQuery()
while (vo.hasNext()) {
def row = vo.next()
// Returns trouble tickets with priorities 1 and 2 that are
// due within three days from today
}
setBindVariable(vo, "VarLowPri_c", 3)
setBindVariable(vo, "VarDueDate_c", 4)
setBindVariable(vo, "VarDueDate_c", today() + 5)
vo.executeQuery()
while (vo.hasNext()) {
def row = vo.next()
// Returns trouble tickets with priorities 3 and 4 that are
// due within five days from today
}