N1QL node.js - Can someone please post an example of a parameterized query?

Node.js SDK
1

Hi,

I can’t find a node.js example that uses a parameterized N1QL query, and especially, I don’t see one that uses UPDATE and parameters. Here’s mine, but it doesn’t work.

var couchbase = require("couchbase") ;
var N1qlQuery = require("couchbase").N1qlQuery ;
var myCluster = new couchbase.Cluster('cb4-server1:8091') ;
var myBucket = myCluster.openBucket('quote-unquote') ;

var nquery = N1qlQuery.fromString("SELECT META().id AS pkey, quoteid FROM `quote-unquote` WHERE channels = 'fitb-guess' ORDER BY time LIMIT 1") ;
myBucket.query(nquery, function(err, r1) {
    if(err) { console.log("ERROR: ", err) ; process.exit(1) ; }
    console.log("pkey=", r1[0]['pkey'], " quoteid=", r1[0]['quoteid']) ;
    nquery = N1qlQuery.fromString("SELECT META().id FROM `quote-unquote` WHERE channels = 'fitb-guess' AND quoteid = $quoteid") ;
    var params = { quoteid:r1[0]['quoteid'] }  ;
    var params2 = [ r1[0]['quoteid'] ] ;
    myBucket.query(nquery, params, function(err, r2) {
        if(err) { console.log("ERROR: ", err) ; process.exit(1) ; }
        console.log(r2) ;
        process.exit() ;
    }) ;
}) ;

The console output of the first log is:

pkey= 4fb1d19f3340b78679f20c1b5e96ab90  quoteid= quote000000001

Now, in the final myBucket.query, when I use params (the json object) as show in the above code I get:

ERROR:  { [Error: args has to be of type array]
  code: 1070,
  otherErrors: [],
  requestID: 'e7d4be12-5332-4f7e-89d9-cee7c4d741c9' }

However, when I change that to params2 (the array), I get no error, but it’s a blank result. I’m just trying to get the same primary key that I had in the first query using the quoteid as a parameter.

When I put that query in cbq command line:

SELECT META().id FROM `quote-unquote` WHERE channels = 'fitb-guess' AND quoteid = 'quote000000001' ;

It works fine:

"results": [
        {
            "id": "4fb1d19f3340b78679f20c1b5e96ab90"
        }
    ],

So something’s wrong with the node.js parameters part.

Kind regards,
David

2

Hey @dbergan,

Here is a snippet of code from a blog post that I did:

AccountModel.getByUsername = function(params, callback) {
    var query = N1qlQuery.fromString(
        "select users.* from `gaming-sample` as usernames " +
        "join `gaming-sample` as users on keys (\"user::\" || usernames.uid) " +
        "where meta(usernames).id = $1"
    );
    db.query(query, ["username::" + params.username], function(error, result) {
        if(error) {
            return callback(error, null);
        }
        callback(null, result);
    });
};

Notice a few things here:

In the query string itself, I am representing parameterized items with a $1. Had I been using more parameterized items I would do $2, $3, etc.

Then in the db.query call, the second parameter is my array. Each $1, $2, $3, etc. represents the index in the array.

I think you’re getting errors because you’re trying to use an object rather than an array.

Let me know if this solves your problem.

The blog post can be found here:

Best,

3

Wow! Yeah, that did it!

I was using named parameters because it was presented as an option on this page.

Placeholders may also be named. This is particularly useful when there are many query parameters and ensuring that they are all in the correct order may be cumbersome. Name query placeholders take the form of $name.

Furthermore, the Node.js SDK documentation says that params can accept an Object or Array and the description explicitly states:

A list or map to do replacements on a N1QL query.

Thus, I figured the SDK would work as described.

I suppose I can use the array method if that’s my only option. But is there a way to make the named parameters work? Some of my queries have a lot of parameters and it would be nice to use the name/map method.

Kind regards,
David

4

Hey @dbergan,

I’m glad that worked out!

Let me get our Node.js engineer, @brett19, involved in regards to named placeholders.

Best,

5

Any reply on this topic?

6

Yea sorry about that.

I opened an issue ticket with @brett19 and he believes it to be a bug, but hasn’t been able to verify for sure yet. I will report back as soon as I get better information.

Best,

7

Hey @dbergan,

Could you indicate what version of Couchbase Server and possibly Query Engine that you are using?

Cheers, Brett

8

I installed server 4.0 RC - community edition, on the day the RC became available. How do I find the version for Query Engine?

9

Hey @dbergan,

Knowing which Couchbase Server RC release you installed is fine. I suspect RC0 (build 4049 or so). Let me look into this and get back to you.

Cheers, Brett

10

Version: 4.0.0-4047 Community Edition (build-4047)

11

Hey @brett19,

Got anything?

12

@brett19 …

13

Hey @dbergan,

The fix for named parameters will be in today’s release:
http://review.couchbase.org/#/c/55841/

Cheers, Brett

14

Excellent. Thanks so much!