Moving from positional parameters to named parameters

SQL++
1

I am trying to move from a request with positional parameters to a request with named parameters. The positional has done me well in past but once you get to 3 + param its hard to keep track which param is what. Not sure what i am missing by passing the params to the query.

Currently i am using to call my N1QL queries like this

const result = await cb.n1qlQuery('contacts',cbSqlStr.column_info,[docKey,colKey])

Here is the function

const n1qlQuery = async (collectId, n1qlStr, id) => {
  try {
    let statement = n1qlStr;
    const options = {
      scanConsistency: couchbase.QueryScanConsistency.RequestPlus,
      parameters: id,
    };

    const result = await cluster.query(statement, options);
    const data = result.rows;
    const meta = result.meta;

    return { data, meta };
  } catch (err) {
    console.log(err);
  }
};

And here is the query

column_info: SELECT col.* FROM Contacts c USE KEYS $1 UNNEST c.columns AS col WHERE col.colId = $2

So based on the docs i tried to change query to

column_info: SELECT col.* FROM Contacts c USE KEYS $DocId UNNEST c.columns AS col WHERE col.colId = $colId

And tried to call it like this

const result = await cb.n1qlQuery_wId('contacts',cbSqlStr.column_info,[$DocId=docKey,$colKey=colKey])

Doing so throws me the following Error complaining that there is no value for $DocId

InternalServerFailureError: internal server failure
    at Object.translateCppError (C:\nodeRoot\CRM-NodeJS\node_modules\couchbase\dist\bindingutilities.js:188:20)
    at C:\nodeRoot\CRM-NodeJS\node_modules\couchbase\dist\connection.js:248:54 {
  cause: LibcouchbaseError: libcouchbase error 205
      at Object.translateCppError (C:\nodeRoot\CRM-NodeJS\node_modules\couchbase\dist\bindingutilities.js:174:21)
      at C:\nodeRoot\CRM-NodeJS\node_modules\couchbase\dist\connection.js:248:54 {
    code: 205
  },
  context: QueryErrorContext {
    first_error_code: 5010,
    first_error_message: 'Error evaluating KEYS - cause: No value for named parameter $DocId (near line 1, column 38).',
    statement: 'SELECT col.* FROM Contacts c USE KEYS $DocId UNNEST c.columns AS col WHERE col.colId = $colId',
    client_context_id: 'cdde636b2e7cf3e7',
    parameters: '',
    http_response_code: 200,
    http_response_body: ''
  }
}
2

Check out example here

3

Thanks, that helped as there was not much docs on it. The main difference is that the numbered param is a Array of values vs the named parameters is a Object.

1 Like