EXECUTE

reference

The EXECUTE statement executes a prepared statement.

Syntax

execute ::= EXECUTE name [ USING parameters ]

name

The name of the prepared statement. This has the format [host:port]local-name-or-UUID, and may consist of:

Optionally, the host and port of the node where the prepared statement was created, in square brackets, followed by
The local name that you specified for the prepared statement, or a UUID that was assigned automatically.

If present, the host and port are used for Statement Retrieval.

If the name of the prepared statement contains hyphens, wrap the entire name in backticks (`) or double quotation marks (").

USING Clause

[Optional] The USING clause enables you to specify parameter values to use in the prepared statement.

parameters

The parameter values to use in the prepared statement. This may be:

An array of values, for positional parameters, or
An object containing name / value properties, for named parameters.

For more details, refer to Parameters below.

Usage

You can execute a prepared statement in three ways:

Using the EXECUTE statement in the Query Workbench.
Using the EXECUTE statement in the the cbq command line shell.
Using the Query REST API (/query/service endpoint).

Statement Retrieval

The query engine first looks for the prepared statement on the currently connected node, using the local name or UUID. If the prepared statement is not found on the currently connected node, the query engine attempts to retrieve it from the node specified in the prepared statement name. Once retrieved, the query engine creates a local cached copy of the prepared statement, and executes it.

An error is returned if the name does not identify a prepared statement.

Auto-Reprepare

Before execution, the query engine checks whether the statement plan is still valid — i.e. that all the indexes and keyspaces to which the plan refers are unchanged. If any indexes or keyspaces have changed, the statement is automatically prepared again, so that the plan matches the new set of resources.

If this automatic reprepare succeeds, the statement simply executes as expected. However, if any required resources are found to be missing, execution of the affected prepared statement fails until those resources are created again. Once the resources are available again, execution proceeds without any further intervention.

Parameters

A prepared statement may contain parameters. These are replaced by a supplied value when the statement is executed. Parameters may be named parameters or positional parameters.

Named parameters are specified by name when the prepared statement is executed. In N1QL, named parameters are specified using an object containing name / value properties.

EXECUTE NameParam
USING {"city":"paris", "country":"france"};

Positional parameters are specified by the position of each supplied parameter when the statement is executed. In N1QL, named parameters are specified using an array of values.

EXECUTE NumParam
USING ["paris", "france"];

Alternatively, you can specify named parameters and positional parameters using the N1QL REST API (/query/service endpoint), the cbq command line tool, or a software development kit (SDK). Named parameters can be specified as request-level parameters, and positional parameters can be specified using the args parameter. Refer to Query Settings for more information.

When you specify parameters with the USING clause, you cannot also specify parameters at the same time using the N1QL REST API, the cbq command line tool, or an SDK. When you do this, the query service returns error 5003: "cannot have both USING clause and request parameters".

Examples

Example 1. Executing a prepared statement in N1QL

The following example shows how to execute a prepared statement in N1QL.

Request

EXECUTE `[127.0.0.1:8091]24734829-c793-4b90-b8bd-74ff788be493`;

Example 2. Executing a prepared statement without a name using the REST API

The following example shows how to prepare the statement without specifying a name.

Request

$ curl -v http://localhost:8093/query/service -d 'statement=PREPARE SELECT text FROM tweets WHERE rating > $r AND created_at > $date'

Response

< HTTP/1.1 200 OK
{
  "requestID": "a339a496-7ed5-4625-9c64-0d7bf584a1bd",
  "signature": "json",
  "results": [
  {  "encoded_plan": "H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==",
    "name": "a1355198-2576-4e3d-af04-5acc77d8a681",
     "operator": {
            "#operator": "Sequence",
            "~children": [
            // Content redacted
            ]
            },
     "signature": {
     "text": "json"
  },
  "text": "PREPARE SELECT text FROM tweets WHERE rating > $r AND created_at > $date"
  }
  ],
  "status": "success",
  "metrics": {
  "elapsedTime": "1.970679ms",
  "executionTime": "1.889351ms",
  "resultCount": 1,
  "resultSize": 2261
 }
}

The following example uses the server-generated name of the prepared statement to execute the statement.

Request:

$ curl -v http://localhost:8093/query/service -d 'prepared="a1355198-2576-4e3d-af04-5acc77d8a681"&$r=9.5&$date="1-1-2014"'

Response:

< HTTP/1.1 200 OK
{
 "requestID": "1bd9956b-bc8e-478a-bd84-3955fe2db047",
 "signature": {
 "text": "json"
 },
 "results": [
 {
  "text": "Couchbase is my favorite database"
 }
 ],
 "status": "success",
 "metrics": {
 "elapsedTime": "1.527795ms",
 "executionTime": "1.443748ms",
 "resultCount": 0,
 "resultSize": 0
 }
}

Example 3. Executing a prepared statement with a name using the REST API

The following example specifies a name for the prepared statement.

Request:

$ curl -v http://localhost:8093/query/service -d 'statement=PREPARE fave_tweets FROM SELECT text FROM tweets WHERE rating >= $r'

Response:

< HTTP/1.1 200 OK
 {
        "requestID": "a339a496-7ed5-4625-9c64-0d7bf584a1bd",
        "signature": "json",
        "results": [
        {   "encoded_plan": "H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==",
        "name": "fave_tweets",
        "operator": {
        // and so on
        ...

The following example uses the name specified in the example above to run the prepared statement.

Request:

$ curl -v http://localhost:8093/query/service -d 'prepared="fave_tweets"&$r=9.5'

Response

< HTTP/1.1 200 OK
{
 "requestID": "1bd9956b-bc8e-478a-bd84-3955fe2db047",
 "signature": {
 "text": "json"
 },
 "results": [
 {
   "text": "Couchbase is my favorite database"
  }
  ],
 "status": "success",
 "metrics": {
 "elapsedTime": "1.527795ms",
 "executionTime": "1.443748ms",
 "resultCount": 0,
 "resultSize": 0
 }
 }

For information on preparing a statement for execution, refer to PREPARE.

EXECUTE

Syntax

USING Clause

Usage

Statement Retrieval

Auto-Reprepare

Parameters

Examples

Related