WITH CONNECTION statement - BODY with Double quote

[Anonymous]

I am trying to POST a REST connection with a Body.

While there is no issue when it is a static BODY (in the connection edition), I can't do it right now in the BODY parameter of the "WITH CONNECTION" statement.

Would you know if there is any escape character or any other solution?

I can't replace double quote by nothing nor by single quote as per the API requirements.

Here is the exemple:

...

Let vBody = '{["ids":[10001,10002]]}';

Load

...

FROM JSON (wrap off) "root" PK "__KEY_root"

WITH CONNECTION(URL "$(vUrl)",

HEADER "$(vHeaderName)" "$(vHeaderParameter",

BODY "$(vBody)");

Thank you in advance for your help.

[petter · Accepted solution]

You can simply double your quotes and Qlik will make sure to send it correctly.

Let vBody = '{[""ids"":[10001,10002]]}';

So doubling your double quotes will do the trick...

[carlcimino]

@petter I'm trying to do something very similar but must be missing something.  I have this as my body in the connection.

{ "query": "SELECT * FROM AppLogs f WHERE (DateTimePart('yyyy', TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in ('/payments/post-sales','/payments/pre-sales','/payments/refunds','/payments','/Payments')"}

i tried to do this:

let vBody = '{ "query": "SELECT * FROM AppLogs f WHERE (DateTimePart('yyyy', TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in ('/payments/post-sales','/payments/pre-sales','/payments/refunds','/payments','/Payments')"}';

I then put the BODY and variable in the with connection

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(url "$(v_EnvironmentURL)",

HTTPHEADER "Authorization" "$(vSessionId)",

HTTPHEADER "x-ms-date" "$(vMS_Date)",

HTTPHEADER "x-ms-version" "$(vMS_Version)",

HTTPHEADER "x-ms-max-item-count" "1000",

BODY "$(vBody)")

;

when I go to load the data I get this error message.  Any ideas/suggestions would be much appreciated!

[petter]

Is your source an Azure Cosmos DB - it sure looks like it with the two functions DateTimePart() and  TimestampToDateTime()....

According to Microsofts documentation the first parameter of the DateTimePart needs to be in double quotes - not in single quotes like you have done. So the first I would try using two double quotes like this:

{ "query": "SELECT * FROM AppLogs f WHERE (DateTimePart(""yyyy"", TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in ('/payments/post-sales','/payments/pre-sales','/payments/refunds','/payments','/Payments')"}

[carlcimino]

@petter Yes you're absolutely correct.  This is Azure Cosmos DB. I tried to do as you suggested and it still gives the unexpected token/operator error.  The script editor looks like it doesn't like the '/ in the request paths part of the query.  In pPstman it works fine without the double quotes and the request paths the way I have them.

let vBody = '{"query": "SELECT * FROM AppLogs f WHERE (DateTimePart(""yyyy"", TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in ('/payments/post-sales','/payments/pre-sales','/payments/refunds','/payments','/Payments')"}';

[petter]

Then I would have tried to replace each of your single-quotes either by double single-quotes or double double-quotes. It is quick to try and it might do the trick...

[carlcimino]

This is very frustrating.  Tried both ways.  Looks like something next to the SELECT or query part.

Double double Quotes.

let vBody = '{"query": "SELECT * FROM AppLogs f WHERE (DateTimePart(""yyyy"", TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in (""/payments/post-sales"",""/payments/pre-sales"",""/payments/refunds"",""/payments"",""/Payments"")"}';

Double Single quotes

let vBody = '{"query": "SELECT * FROM AppLogs f WHERE (DateTimePart(''yyyy'', TimestampToDateTime(f._ts * 1000)) = 2023) AND f.RequestPath in (''/payments/post-sales'',''/payments/pre-sales'',''/payments/refunds'',''/payments'',''/Payments'')"}';