Prepare Statements for Reuse

how-to

How to create and execute prepared statements, including placeholder parameters.

Introduction

If you need to execute certain SQL++ statements repeatedly, you can use placeholder parameters and prepared statements to optimize query reuse.

If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

Adding Placeholder Parameters

You can add placeholder parameters to a statement, so that you can safely supply variable values when you run the statement. You can add placeholder parameters in the WHERE clause, the LIMIT clause, or the OFFSET clause.

A placeholder parameter may be a named parameter or a positional parameter.

To add a named parameter to a query, enter a dollar sign $ or an at sign @ followed by the parameter name.
To add a positional parameter to a query, enter a dollar sign $ or an at sign @ followed by the number of the parameter, or enter a question mark ?.

The following example includes two named parameters.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;

To execute this query, the parameters must be supplied by name.

The following example includes two numbered positional parameters.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;

To execute this query, the parameters must be supplied as a list, in order of the placeholder numbers.

The following example includes two unnumbered positional parameters.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;

To execute this query, the parameters must be supplied as a list, in the order in which the placeholders appear in the statement.

Supplying Parameter Values

To run a query containing placeholder parameters, you must supply values for the parameters.

SQL++
.NET
Java
Node.js
Python

To supply values for placeholder parameters using the cbq shell:

Use the \SET command to set the parameters before running the statement.
Use the -args parameter to specify positional parameters.
Use -$ or -@ followed by a parameter name to specify named parameters.

To supply values for placeholder parameters using the SQL++ REST API:

Specify the parameters in the request body or the query URI, alongside the statement.
Use the args parameter to specify positional parameters.
Use $ or @ followed by a parameter name to specify named parameters.

To supply values for placeholder parameters using the Query Workbench:

Use the cog icon to display the Run-Time Preferences window before running the statement.
Use the Positional Parameters options to specify positional parameters.
Use the Named Parameters options to specify named parameters.

When you are executing a prepared statement, the EXECUTE statement provides another, easier way to supply parameter values. Refer to Executing a Prepared Statement below.

Context

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Queries

The following query supplies positional parameter values, using the cbq shell.

\SET -args ["France", 500];

SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;

The following query supplies named parameter values, using the cbq shell.

\SET -@country "France";
\SET -$altitude 500;

SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;

For more information and examples, refer to Settings and Parameters.

To supply values for placeholder parameters, use the Parameter method on the QueryOptions object.

There are different versions of the Parameter method for supplying a single named parameter, a collection of named parameters, a single positional parameter, or a list of positional parameters.

The following example supplies a single positional parameter.

Unresolved include directive in modules/guides/pages/prep-statements.adoc - include::dotnet-sdk:howtos:example$/n1ql-queries-sdk/n1ql-queries-sdk/Program.cs[]

The following example supplies a single named parameter.

Unresolved include directive in modules/guides/pages/prep-statements.adoc - include::dotnet-sdk:howtos:example$/n1ql-queries-sdk/n1ql-queries-sdk/Program.cs[]

For details, refer to QueryOptions.

To supply values for placeholder parameters, use the parameter method on the QueryOptions object.

There are different versions of the parameter method for supplying named parameters or positional parameters.

The following example supplies a single positional parameter.

QueryResult result = cluster.query(
    "select count(*) from `travel-sample`.inventory.airline where country = ?",
    queryOptions().parameters(JsonArray.from("France")));

The following example supplies a single named parameter.

QueryResult result = cluster.query(
    "select count(*) from `travel-sample`.inventory.airline where country = $country",
    queryOptions().parameters(JsonObject.create().put("country", "France")));

For details, refer to QueryOptions.

To supply values for placeholder parameters, use the parameters property on the QueryOptions interface.

The parameters property may be an object for supplying named parameters, or an array for supplying positional parameters.

The following example supplies a single positional parameter.

