Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
heij1421
Partner - Contributor III
Partner - Contributor III

Qlik Sense rest connection returns error

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.

heij1421_0-1691764187435.png

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:
 
heij1421_3-1691764887307.png

 

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?
 
 
Labels (1)
1 Solution

Accepted Solutions
heij1421
Partner - Contributor III
Partner - Contributor III
Author

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.

View solution in original post

3 Replies
henrikalmen
Specialist
Specialist

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

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
Partner - Contributor III
Partner - Contributor III
Author

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.