A newer version of this documentation is available.

View Latest
February 16, 2025
+ 12

How to create and execute prepared statements, including placeholder parameters.
This guide is for Couchbase Server.

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 $ followed by the parameter name.

  • To add a positional parameter to a query, either enter a dollar 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.

To supply values for placeholder parameters:

  • If you are using the cbq shell, use the \SET command to set the parameters, then run the statement.

  • If you are using the SQL++ REST API, specify the parameters in the request body or the query URI, alongside the statement.

  • If you are using the Query Workbench, use the cog icon to display the Run-Time Preferences window and set the parameters, then run the statement.

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]; (1)(2)(3)

SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > $2;
1 In the cbq shell, parameter names must be prefixed by a hyphen.
2 In the cbq shell or the REST API, the args parameter specifies positional parameters.
3 The args parameter takes an array, each element of which is a value for a positional parameter.

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

\SET -$country "France"; (1)(2)
\SET -$altitude 500;

SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > $altitude;
1 In the cbq shell, parameter names must be prefixed by a hyphen.
2 In the cbq shell, the REST API, or the Query Workbench, named parameters must begin with a dollar sign $.

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

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.

To create a prepared statement, use the PREPARE statement.

  1. If necessary, set the query context to the bucket and scope where you want to create the prepared statement.

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

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.

To execute a prepared statement, use the EXECUTE statement.

  1. If necessary, set the query context to the bucket and scope where you created the prepared statement.

  2. Supply the name of the prepared statement, as provided when you created the prepared statement.

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

Overview:

Reference:

Querying with SDKs:

Prepared statements with SDKs: