Complex Prepared statement deployment through RESTAPI

Couchbase Capella
1

Hi Team,

We are in a process of automating the prepared statements through Azure Devops CI/CD pipeline to Couchbase Capella using RESTAPI. We are facing issues while deploying the complex prepared statements. We would need your suggestion for the below Prepared statement query to deploy through RESTAPI.

Prepared statement:

curl.exe -k -v -w “%(http code}” -X POST “https://*.cloud.couchbase.com:18093/query/service” -u username:password -H “Content-Type: application/json” -d
"{“statement”: ‘PREPARE testprepared12 AS
SELECT META(lb).id AS key,
{ “field1” AS type,
“value1” AS Src_type,
lb.field3 AS acct_nb,
LTRIM (lb_deposit_rt, “0”) AS rtgnb,
CONCAT2(‘|’, lb.deposit_act, LTRIM (lb.deposit_rt, “0”)) AS act_rtg_nb,
bnk.BankNumber AS bknb,
bnk.BankCode AS bnk.cd,
cta_ref.CustomerNumber AS cstmr.nb,
“C” AS tx_tp,
trancd_ref.txcd AS tx_cd,
lb.batch_amount AS tx_amt,
lb.deposit_date AS prcss_dt,
lb.deposit_date AS eff_dt,
ref.BhProdCategory AS BhProdCategory,
ref.DetailBaiCede AS dtlbaicode,
ref.DetailBaiCodedesc AS dtlbaidesc,
ref.TranCodeDesc AS desc
{
“ref_nb”: “”,
“bk_nb”: “”
} AS desc1,
{
“ref_nb”: “”,
“bk_ref”: “”,
“desc_1”: “”,
“desc 2”: “”,
“desc_3”: “”,
“desc_4”: “”,
“desc_5”: “”,
“desc 6”: “”
} AS desc2,
CLOCK_TZ(“US/Central”) AS cretd_on,
“proc1” AS cretd_by
} AS value
FROM
bucket01.scp01.coll01 lb
INNER JOIN bucket01.scp02.coll01 bnk
ON LTRIM(lb.deposit_rt, “0”) = bnk.RtNumber
INNER JOIN bucket01.scp02.coll02 trancd.ref
ON UPPER (cancd_cef.Ip.sNm)=‘TESTBOX’
INNER JOIN bucket01.scp02.coll03 ref
ON ref.txCd = ref.txCode
INNER JOIN bucket01.scp02.coll04 to cta_ref
ON lb.dpt_acct = cta_ref.AccountNumber
AND LTRIM(lb.deposit_rt,“0”) = cta_ref.RtgNumber
AND UPPER (cta_ref.Status) = “А”
WHERE
meta(lb).id = $meta_id’ }”

Error:

curl: (3) nested brace in URL position.

It looks like nested brace is not accepting inside the PREPARED statement creation. Could you please suggest how to create complex nested braces prepared statements via RESIAPI commands.

2

You have an opening parenthesis and a closing brace. They are mis-matched. And it seems that a space in ‘http code’ is problematic. When you post, use the ‘code’ formatting so it doesn’t change your quotes.

Start simple and add to it. Note that double-quotes within ‘statement’ will need to be escaped and that key is a keyword and must be escaped with back-tick.

curl -k -v -w "%{http_code}" -X POST "https://localhost:18093/query/service" -u Administrator:password -H "Content-Type: application/json" -d \
'{"statement": "PREPARE testprepared12 AS SELECT META(lb).id AS `key`, { field1 AS f } from lb WHERE meta(lb).id = 1" }'
3

In addition

This is not valid syntax with in the object construct it must be “field”: value

{“type”:”field1”, ….}

5

@mreiche Simple prepared statement is working fine with RESTAPI. I tried the above suggested one it is working fine as expected. But when i tried the below prepared statement it is not working, the below sample prepared statement is throwing error using curl command but the same is working fine in couchbase capella query workbench. Please suggest how to create the object construct in prepared statements with CONCAT2, LTRIM etc

curl.exe -k -v -w "%{http_code}" -X POST "https://localhost:18093/query/service" -u username:password -H "Content-Type: application/json" -d '{ \"statement\": \"prepare test01 AS SELECT META(lb).id                                   AS `key`
>>         ,{ 'sameday'                           AS type
>>         ,'testsrc'                                     AS src_sys
>>         ,lb.dp_num                            AS acct_nb
>>         ,LTRIM(lb.dp_rt, "0")                     AS rtg_nb
>>         ,CONCAT2('|',lb.dp_num,LTRIM(lb.dp_rt,"0")) AS acct_rtg_nb
>>        }                                              AS `value`
>> FROM
>>    bucket01.scp02.`c0ll5` lb
>>     INNER JOIN bucket01.scp01.`coll1` bnk
>>         ON LTRIM(lb.dp_rt,"0") = bnk.rtnum
>>     INNER JOIN bucket01.scp01.`coll2` trancd_ref
>>         ON UPPER(trancd_ref.TpsNm)="testsrc"
>>     INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref
>>         ON trancd_ref.TxCd = dtl_ref.TranCode
>>     INNER JOIN bucket01.scp01.`coll4` cta_ref
>>         ON lb.dp_num = cta_ref.AccountNumber
>>         AND LTRIM(lb.dp_rt,"0") = cta_ref.rtnum
>>         AND UPPER(cta_ref.Status) = "A"
>> WHERE
>>     meta(lb).id = $meta_id\"}'
At line:6 char:20
+ ...  ,CONCAT2('|',lb.dp_num,LTRIM(lb.dp_rt,"0")) AS acct_rt ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Expressions are only allowed as the first element of a pipeline.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ExpressionsMustBeFirstInPipeline

type or paste code here
6

Before trying to execute curl. try echoing the command to check the shell processing of the command.

You have the -d quoted with single quotes and you.have non-escaped single quotes within the -d parameter.

7

Try the following

curl.exe -k -v -w "%{http_code}" -X POST "https://localhost:18093/query/service" -u username:password -H "Content-Type: application/json" -d '{ "statement": "prepare test01 AS SELECT META(lb).id AS `key`, { \"type\":\"sameday\", \"src_sys\":\"testrc\", \"acct_nb\":lb.dp_num, \"rtg_nb\":LTRIM(lb.dp_rt, \"0\"), \"acct_rtg_nb\":CONCAT2(\"|\",lb.dp_num,LTRIM(lb.dp_rt,\"0\"))} AS `value` FROM bucket01.scp02.`c0ll5` lb INNER JOIN bucket01.scp01.`coll1` bnk ON LTRIM(lb.dp_rt,\"0\") = bnk.rtnum INNER JOIN bucket01.scp01.`coll2` trancd_ref ON UPPER(trancd_ref.TpsNm)=\"testsrc\" INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref ON trancd_ref.TxCd = dtl_ref.TranCode INNER JOIN bucket01.scp01.`coll4` cta_ref ON lb.dp_num = cta_ref.AccountNumber AND LTRIM(lb.dp_rt,\"0\") = cta_ref.rtnum AND UPPER(cta_ref.Status) = \"A\" WHERE meta(lb).id = $meta_id"}'

There is single quote on curl payload i.e -d ‘{……}’

Now “statement”: “…….” value is srting in double quoute

With in statement any string must be escaped quotes must be escaped with black slash. \”aaa\”

8

yes, i tried with escaping all strings by slash inside the statement but it is looking for “statement” double quote should also be escaped. Please find the curl and error details.

command:


curl.exe -k -v -w "%{http_code}" -X POST "https://localhost:18093/query/service" -u username:password -H "Content-Type: application/json" -d '{ "statement": "prepare test01 AS SELECT META(lb).id AS `key`, { \"type\":\"sameday\", \"src_sys\":\"testrc\", \"acct_nb\":lb.dp_num, \"rtg_nb\":LTRIM(lb.dp_rt, \"0\"), \"acct_rtg_nb\":CONCAT2(\"|\",lb.dp_num,LTRIM(lb.dp_rt,\"0\"))} AS `value` FROM bucket01.scp02.`c0ll5` lb INNER JOIN bucket01.scp01.`coll1` bnk ON LTRIM(lb.dp_rt,\"0\") = bnk.rtnum INNER JOIN bucket01.scp01.`coll2` trancd_ref ON UPPER(trancd_ref.TpsNm)=\"testsrc\" INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref ON trancd_ref.TxCd = dtl_ref.TranCode INNER JOIN bucket01.scp01.`coll4` cta_ref ON lb.dp_num = cta_ref.AccountNumber AND LTRIM(lb.dp_rt,\"0\") = cta_ref.rtnum AND UPPER(cta_ref.Status) = \"A\" WHERE meta(lb).id = $meta_id"}'

Error:

  • schannel: SSL/TLS connection renegotiated
    < HTTP/1.1 400 Bad Request
    < Content-Length: 349
    < Content-Type: application/json; version=7.6.3-N1QL
    < Date: Tue, 26 Aug 2025 16:32:40 GMT
    <
    {
    “requestID”: “319e6e15-a207-4f88-905f-c7540fc6c7cc”,
    “errors”: [{“code”:1040,“msg”:“Error processing getting key - cause: invalid character ‘s’ looking for beginning of object key string”}],
    “status”: “fatal”,
    “metrics”: {“elapsedTime”: “96.702µs”,“executionTime”: “48.201µs”,“resultCount”: 0,“resultSize”: 0,“serviceLoad”: 0,“errorCount”: 1}
    }
9 

curl -X POST "http://localhost:8093/query/service" -u Administrator:password -H "Content-Type: application/json" -d '{ "statement": "prepare test01 AS SELECT META(lb).id AS `key`, { \"type\":\"sameday\", \"src_sys\":\"testrc\", \"acct_nb\":lb.dp_num, \"rtg_nb\":LTRIM(lb.dp_rt, \"0\"), \"acct_rtg_nb\":CONCAT2(\"|\",lb.dp_num,LTRIM(lb.dp_rt,\"0\"))} AS `value` FROM bucket01.scp02.`c0ll5` lb INNER JOIN bucket01.scp01.`coll1` bnk ON LTRIM(lb.dp_rt,\"0\") = bnk.rtnum INNER JOIN bucket01.scp01.`coll2` trancd_ref ON UPPER(trancd_ref.TpsNm)=\"testsrc\" INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref ON trancd_ref.TxCd = dtl_ref.TranCode INNER JOIN bucket01.scp01.`coll4` cta_ref ON lb.dp_num = cta_ref.AccountNumber AND LTRIM(lb.dp_rt,\"0\") = cta_ref.rtnum AND UPPER(cta_ref.Status) = \"A\" WHERE meta(lb).id = $meta_id"}'id"}'

{
"requestID": "1b8a2dde-b7f0-4acb-b3cf-7fbc75c8a497",
"signature": "json",
"results": [
{"encoded_plan":"H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=","featureControls":76,"indexApiVersion":4,"indexScanKeyspaces":{"default:bucket01.scp01.coll1":false,"default:bucket01.scp01.coll2":false,"default:bucket01.scp01.coll3":false,"default:bucket01.scp01.coll4":false,"default:bucket01.scp02.c0ll5":false},"name":"[127.0.0.1:8091]test01","namespace":"default","operator":{"#operator":"Authorize","privileges":{"List":[{"Priv":7,"Props":0,"Target":"default:bucket01.scp02.c0ll5"},{"Priv":39,"Props":0,"Target":"default:bucket01.scp02.c0ll5"},{"Priv":7,"Props":0,"Target":"default:bucket01.scp01.coll1"},{"Priv":39,"Props":0,"Target":"default:bucket01.scp01.coll1"},{"Priv":7,"Props":0,"Target":"default:bucket01.scp01.coll2"},{"Priv":39,"Props":0,"Target":"default:bucket01.scp01.coll2"},{"Priv":7,"Props":0,"Target":"default:bucket01.scp01.coll3"},{"Priv":39,"Props":0,"Target":"default:bucket01.scp01.coll3"},{"Priv":7,"Props":0,"Target":"default:bucket01.scp01.coll4"},{"Priv":39,"Props":0,"Target":"default:bucket01.scp01.coll4"},{"Priv":39,"Props":0,"Target":"`default`:`bucket01`.`scp01`.`coll4`"},{"Priv":39,"Props":0,"Target":"`default`:`bucket01`.`scp01`.`coll3`"},{"Priv":39,"Props":0,"Target":"`default`:`bucket01`.`scp01`.`coll1`"},{"Priv":39,"Props":0,"Target":"`default`:`bucket01`.`scp02`.`c0ll5`"},{"Priv":39,"Props":0,"Target":"`default`:`bucket01`.`scp01`.`coll2`"}]},"~child":{"#operator":"Sequence","~children":[{"#operator":"Sequence","~children":[{"#operator":"PrimaryScan3","as":"cta_ref","bucket":"bucket01","index":"#sequentialscan","index_projection":{"primary_key":true},"keyspace":"coll4","namespace":"default","scope":"scp01","using":"sequentialscan"},{"#operator":"Fetch","as":"cta_ref","bucket":"bucket01","early_projection":["AccountNumber","Status","rtnum"],"keyspace":"coll4","namespace":"default","scope":"scp01"},{"#operator":"Parallel","maxParallelism":1,"~child":{"#operator":"Sequence","~children":[{"#operator":"Filter","alias":"cta_ref","condition":"(upper((`cta_ref`.`Status`)) = \"A\")"}]}},{"#operator":"HashJoin","build_aliases":["lb","bnk","trancd_ref","dtl_ref"],"build_exprs":["(`lb`.`dp_num`)","ltrim((`lb`.`dp_rt`), \"0\")"],"on_clause":"((((`lb`.`dp_num`) = (`cta_ref`.`AccountNumber`)) and (ltrim((`lb`.`dp_rt`), \"0\") = (`cta_ref`.`rtnum`))) and (upper((`cta_ref`.`Status`)) = \"A\"))","probe_exprs":["(`cta_ref`.`AccountNumber`)","(`cta_ref`.`rtnum`)"],"~child":{"#operator":"Sequence","~children":[{"#operator":"PrimaryScan3","as":"dtl_ref","bucket":"bucket01","index":"#sequentialscan","index_projection":{"primary_key":true},"keyspace":"coll3","namespace":"default","scope":"scp01","using":"sequentialscan"},{"#operator":"Fetch","as":"dtl_ref","bucket":"bucket01","early_projection":["TranCode"],"keyspace":"coll3","namespace":"default","scope":"scp01"},{"#operator":"HashJoin","build_aliases":["lb","bnk","trancd_ref"],"build_exprs":["(`trancd_ref`.`TxCd`)"],"on_clause":"((`trancd_ref`.`TxCd`) = (`dtl_ref`.`TranCode`))","probe_exprs":["(`dtl_ref`.`TranCode`)"],"~child":{"#operator":"Sequence","~children":[{"#operator":"PrimaryScan3","as":"bnk","bucket":"bucket01","index":"#sequentialscan","index_projection":{"primary_key":true},"keyspace":"coll1","namespace":"default","scope":"scp01","using":"sequentialscan"},{"#operator":"Fetch","as":"bnk","bucket":"bucket01","early_projection":["rtnum"],"keyspace":"coll1","namespace":"default","scope":"scp01"},{"#operator":"HashJoin","build_aliases":["lb"],"build_exprs":["ltrim((`lb`.`dp_rt`), \"0\")"],"on_clause":"(ltrim((`lb`.`dp_rt`), \"0\") = (`bnk`.`rtnum`))","probe_exprs":["(`bnk`.`rtnum`)"],"~child":{"#operator":"Sequence","~children":[{"#operator":"IndexScan3","as":"lb","bucket":"bucket01","index":"#sequentialscan","index_id":"#sequentialscan","keyspace":"c0ll5","namespace":"default","scope":"scp02","spans":[{"exact":true,"range":[{"high":"$meta_id","inclusion":3,"low":"$meta_id"}]}],"using":"sequentialscan"},{"#operator":"Fetch","as":"lb","bucket":"bucket01","early_projection":["dp_num","dp_rt"],"keyspace":"c0ll5","namespace":"default","scope":"scp02"},{"#operator":"Parallel","~child":{"#operator":"Sequence","~children":[{"#operator":"Filter","alias":"lb","condition":"((meta(`lb`).`id`) = $meta_id)"}]}}]}},{"#operator":"Parallel","maxParallelism":1,"~child":{"#operator":"Sequence","~children":[{"#operator":"NestedLoopJoin","alias":"trancd_ref","on_clause":"(upper((`trancd_ref`.`TpsNm`)) = \"testsrc\")","~child":{"#operator":"Sequence","~children":[{"#operator":"PrimaryScan3","as":"trancd_ref","bucket":"bucket01","index":"#sequentialscan","index_projection":{"primary_key":true},"keyspace":"coll2","namespace":"default","scope":"scp01","using":"sequentialscan"},{"#operator":"Fetch","as":"trancd_ref","bucket":"bucket01","cache_result":true,"early_projection":["TpsNm","TxCd"],"keyspace":"coll2","namespace":"default","nested_loop":true,"scope":"scp01"},{"#operator":"Parallel","maxParallelism":1,"~child":{"#operator":"Sequence","~children":[{"#operator":"Filter","alias":"trancd_ref","condition":"(upper((`trancd_ref`.`TpsNm`)) = \"testsrc\")"}]}}]}}]}}]}}]}},{"#operator":"Parallel","maxParallelism":1,"~child":{"#operator":"Sequence","~children":[{"#operator":"InitialProject","discard_original":true,"preserve_order":true,"result_terms":[{"as":"key","expr":"(meta(`lb`).`id`)"},{"as":"value","expr":"{\"acct_nb\": (`lb`.`dp_num`), \"acct_rtg_nb\": concat2(\"|\", (`lb`.`dp_num`), ltrim((`lb`.`dp_rt`), \"0\")), \"rtg_nb\": ltrim((`lb`.`dp_rt`), \"0\"), \"src_sys\": \"testrc\", \"type\": \"sameday\"}"}]}]}}]},{"#operator":"Stream","serializable":true}]}},"queryContext":"","reqType":"SELECT","signature":{"key":"json","value":"object"},"text":"prepare test01 AS SELECT META(lb).id AS `key`, { \"type\":\"sameday\", \"src_sys\":\"testrc\", \"acct_nb\":lb.dp_num, \"rtg_nb\":LTRIM(lb.dp_rt, \"0\"), \"acct_rtg_nb\":CONCAT2(\"|\",lb.dp_num,LTRIM(lb.dp_rt,\"0\"))} AS `value` FROM bucket01.scp02.`c0ll5` lb INNER JOIN bucket01.scp01.`coll1` bnk ON LTRIM(lb.dp_rt,\"0\") = bnk.rtnum INNER JOIN bucket01.scp01.`coll2` trancd_ref ON UPPER(trancd_ref.TpsNm)=\"testsrc\" INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref ON trancd_ref.TxCd = dtl_ref.TranCode INNER JOIN bucket01.scp01.`coll4` cta_ref ON lb.dp_num = cta_ref.AccountNumber AND LTRIM(lb.dp_rt,\"0\") = cta_ref.rtnum AND UPPER(cta_ref.Status) = \"A\" WHERE meta(lb).id = $meta_id","useCBO":true}
],
"status": "success",
"metrics": {"elapsedTime": "54.211274ms","executionTime": "54.115151ms","resultCount": 1,"resultSize": 6379,"serviceLoad": 3}
}

