3.3 Parameter Binding

Instead of using values directly in an expression string it is good practice to separate values from the expression string. This is done using parameters in the expression string and the bind() function to bind values to the parameters.

Parameters can be specified in the following ways: anonymous and named.

Parameter Type	Syntax	Example	Allowed in CRUD operations	Allowed in SQL strings
Anonymous	?	'age > ?'	no	yes
Named	:<name>	'age > :age'	yes	no

The following example shows how to use the bind() function before an execute() function. For each named parameter, provide an argument to bind() that contains the parameter name and its value. The order in which the parameter value pairs are passed to bind() is of no importance. The example assumes that the test schema has been assigned to the variable db and that the collection my_collection exists.

// Collection.find() function with fixed values
var myColl = db.getCollection('my_collection');

var myRes1 = myColl.find('age = 18').execute();

// Using the .bind() function to bind parameters
var myRes2 = myColl.find('name = :param1 AND age = :param2').bind('param1','Rohit').bind('param2', 18).execute();

// Using named parameters
myColl.modify('name = :param').set('age', 55).
        bind('param', 'Nadya').execute();

// Binding works for all CRUD statements except add()
var myRes3 = myColl.find('name like :param').
        bind('param', 'R%').execute();

Anonymous placeholders are not supported in X DevAPI. This restriction improves code clarity in CRUD command chains with multiple methods using placeholders. Regardless of the bind() syntax variant used there is always a clear association between parameters and placeholders based on the parameter name.

All methods of a CRUD command chain form one namespace for placeholders. In the following example, modify() and set() are chained. Both methods take an expression with placeholders. The placeholders refer to one combined namespace. Both use one placeholder called :param. A single call to bind() with one name value parameter for :param is used to assign a placeholder value to both occurrences of :param in the chained methods.

// one bind() per parameter
var myColl = db.getCollection('relatives');
var juniors = myColl.find('alias = "jr"').execute().fetchAll();

for (var index in juniors){
  myColl.modify('name = :param').
    set('parent_name',mysqlx.expr(':param')).
    bind('param', juniors[index].name).execute();
}

It is not permitted for a named parameter to use a name that starts with a digit. For example, :1one and :1 are not allowed.

Preparing CRUD Statements

Instead of directly binding and executing CRUD operations with bind() and execute() or execute() it is also possible to store the CRUD operation object in a variable for later execution.

The advantage of doing so is to be able to bind several sets of variables to the parameters defined in the expression strings and therefore get better performance when executing a large number of similar operations. The example assumes that the test schema has been assigned to the variable db and that the collection my_collection exists.

var myColl = db.getCollection('my_collection');

// Only prepare a Collection.remove() operation, but do not run it yet
var myRemove = myColl.remove('name = :param1 AND age = :param2');

// Binding parameters to the prepared function and .execute()
myRemove.bind('param1', 'Leon').bind('param2', 39).execute();
myRemove.bind('param1', 'Johannes').bind('param2', 28).execute();

// Binding works for all CRUD statements but add()
var myFind = myColl.find('name like :param1 AND age > :param2');

var myDocs = myFind.bind('param1', 'L%').bind('param2', 20).execute();
var MyOtherDocs = myFind.bind('param1', 'J%').bind('param2', 25).execute();