ParametrizedQuery does not work properly

Java SDK
1

Hello,

I ran into this problem:

            JsonObject paramsObject =  JsonObject.create();
            paramsObject.put("name",args[0]);
            JsonArray paramsArray =  JsonArray.create();
            paramsArray.add(args[0]);


            Query query = ParametrizedQuery.parametrized("SELECT * FROM bucket where field='$name' limit 1", paramsObject);
            System.out.println(query.n1ql().toString());
            QueryResult res = bucket.query(query);
            for ( QueryRow row : res.allRows()) {
                System.out.println("RESULT: "+row.value());
            }                

            query = ParametrizedQuery.parametrized("SELECT * FROM bucket where field='$1' limit 1", paramsArray);
            System.out.println(query.n1ql().toString());
            res = bucket.query(query);
            for ( QueryRow row : res.allRows()) {
                System.out.println("RESULT: "+row.value());
            }
            
            query = ParametrizedQuery.parametrized("SELECT * FROM bucket where field='"+args[0]+"' limit 1", paramsArray);
            System.out.println(query.n1ql().toString());
            res = bucket.query(query);
            for ( QueryRow row : res.allRows()) {
                System.out.println("RESULT: "+row.value());
            }

And just the last one works properly (the output):

        {"args":["any_command_line_value"],"statement":"SELECT * FROM bucket where field='$name' limit 1"}
        {"args":["any_command_line_value"],"statement":"SELECT * FROM bucket where field='$1' limit 1"}
        {"args":["any_command_line_value"],"statement":"SELECT * FROM bucket where field='any_command_line_value' limit 1"}
RESULT:    {"bucket": ....

Can you help me, please?
What did i wrong?

The versions:

3.0.1-1444-rel
cbq-dp4
rxjava-1.0.8.jar
core-io-1.1.1.jar
java-client-2.1.1.jar
couchbase-client-1.4.8.jar

Thank you so much!
Cheers!
Huhh

2

hi @huhh,
there is a bug in N1QL DP4 for the parametrized query with named placeholders, it doesn’t work (which is why we used $1 format in our examples in the blog post).
it’s only true for querying via the POST interface with JSON request format, but that’s unfortunately the one we are using in the SDK (since it allows the maximum range of features to be used), so it may be the case that the same query works in cbq client.

Best workaround for Java is to use the $1 syntax. The issue was since fixed (Loading...) and should be available in the next public release of N1QL (either as a separate download or part of developer preview for next major version of Couchbase Server).

Simon

3

Dear Simon!
Thank you very much!
Cheers, Huhh

4

Hi Simon,

I am facing the problem with below query parametrized query.

String ramainingPackageByExceptionQuery = "SELECT shuTckNr AS trackingNumber FROM “+”"+bucketName+" "+
“WHERE ANY sort IN busOp SATISFIES sort.opPlnDt = now_str(‘1111-11-11’) END AND evtTypCd = $1”;
log.info(“ramainingPackageByExceptionQuery============================>>>>>>>”+ramainingPackageByExceptionQuery);
int count = 03;

    //N1qlQuery n1qlQuery = N1qlQuery.simple(ramainingPackageByExceptionQuery);
    JsonArray placeholderValue = JsonArray.from(eventTypeCode);
    log.info("placeholderValue==========="+placeholderValue);
    ParameterizedN1qlQuery parameterizedN1qlQuery = N1qlQuery.parameterized(ramainingPackageByExceptionQuery, jsonObject);
    log.info("parameterizedN1qlQuery================>>>>>>>>>>>>>>>>>>>>>>"+parameterizedN1qlQuery.statement());
    List<RemainingPackagesByExceptionData> remainingPackagesByExceptionDatas = couchbaseTemplate.findByN1QLProjection(parameterizedN1qlQuery,RemainingPackagesByExceptionData.class);

The problem is when i see log.info(“placeholderValue===========”+placeholderValue); its printing ["“03"”]. however my eventcode value is eventcode=“03”.

Can you please help me out?

6

Hi @dhiraj.singh,

The code doesn’t contain how the eventTypeCode list has been created. If it was a list of ints, then it should have probably worked.

List<Integer> eventTypeCode = Arrays.asList(03);
JsonObject placeHolders = JsonObject.create().put("evtTypCd", JsonArray.from(eventTypeCode));
7

Thanks For your reply Subhashni…

evenTypeCode is String passed as arguments in repository layer. As you can see below:–

public List retrieveRemainingPackageByExceptionType(String eventTypeCode) {
String ramainingPackageByExceptionQuery = "SELECT shuTckNr AS trackingNumber FROM “+”"+bucketName+" "+
“WHERE ANY sort IN busOp SATISFIES sort.opPlnDt = now_str(‘1111-11-11’) END AND evtTypCd = ‘$1’”;
JsonArray placeholderValue = JsonArray.from(eventTypeCode);
log.info(“placeholderValue===========”+placeholderValue);
ParameterizedN1qlQuery parameterizedN1qlQuery = N1qlQuery.parameterized(ramainingPackageByExceptionQuery, placeholderValue);
List remainingPackagesByExceptionDatas = couchbaseTemplate.findByN1QLProjection(parameterizedN1qlQuery,RemainingPackagesByExceptionData.class);
return remainingPackagesByExceptionDatas;
}

8

However if i am trying this with harcoded value as beolw
JsonArray placeholderValue = JsonArray.from(“03”);

its working fine.

9

If it is working ignore this post, Otherwise remove quotes around $1. this makes additional quotes.

10

I tried AND evtTypeCd = ‘$1’";

Its not working

11

Hi Subhashni I tried with above code also…its still not working

List strList = Arrays.asList(eventTypeCode);
JsonObject placeHolders = JsonObject.create().put(“evtTypCd”, JsonArray.from(strList));

12

Hi @dhiraj.singh,

From your previous comments, I think the parameter is an integer. You should not be passing it in as a string.

13

Subhashni as per your reply we can not pass String as parameter in N1Q1L query. Is it a restriction?? What if i have string field in my docs and i want to use that in where clause?

14

Hi subhasni,

I found the solution for above problem, it was not the couchbase issue instead it was issue with the query parameter which is getting passed from rest Layer. Can you suggest me options of passing array as parameter for IN. Like for below query

SELECT count(*) FROM default
WHERE ANY sort IN busOp SATISFIES sort.opPlnDt = now_str(‘1111-11-11’) END
AND eqpNr IN [“04856374”,“04856375”,“04856378”] AND pkgPrcStsCd = “01”

You can see i need to pass Array of eqpNr.

Thanks,
Dhiraj

15

Hi All

I have date in my docs as 2017-06-07T10:00:00Z. I want to get yyyy-MM-dd from this to compare with now_tz(‘UTC’,‘1111-11-11’). How can i do that?

16

you can try SUBSTR function to get yyyy-MM-dd, for example

SUBSTR("2017-06-07T10:00:00Z",0,10)

will get

2017-06-07
17

@atom_yang
Thanks for your reply. I found the solution with
DATE_FORMAT_STR(“2017-06-07T10:00:00Z”, ‘1111-11-11’)