Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  CRUD Operations  /  Parameter Binding

Pre-General Availability Draft: 2017-07-18

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 = 15').execute();

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

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

// Binding works for all CRUD statements except add()
var myRes3 = myColl.find('name like :param').
        bind('param', 'J%').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 = 15').execute()

# Using the .bind() function to bind parameters
myRes2 = myColl.find('name = :param1 AND age = :param2').bind('param1','jack').bind('param2', 17).execute()

# Using named parameters
myColl.modify('name = :param').set('age', 37).bind('param', 'clare').execute()

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

C# Code

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

var myRes1 = myColl.Find("age = 15").Execute();

// Using the .bind() function to bind parameters
var myRes2 = myColl.Find("name = :param1 AND age = :param2").Bind("param1", "jack").Bind("param2", 17).Execute();

// Using named parameters
myColl.Modify("name = :param").Set("age", 37)
  .Bind("param", "clare").Execute();

// Binding works for all CRUD statements except add()
var myRes3 = myColl.Find("name like :param")
  .Bind("param", "J%").Execute();

Java Code

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

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

// Using the .bind() function to bind parameters
DocResult myRes2 = myColl.find("name = :param1 AND age = :param2").bind("param1", "jack").bind("param2", 17).execute();

// Using named parameters
myColl.modify("name = :param").set("age", 37)
  .bind("param", "clare").execute();

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

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

C++ Code

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

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

// Using the .bind() function to bind parameters
auto myRes2 = myColl.find("name = :param1 AND age = :param2")
                    .bind("param1","jack").bind("param2", 17)
                    .execute();

// Using named parameters
myColl.modify("name = :param").set("age", 37)
      .bind("param", "clare").execute();

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

Anonymous placeholders are not supported in the 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 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 occurences of :param in find() and fields().

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
var myColl = db.getCollection('relatives');
myColl.find('alias = "jr"').execute(function (junior) {
  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();

Java Code

// one bind() per parameter
myColl.find("a = :param").fields(":param as b")
  .bind("param", "c").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', 'mike').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', 'S%').bind('param2', 18).execute();
var MyOtherDocs = myFind.bind('param1', 'M%').bind('param2', 24).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', 'mike').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', 'S%').bind('param2', 18).execute()
MyOtherDocs = myFind.bind('param1', 'M%').bind('param2', 24).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', 'mike').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', 'S%').bind('param2', 18).execute();
var MyOtherDocs = myFind.bind('param1', 'M%').bind('param2', 24).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", "mike").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", "S%").Bind("param2", 18).Execute();
var MyOtherDocs = myFind.Bind("param1", "M%").Bind("param2", 24).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", "mike").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", "S%");
params.put("age", 18);
DocResult myDocs = myFind.bind(params).execute();
params.put("name", "M%");
params.put("age", 24);
DocResult myOtherDocs = myFind.bind(params).execute();

C++ Code

// Ths functionality is not yet implemented in Connector/C++. At the
// moment a crud operation can be executed only once, with one set
// of parameter values.

User Comments
Sign Up Login You must be logged in to post a comment.