async function queryPlaceholders() {
  const query = `
  SELECT airportname, city FROM \`travel-sample\`.inventory.airport
  WHERE city=$1
  `;
  const options = { parameters: ['San Jose'] }

  try {
    let result = await cluster.query(query, options)
    console.log("Result:", result)
    return result
  } catch (error) {
    console.error('Query failed: ', error)
  }
}

The following example supplies a single named parameter.

async function queryNamed() {
  const query = `
    SELECT airportname, city FROM \`travel-sample\`.inventory.airport
    WHERE city=$CITY;
  `
  const options = { parameters: { CITY: 'Reno' } }

  try {
    let result = await cluster.query(query, options)
    console.log("Result:", result)
    return result
  } catch (error) {
    console.error('Query failed: ', error)
  }
}

For details, refer to QueryOptions.

To supply positional parameter values for a query or prepared statement, use the positional_parameters parameter in the QueryOptions.

To supply named parameter values for a query or prepared statement, use the named_parameters parameter in the QueryOptions.

Alternatively, you can supply positional parameters or named parameters as keyword arguments for the query() function.

The following examples supply a single positional parameter.

result = cluster.query(
    "SELECT ts.* FROM `travel-sample`.inventory.airport WHERE city=$1",
    QueryOptions(positional_parameters=["San Jose"]))

result = cluster.query(
    "SELECT ts.* FROM `travel-sample`.inventory.airport WHERE city=$1",
    "San Jose")

The following examples supply a single named parameter.

result = cluster.query(
    "SELECT ts.* FROM `travel-sample`.inventory.airport WHERE city=$city",
    QueryOptions(named_parameters={"city": "San Jose"}))

result = cluster.query(
    "SELECT ts.* FROM `travel-sample`.inventory.airport WHERE city=$city",
    city='San Jose')

For details, refer to QueryOptions.

Creating a Prepared Statement

If you need to run a statement more than once, you can prepare the execution plan for the statement and cache it for reuse.

You can include placeholder parameters in the prepared statement, if necessary.

SQL++
.NET
Java
Node.js
Python

To create a prepared statement, use the PREPARE statement.

If necessary, set the query context to the bucket and scope where you want to create the prepared statement.
Use the FROM / AS clause to specify a name for the prepared statement, if required. If you don’t, a name is generated automatically.

Context

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

The following query prepares the execution plan for the given statement, including the specified positional parameters.

PREPARE NumParam AS
SELECT * FROM hotel
WHERE city=$1 AND country=$2;

Result

"name": "[127.0.0.1:8091]NumParam", (1)

1	The query returns the name of the prepared statement.

Query

The following query prepares the execution plan for the given statement, including the specified named parameters.

PREPARE NameParam AS
SELECT * FROM hotel
WHERE city=$city AND country=$country;

Result

"name": "[127.0.0.1:8091]NameParam", (1)

1	The query returns the name of the prepared statement.

For more information and examples, refer to PREPARE.

To create a prepared statement, use the Cluster.QueryAsync<T> method with the adhoc query option set to false.

The following example executes a query with the specified parameters. If this query has not been executed before, the query plan is cached for reuse.

var result = await cluster.QueryAsync<dynamic>(
    "select count(*) from `travel-sample`.inventory.airport where country = ?",
    options =>
        options.Parameter("France")
        .AdHoc(false);
);

For details, refer to QueryOptions.

To create a prepared statement, use the query() method with the AdHoc query option set to false.

The following example executes a query with the specified parameters. If this query has not been executed before, the query plan is cached for reuse.

QueryResult result = cluster.query(
    "select count(*) from `travel-sample`.inventory.airport where country = ?",
    QueryOptions.queryOptions().adhoc(false).parameters(JsonArray.from("France"))
);

For details, refer to QueryOptions.

To create a prepared statement, use the query() function with the adhoc query option set to false.

The following example executes a query with the specified parameters. If this query has not been executed before, the query plan is cached for reuse.

