CODEWITHSUNDEEP
couchbasecouchbase-java-api
akash verma
akash verma

Reputation: 159

Couchbase Java LIKE query giving syntax error - at %!(NOVERB)

Below is the java code:

QueryResult result = bucket.defaultScope().query("select * from _default d where d.name like %$nameParam%",
            QueryOptions.queryOptions()
                    .parameters(JsonObject.create().put("nameParam", param)));

Note: "_default" is my collection and the query works if I remove "like" and parameters options -> (select * from _default)

Below is the error coming :

com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed 
{"completed":true,"coreId":"0x3c3d77b600000001","errors":[{"code":3000,"message":"syntax error - at %!(NOVERB)"}],"httpStatus":400,"idempotent":false,"lastDispatchedFrom":"127.0.0.1:57882","lastDispatchedTo":"127.0.0.1:8093","requestId":11,"requestType":"QueryRequest","retried":0,"service":{"bucket":"CartTest","operationId":"976e0085-c83f-4732-ad8e-7f8e258f52b8","scope":"_default","statement":"select * from _default d where d.name like %$nameParam%","type":"query"},"timeoutMs":75000,"timings":{"dispatchMicros":5828,"totalDispatchMicros":5828,"totalMicros":70213}}

Currently I don't want to use FTS , just want to know the correct syntax for like query ?

Upvotes: 1

Views: 806

Answers (2)

Mark Day
Mark Day

Reputation: 17

I've been spending alot of time trying to get a reactive get items working with couchbase.

I'm using as of writing the latest couchbase gradle implementation

    implementation 'org.springframework.boot:spring-boot-starter-data-couchbase:2.7.3'
implementation 'com.couchbase.client:java-client:3.3.4'

Simple working example where Item is a POJO class

Service Class

public Mono<List<Item>> getItems(String user) {
    return storage.getItems(user).flatMapMany(r -> r.rowsAs(Item.class)).collectList();
}

Storage Class

public Mono<ReactiveQueryResult> getItems(String user)
{
   Cluster cluster = getCluster();
   return cluster.reactive().query(
           "SELECT `pid`, `name`, `type` FROM `MY_BUCKET` WHERE Meta().id LIKE \"" + user + "%\"");
}

Something to note here about building your query string. Simply go into the couchbase web console (localhost:8091) under the query tab and construct the query. Then you just simply have to paste it into the code between the quotes "". Don't forget that the query quotes must be prefixed with \

Then you can call it like this

List<Item> items = inventory.getItems(user).block();

Don't forget to block it to return the List.

Hope it helps someone get started.

Upvotes: 0

J.Zhao
J.Zhao

Reputation: 64

You would need to do, "select * from _default d where d.name like '%' || $nameParam || '%'" where "||" is string concat. What goes after "like" should be a string and "$" parameter cannot be inside string literal.

Upvotes: 1

Related Questions