3.4 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.

MySQL Shell and Node.js JavaScript Code

// 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();

When running this with Connector/Node.js be aware that execute() returns a Promise. You might want to check the results to avoid errors being lost.

MySQL Shell Python Code

# Collection.find() function with hardcoded values
myColl = db.get_collection('my_collection')

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

# Using the .bind() function to bind parameters
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()
myRes3 = myColl.find('name like :param').bind('param', 'R%').execute()

C# Code

// 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();

Python Code

# Collection.find() function with hardcoded values
my_coll = my_schema.get_collection('my_collection')

my_res_1 = my_coll.find('age = 18').execute()

# Using the .bind() function to bind parameters
my_res_2 = my_coll.find('name = :param1 AND age = :param2').bind('param1', 'Rohit').bind('param2', 18).execute()

# Using named parameters
my_coll.modify('name = :param').set('age', 55).bind('param', 'Nadya').execute()

# Binding works for all CRUD statements except add()
my_res_3 = my_coll.find('name like :param').bind('param', 'R%').execute()

Java Code

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

DocResult myRes1 = myColl.find("age = 18").execute();

// Using the .bind() function to bind parameters
DocResult 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();

// Using named parameters with a Map
Map<String, Object> params = new HashMap<>();
params.put("name", "Nadya");
myColl.modify("name = :name").set(".age", 55).bind(params).execute();

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

C++ Code

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

auto myRes1 = myColl.find("age = 18").execute();

// Using the .bind() function to bind parameters
auto 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()
auto 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() (or find() and Fields()) 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.

MySQL Shell JavaScript Code

// 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();
}

MySQL Shell Python Code

# one bind() per parameter
myColl = db.get_collection('relatives')
juniors = myColl.find('alias = "jr"').execute().fetch_all()

for junior in juniors:
  myColl.modify('name = :param'). \
    set('parent_name',mysqlx.expr(':param')). \
    bind('param', junior.name).execute()

Node.js JavaScript Code

// one bind() per parameter
db
  .getCollection('relatives');
  .find('alias = "jr"')
  .execute(function (junior) {
    return myColl
      .modify('name = :param')
      .set('parent_name', mysqlx.expr(':param'))
      .bind('param', junior.name)
      .execute();
  });

C# Code

// one bind() per parameter
myColl.Find("a = :param").Fields(":param as b")
  .Bind(new { param = "c"}).Execute();

Python Code

# one bind() per parameter
my_coll = my_schema.get_collection('relatives')
juniors = my_coll.find('alias = "jr"').execute().fetch_all()

for junior in juniors:
    my_coll.modify('name = :param') \
        .set('parent_name', mysqlx.expr(':param')) \
        .bind('param', junior.name).execute()

Java Code

# one bind() per parameter
Collection myColl = db.getCollection("relatives");
DocResult juniors = myColl.find("alias = 'Jr.'").execute();
while (juniors.hasNext()){
    myColl.modify("name = :param")
            .set("parent_name", Expression.expr(":param"))
            .bind("param", juniors.next().get("name"))
            .execute();
}

C++ Code

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

DbDoc junior;
while ((junior = juniors.fetchOne()))
{
  myColl.modify("name = :param")
        .set("parent_name", expr(":param"))
        .bind("param", junior["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.

MySQL Shell JavaScript Code

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();

MySQL Shell Python Code

myColl = db.get_collection('my_collection')

# Only prepare a Collection.remove() operation, but do not run it yet
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()
myFind = myColl.find('name like :param1 AND age > :param2')

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

Node.js JavaScript Code

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();

C# Code

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();

Python Code

my_coll = my_schema.get_collection('my_collection')

# Only prepare a Collection.remove() operation, but do not run it yet
my_remove = my_coll.remove('name = :param1 AND age = :param2')

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

# Binding works for all CRUD statements but add()
my_find = my_coll.find('name like :param1 AND age > :param2')

my_docs = my_find.bind('param1', 'L%').bind('param2', 20).execute()
my_other_docs = my_find.bind('param1', 'J%').bind('param2', 25).execute()

Java Code

Collection myColl = db.getCollection("my_collection");

// Create Collection.remove() operation, but do not run it yet
RemoveStatement 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()
FindStatement myFind = myColl.find("name LIKE :name AND age > :age");

Map<String, Object> params = new HashMap<>();
params.put("name", "L%");
params.put("age", 20);
DocResult myDocs = myFind.bind(params).execute();
params.put("name", "J%");
params.put("age", 25);
DocResult myOtherDocs = myFind.bind(params).execute();

C++ Code

Collection myColl = db.getCollection("my_collection");

// Create Collection.remove() operation, but do not run it yet
auto 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()
auto myFind = myColl.find("name like :param1 AND age > :param2");

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