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 positional parameter values using the Query Workbench.

    1. Click the cog icon to display the Run-Time Preferences 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 Preferences.

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

    1. Click the cog icon to display the Run-Time Preferences window.

    2. Next to Named Parameters, click +.

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

    4. Next to Named Parameters, click + again.

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

    6. Choose Save Preferences.

    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.

    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.

    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.

    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.

    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.

    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:

    Reference:

    Querying with SDKs:

    Prepared statements with SDKs: