December 15, 2024
+ 12
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 Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy 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.

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 Query tab:

  • Click Query Options to display the Query Options 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 positional parameter values using the Query tab.

  1. Click Query Options to display the Query Options window.

  2. Next to Positional Parameters, click +.

  3. In the $1 box, enter "France".

  4. Next to Positional Parameters, click + again.

  5. In the $2 box, enter 500.

  6. Choose Save.

  7. Run the following query.

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;

The following query supplies named parameter values using the Query tab.

  1. Click Query Options to display the Query Options window.

  2. Next to Named Parameters, click +.

  3. In the name 0 box, enter country, and in the value 0 box, enter "France".

  4. Next to Named Parameters, click + again.

  5. In the name 1 box, enter altitude, and in the value 1 box, enter 500.

  6. Choose Save.

  7. Run the following query.

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

For more information and examples, refer to Configure Queries.

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: