topic Re: WITH CONNECTION statement - BODY with Double quote in Integration, Extension & APIs

WITH CONNECTION statement - BODY with Double quote

Anonymous — Sun, 19 Mar 2017 19:02:43 GMT

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.

Re: WITH CONNECTION statement - BODY with Double quote

petter — Sun, 19 Mar 2017 23:42:20 GMT

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...

Re: WITH CONNECTION statement - BODY with Double quote

carlcimino — Fri, 13 Jan 2023 18:45:47 GMT

@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!

Re: WITH CONNECTION statement - BODY with Double quote

petter — Wed, 22 Feb 2023 15:51:47 GMT

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')"}

Re: WITH CONNECTION statement - BODY with Double quote

carlcimino — Thu, 23 Feb 2023 15:54:38 GMT

@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')"}';

Re: WITH CONNECTION statement - BODY with Double quote

petter — Thu, 23 Feb 2023 16:25:02 GMT

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...

Re: WITH CONNECTION statement - BODY with Double quote

carlcimino — Thu, 23 Feb 2023 22:07:24 GMT

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'')"}';