async function queryNamed() {
  const query = `
  SELECT airportname, city FROM \`travel-sample\`.inventory.airport
  WHERE city=$1
  `;
  var options = { adhoc: false, parameters: ['London'] }

  try {
    let result = await cluster.query(query, options)
    console.log("Result:", result)
    return result
  } catch (error) {
    console.error('Query failed: ', error)
  }
}

For details, refer to QueryOptions.

To create a prepared statement, use the query() function with the adhoc query option set to false.

The following example executes a query with the specified parameters. If this query has not been executed before, the query plan is cached for reuse.

result = cluster.query(
    """SELECT airportname, city
    FROM \`travel-sample\`.inventory.airport
    WHERE city=$1;""",
    'London', QueryOptions(adhoc=false))

For details, refer to QueryOptions.

Executing a Prepared Statement

When you execute a prepared statement, the cached execution plan is reused, so the query executes faster.

You can supply parameter values for a prepared statement, just as you can for a query. These can be different to the parameter values that you supplied when you created the prepared statement.

SQL++
.NET
Java
Node.js
Python

To execute a prepared statement, use the EXECUTE statement.

If necessary, set the query context to the bucket and scope where you created the prepared statement.
Supply the name of the prepared statement, as provided when you created the prepared statement.
If necessary, use the USING clause to supply the values for parameters in the prepared statement.
- Specify positional parameters using an array of values.
- Specify named parameters using an object containing name / value properties.

Context

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Queries

The following query executes a prepared statement, including the specified positional parameters.

EXECUTE NumParam
USING ["Paris", "France"];

The following query executes a prepared statement, including the specified named parameters.

EXECUTE NameParam
USING {"city": "Paris", "country": "France"};

For more information and examples, refer to EXECUTE.

To execute a prepared statement, use the Cluster.QueryAsync<T> method to run the prepared statement query string again, with the adhoc query option set to false.

Specify parameter values for the query, if necessary.

The following example executes a query with the specified parameters. If a prepared statement has been created from this query previously, the cached query plan is reused.

var result = await cluster.QueryAsync<dynamic>(
    "select count(*) from `travel-sample`.inventory.airport where country = ?",
    options =>
        options.Parameter("France")
        .AdHoc(false);
);

For details, refer to QueryOptions.

To execute a prepared statement, use the query() method to run the prepared statement query string again, with the AdHoc query option set to false.

Specify parameter values for the query, if necessary.

The following example executes a query with the specified parameters. If a prepared statement has been created from this query previously, the cached query plan is reused.

QueryResult result = cluster.query(
    "select count(*) from `travel-sample`.inventory.airport where country = ?",
    QueryOptions.queryOptions().adhoc(false).parameters(JsonArray.from("France"))
);

For details, refer to QueryOptions.

To execute a prepared statement, use the query() function to run the prepared statement query string again, with the adhoc query option set to false.

Specify parameter values for the query, if necessary.

The following example executes a query with the specified parameters. If a prepared statement has been created from this query previously, the cached query plan is reused.

async function queryNamed() {
  const query = `
  SELECT airportname, city FROM \`travel-sample\`.inventory.airport
  WHERE city=$1
  `;
  var options = { adhoc: false, parameters: ['London'] }

  try {
    let result = await cluster.query(query, options)
    console.log("Result:", result)
    return result
  } catch (error) {
    console.error('Query failed: ', error)
  }
}

For details, refer to QueryOptions.

To execute a prepared statement, use the query() function to run the prepared statement query string again, with the adhoc query option set to false.

Specify parameter values for the query, if necessary.

The following example executes a query with the specified parameters. If a prepared statement has been created from this query previously, the cached query plan is reused.

result = cluster.query(
    """SELECT airportname, city
    FROM \`travel-sample\`.inventory.airport
    WHERE city=$1;""",
    'London', QueryOptions(adhoc=false))

For details, refer to QueryOptions.

Overview:

SQL++ Queries and Results

Reference:

Querying with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Prepared statements with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Prepare Statements for Reuse

Introduction

Adding Placeholder Parameters

Supplying Parameter Values

Creating a Prepared Statement

Executing a Prepared Statement

Related Links