It works for me

11

@mreiche Please find the curl command display and execution error from powershell script.

Curl command: curl.exe -k -v -w "%{http_code}" -X POST "https://localhost:18093/query/service" -u username:password -H "Content-Type: application/json" -d '{ "statement": "prepare test01 AS SELECT META(lb).id                                   AS `key`\r\n         ,{ \"type\": \"sameday\"\r\n         ,\"src_sys\":  \"testsrc\"\r\n         ,\"acct_nb\": lb.dp_num                             \r\n         ,\"rtg_nb\": LTRIM(lb.dp_rt, \"0\")\r\n         ,CONCAT2(\"|\",lb.dp_num,LTRIM(lb.dp_rt,\"0\")) AS acct_rtg_nb\r\n        }                                            AS `value`\r\n FROM\r\n    bucket01.scp02.`c0ll5` lb\r\n     INNER JOIN bucket01.scp01.`coll1` bnk\r\n         ON LTRIM(lb.dp_rt,\"0\") = bnk.rtnum\r\n     INNER JOIN bucket01.scp01.`coll2` trancd_ref\r\n         ON UPPER(trancd_ref.TpsNm)=\"testsrc\"\r\n     INNER JOIN `bucket01`.`scp01`.`coll3` dtl_ref\r\n         ON trancd_ref.TxCd = dtl_ref.TranCode\r\n     INNER JOIN bucket01.scp01.`coll4` cta_ref\r\n         ON lb.dp_num = cta_ref.AccountNumber\r\n         AND LTRIM(lb.dp_rt,\"0\") = cta_ref.rtnum\r\n         AND UPPER(cta_ref.Status) = \"A\"\r\n WHERE\r\n     meta(lb).id = $meta_id" }'

