A newer version of this documentation is available.

View Latest

PREPARE statement [Beta]

PREPARE statement

[This is a beta feature; it’s not for use in production.]

prepare:

 PREPARE [ name ( FROM | AS ) ] statement

The PREPARE statement prepares a query. A name can optionally be specified. A JSON array is returned that contains one result that has the properties:

  • name: If present, this will be the name given to the PREPARE statement. If not, a name is generated.

  • operator: the execution plan of the statement being prepared.

  • signature: the signature of the statement being prepared.

  • text: the full PREPARE statement text.

  • encoded_plan: the full prepared statement in encoded format. The encoded plan can be included in addition to the prepared parameter in a request to execute a prepared statement. If the name given by the prepared parameter is unrecognized, the query engine will decode and execute the prepared statement given by the encoded_plan parameter. The decoded prepared statement will be saved by the query engine so the next request that uses its name will succeed.

Repeatedly preparing a statement with the same name will result in the statement being overwritten. A prepare statement that uses a name that was used for a different statement will result in a duplicate name error.

EXECUTE statement

[This is a beta feature; it’s not for use in production.]

The EXECUTE statement executes a prepared statement. When provided, name should identify a prepared statement. An error is returned if the name does not identify a prepared statement. When provided, plan should be the result of a prepared statement. An error is returned if the plan cannot be converted to a prepared statement.

Examples

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
        }
        }

Prepared statements are stored in memory until you restart the Couchbase Server. After restarting the server, you must prepare the statements again before you can execute the prepared statements.

For information on how to use prepared statements with various SDK, refer to the SDK documentation. For example, see Using prepared statements with .NET SDK.

EXECUTE statement

The EXECUTE statement executes a prepared statement. When specified, the name should identify a prepared statement. An error is returned if the name does not identify a prepared statement. When specified, the encoded_plan should be the result of a prepared statement. An error is returned if the encoded_plan cannot be converted to a prepared statement.

Examples

Prepared statement without a name

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
 }
}

>>>>>>> 5f28d521758ee0041b20af3c4a16a4725faedae3

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
 }
}
$

Specifying a name for the prepared statement

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
 }
 }
$

Specifying the name and the encoded_plan

The following example specifies a name for the prepared statement and the response includes an encoded_plan.

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 and encoded_plan from the example above to run the prepared statement.

Request
$ curl -v http://localhost:8093/query/service -H "Content-Type: application/json" -d  \
'{ "prepared":"fave_tweets", "encoded_plan":"H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==", "$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
   }
 }
$