Database is not updating with AddNamedParameter for Update statement

.NET SDK
,
1

Please find below query. It is returning success as true but database record is not updating

queryRequest = new QueryRequest().
.Statement(“UPDATE “+ bucketName + " SET modifiedTimestamp = $modifiedTimestamp, " + “where meta().id = $metaid and documentType = $documentType”)
.AddNamedParameter(”$modifiedTimestamp”, currentESTDateTime.ToString(dateFormat))
.AddNamedParameter("$metaid", metaId)
.AddNamedParameter("$documentType", documentType);

Please correct above query if its wrong

2

You should try the query in query workbench first. Also you have document key. You can direct KV update.

3

Hi @pratik.dungarwal,

There are a few things that might be the problem. First, when I pasted your code in, I noticed that there was a syntax error. There’s a period after QueryRequest() and there’s another period right after it before Statement.

Second, in your N1QL, there’s a comma after $modifiedTimestamp, but following that is a WHERE clause. This is a syntax error.

Third, do you have appropriate indexes created for this kind of query?

Fourth, in your code, I don’t see that you actually execute the query. Something like bucket.Query<dynamic>(queryRequest).

And finally, I agree with @vsr1 that you should execute the query in Query Workbench to make sure it’s actually doing what you expect. In your query, you use both meta().id and documentType in the WHERE clause. If the document doesn’t have that documentType property or the key is incorrect, then the query will be “successful” but it won’t actually do anything.

For more help, you can check out the documentation on using N1QL from an SDK here: https://docs.couchbase.com/dotnet-sdk/2.6/n1ql-queries-with-sdk.html

4

Hi @matthew.groves
because of security reason I modified some of the content after pasting query here so because of that may be you are getting syntax error. Query was running successfully at my end. My concern was I am able to get result in case of “SELECT…” but not able to update database in case of “UPDATE” even on getting SUCCESS as true.
Please find below updated query.
var queryRequest = new QueryRequest().
.Statement(“UPDATE XYZBucket SET modifiedTimestamp = $modifiedTimestamp WHERE meta().id = $metaid and documentType = $documentType”)
.AddNamedParameter("$modifiedTimestamp", DateTime.Now.ToString())
.AddNamedParameter("$metaid", metaId)
.AddNamedParameter("$documentType", documentType);

var result = _bucket.Query(queryRequest ).Success;

Please correct me if i am trying something wrong.

5

Can u remove $ in these 3 lines example: .AddNamedParameter(“metaid”, metaId)

6

Hi @vsr1,
I tried removing that as well but still not able to update database. I have one basic question. I checked the result of Query() and in that we are calling GET API of Couchbase. Is it correct that same will work for “UPDATE” query?
Or I am missing something? I am stuck because of it. I was trying with string previously and it was working properly but because of some special character in UPDATE query I have to use this. If we have something for special character then that will also helpful.

Thanks in advance.

7

@pratik.dungarwal,

Could you please post an example of a document you expect to get updated but isn’t getting updated?

8

I might recommend turning the log level up (in addition to @matthew.groves’s suggestion) so we can all can see the HTTP conversation from the SDK to the query service. That may make the interaction between your high level code and the conversation with the query service more clear and may help in spotting the issue.

9

Please find document which i want to update

{
“createTimeStamp”: “09/26/2018 07:35:40”,
“documentType”: “dispose”,
“otherDocId”: “12232334454556”,
“id”: “910291029102910-unique”,
“modifiedTimestamp”: “09/27/2018 13:47:40”,
“data”: [
{
“updateData”: {
“analyst”: “abc”,
“comment”: “comment”,
“modifiedTimestamp”: “09/26/2018 07:57:48”,
“reason”: “reason”
},
“text”: “11331139”
},
{
“updateData”: {
“analyst”: “abc”,
“comment”: “comment”,
“modifiedTimestamp”: “09/26/2018 07:57:48”,
“reason”: “reason”
},
“text”: “11331139”
},
{
“updateData”: {
“analyst”: “abc”,
“comment”: “comment”,
“modifiedTimestamp”: “09/26/2018 07:57:48”,
“reason”: “reason”
},
“text”: “11331139”
}
]
}

10

@pratik.dungarwal,

You didn’t specify what the document key is, and you never answered if you have the appropriate index. So I’m going to assume that the document key is 910291029102910-unique and you’ve created an index on the documentType field. Then, the following worked for me:

UPDATE pratiksBucket
SET modifiedTimestamp = 'modified'
WHERE meta().id = '910291029102910-unique'
AND documentType = 'dispose';

Does that also work for you when you try it in Query Workbench directly?

11

Hi @matthew.groves,
Did you tried with QueryRequest object as mentioned above?
Normal query is working for me as well from querybench. I am facing issue if i tried running using AddNamedParameter object. I am using “QueryRequest” only because i want to manage all special character, which is managed by QueryRequest.

12

@pratik.dungarwal,

I’m just trying to eliminate causes and narrow it down. If the query works for you in Query Workbench directly, then we can eliminate that as the problem.

13

Sure @matthew.groves. Yes it is working in querybench at my end.

14

Hi @pratik.dungarwal,

I ran the following C# program and it works as expected.

static void Main(string[] args)
{
    var cluster = new Cluster(new ClientConfiguration
    {
        Servers = new List<Uri> {new Uri("http://localhost:8091")}
    });
    cluster.Authenticate("Administrator", "password");
    var bucket = cluster.OpenBucket("pratik");

    var metaId = "910291029102910-unique";
    var documentType = "dispose";
    var modifiedTimestamp = DateTime.Now;

    Console.WriteLine("Updating modifiedTimestamp to " + modifiedTimestamp);

    var queryRequest = new QueryRequest()
        .Statement("UPDATE pratik SET modifiedTimestamp = $modifiedTimestamp WHERE meta().id = $metaid and documentType = $documentType")
        .AddNamedParameter("$modifiedTimestamp", modifiedTimestamp)
        .AddNamedParameter("$metaid", metaId)
        .AddNamedParameter("$documentType", documentType);

    var result = bucket.Query<dynamic>(queryRequest);

    Console.WriteLine("Success: " + result.Success);

    var doc = bucket.Get<dynamic>(metaId).Value;
    Console.WriteLine(doc.modifiedTimestamp);

    cluster.Dispose();
}

The output is:

Updating modifiedTimestamp to 10/5/2018 9:35:11 AM
Success: True
10/5/2018 9:35:11 AM
Press any key to continue . . .

Does anything stand out as different between my program and yours?

15

Thanks @matthew.groves, I don’t what was issue but it is working now with above code. may be i was missing something related to query. Thanks for your solution.