PREPARE

PREPARE

PREPARE statement

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.

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 SDKs, refer to Querying with N1QL and N1QL from the 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
 }
}

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