Log In    |    Knowledge Base    |    4D Home
Tech Tip: Use parameters object in ORDA query() to supply a collection of placeholders dynamically
Published On: December 13, 2018

The new dataClass.query() method in V17 ORDA allows developer to build multiple queries into on single strings. The values to match can be passed as subsequent parameters of query() method. For example:

$entitySelection:=ds.People.query("(firstName = :1 or firstName = :2) and (lastName = :3 or lastName = :4)"; "D@";"R@";"S@";"K@")

This has provided flexibilities for building queries especially programmatically. However, the parameters passed to query() method are separated in 4 parameters. The number of parameters has to strictly match the number of placeholders used in query string or query() method will report an error.

In fact, query() method can be executed with only two parameters: query string, and params object. The params object has three properties: queryPlan, queryPath and parameters. The first two porperties are both Boolean values used to enable/disable queryPlan and queryPath. The parameter property is an collection storing all placeholders for the queries string. For example, this query is equivalent to the query above:

$param:= New object
$param.parameters:= New collection ("D@";"R@";"S@";"K@")
$entitySelection:=ds.People.query("(firstName = :1 or firstName = :2) and (lastName = :3 or lastName = :4)"; $param)

The elements of $param.parameters can be any type supported by placeholder, including a collection used with IN statement. For example, the above queries can be replaced with the code below:

$param:=New object
$firstNameCol:=New collection("D@";"R@")
$lastNameCol:=New collection("S@";"K@")
$param.parameters:=New collection($firstNameCol;$lastNameCol)
$entitySelection:=ds.People.query("firstName in :1 and lastName in :2"; $param)

If we combine all code constructing $param object into the query method, the final version of the query() can be simplified as:

$entitySelection:=ds.People.query("firstName in :1 and lastName in :2"; New object("parameters":;New collection(New collection("D@";"R@");New collection("S@";"K@"))))

In this way, the query() method always take two parameters no matter how many of placeholders need to be passed to the query. It makes building dynamic query string and parameters much easier.