Error:
httpCode: { “requestID”: “e1bf80fd-238c-4582-9c0f-6aaf1ade00da”, “errors”: [{“code”:1040,“msg”:“Error processing getting key - cause: invalid character ‘s’ looking for beginning of object key string”}], “status”: “fatal”, “metrics”: {“elapsedTime”: “79.101┬╡s”,“executionTime”: “28.9┬╡s”,“resultCount”: 0,“resultSize”: 0,“serviceLoad”: 0,“errorCount”: 1} } 400000000000000000000
[ERROR] Failed to create PREPARED statements (HTTP { “requestID”: “e1bf80fd-238c-4582-9c0f-6aaf1ade00da”, “errors”: [{“code”:1040,“msg”:“Error processing getting key - cause: invalid character ‘s’ looking for beginning of object key string”}], “status”: “fatal”, “metrics”: {“elapsedTime”: “79.101┬╡s”,“executionTime”: “28.9┬╡s”,“resultCount”: 0,“resultSize”: 0,“serviceLoad”: 0,“errorCount”: 1} } 400000000000000000000)

12

powershell appears to be removing the unescaped double-quotes. So you will need to escape those double-quotes so powershell doesn’t remove them (with back-tics?). And the double-quotes you already have escaped with a backslash for json - I suspect you will need to escape the backslash and the double-quote. So back-tic, back-slash, back-tick, double-quote. Since there are no special characters in your bucket name - the bucketname does not need to be quoted with back-tics.

13
14

@mreiche @vsr1 I tried possible scenarios which you explained with back-tic, back-slash, back-tick, double-quote but it did not helped. Could you please share some simple example for prepared statements using double quotes with escaped chars, so i can try test out in my Powershell and see.

15

I don’t have powershell. But like I suggested a few times already - use “echo” to show how the shell interprets it. And since your issue is with powershell, try posting on a powershell forum. Or stackoverflow.

I tried possible scenarios which you explained with back-tic, back-slash, back-tick, double-quote but it did not helped

Without seeing what you tried and how it failed, it’s not possible to indicate to you what you should do differently. And again - use echo. echo will show exactly what the issue is versus some obscure json parsing message

16 
The following should work in normal shell
curl.exe -u username:password -X POST "http://localhost:8093/query/service" "Content-Type: application/json" -d '{"statement": "SELECT \"hello\"" }'

If you want power shell see if this works or adjust what described here https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_quoting_rules?view=powershell-7.5 until you get correct output

curl.exe -u username:password -X POST "http://localhost:8093/query/service" "Content-Type: application/json" -d '{"statement": ""SELECT \"hello\""" }'

First command should work