Prepare Statements for Reuse
- Capella Operational
- 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 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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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 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.
|
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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.
-
Click Query Options to display the Query Options window.
-
Next to Positional Parameters, click +.
-
In the $1 box, enter
"France"
. -
Next to Positional Parameters, click + again.
-
In the $2 box, enter
500
. -
Choose Save.
-
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.
-
Click Query Options to display the Query Options window.
-
Next to Named Parameters, click +.
-
In the name 0 box, enter
country
, and in the value 0 box, enter"France"
. -
Next to Named Parameters, click + again.
-
In the name 1 box, enter
altitude
, and in the value 1 box, enter500
. -
Choose Save.
-
Run the following query.
SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;
For more information and examples, refer to Configure Queries.
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.
var result = await cluster.QueryAsync<dynamic>(
"SELECT t.* FROM `travel-sample` t WHERE t.type=$1",
options => options.Parameter("landmark")
);
The following example supplies a single named parameter.
var result = await cluster.QueryAsync<dynamic>(
"SELECT t.* FROM `travel-sample` t WHERE t.type=$type",
options => options.Parameter("type", "landmark")
);
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.
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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;
"name": "[127.0.0.1:8091]NumParam", (1)
1 | The query returns the name of the prepared statement. |
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;
"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.
-
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
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.