Qlik Sense rest connection returns error

[heij1421]

Hi,

I'm trying to retrieve some data via de REST connector in Qlik Sense.

When I use the REST connector, I can retrieve the data. In this connection is also a hardcoded bearer token which should be retrieved for every connection. So I can't use this connector as I can't put a bearer token as variable in the connection.

So I don't want to use this connector because of the bearer token, but I want to make the connection within my script.

The script is:

LET vRequestBody ='{';

Let vRequestBody = vRequestBody&'"ReportId": 200052,';

Let vRequestBody = vRequestBody&'"Parameters":[';

Let vRequestBody = vRequestBody&'{';

Let vRequestBody = vRequestBody&'"Name":"START_DATE",';

Let vRequestBody = vRequestBody&'"Value":"01-01-2023"';

Let vRequestBody = vRequestBody&'}';

Let vRequestBody = vRequestBody&']';

Let vRequestBody = vRequestBody&'}';

let vRequestBody = replace(vRequestBody,'"', chr(34));

Unqualify *;

//LIB CONNECT TO 'REST_FLEXRAPPORT_ORDERS (hosting_039885_0002)';

RestConnectorMasterTable:

SQL SELECT 

"ordernr.",

"factuurnr."

FROM JSON (wrap off) "report"

 WITH CONNECTION (

    URL "$(vBaseURL)FlexReport/Get", 

HTTPHEADER "Content-Type" "application/json",

    HTTPHEADER "Authorization" "Bearer $(vAccessTokenValue)"//,

BODY "$(vRequestBody)"

    )

 ;

When I run the script, it returns this error:

So it seems it can not handle the date. In postman and in the qlik connector with the fixed bearer token the data can be retrieved. It is a POST request.

I also tried different date formats like "20230101" which returns this error:

Connector reply error: Unexpected lexem 'RequestBody' at (9,5). Expected 'CloseBracket'. Check SELECT STATEMENT syntax with documentation.

and "01/01/2023" returns this error:

Connector reply error: Unknown substring, string, or value at (9,72): '/01/2023"}]}"'

How to solve this?

[henrikalmen]

I don't believe the date is your problem, more likely it is the quotation encapsulation. This line is probably your problem:

BODY "$(vRequestBody)"

It renders to BODY "{"ReportId": 200052, "Para ...and so on. Observe the quotation marks, they don't add up. Try something like BODY '$(vRequestBody)' or BODY [$(vRequestBody)] instead, that might help. Or maybe the api you're calling can accept the body with single quotes instead, then you could alter the contents of vRequestBody to {'ReportId': 20...

[Vegar]

That is my conclusion as well when looking at your error. Your quotes are skewed hence you have a outer " " wrapper of the whole BODY together with sub " " within.

IF you fix that then you will most likely fix your error.

[heij1421 · Accepted solution]

The error was indeed due to the body.
When I changed the variable vRequestbody from
let vRequestBody = replace(vRequestBody,'"', chr(34));
to
let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

the error didn't occur anymore, as all double quotes are changed to double-double quotes.

So

LET vRequestBody ='{"ReportId": 200051,"Parameters":[{"Name":"START_DATE","Value":"01/01/2023"}]}';
Let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

OR

LET vRequestBody ='{""ReportId"": 200051,""Parameters"":[{""Name"":""START_DATE"",""Value"":""01/01/2023""}]}'; 

Also make sure that you create a (dummy) POST connection before this script, so Qlik is expecting a post request. I used the new REST api connection to 'https://jsonplaceholder.typicode.com/posts' with method POST as a dummy.