Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik experts,
I've been struggling to get Qlik Sense to communicate with a database (Exact Online) that uses OAuth2. I've managed to get the token aspect to work, however now I'm stuck on how to retrieve the data from the database.
What I've done:
1. Set up an authorization process and a POST call in Qlik to get my access and refresh tokens.
2. Automatically get new access token if needed.
I've also managed to connect to the database using the access token. I know this, because my script gives an error message indicating my access token is no longer valid if I try to access it without the proper token, while giving a different parsing error when I use the correct token.
The endpoint I'm trying to contact is as shown here: https://start.exactonline.nl/docs/HlpRestAPIResources.aspx?SourceAction=10 and has the form of (for example): https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList?$select=InvoiceNum.... This would fetch the InvoiceNumbers as simple example.
I've managed to get this to work in Postman. The result I get I've added to the bottom of this post. The setting I use in postman are GET https://start.exactonline.nl/api/v1/{DivisionCode}/read/financial/ReceivablesList?$select=InvoiceNum... and Authorization Type Bearer Token.
In Qlik, I have the following code:
LIB CONNECT TO 'REST_GET'; // dummy connector
set vURL = 'https://start.exactonline.nl/api/v1/3020858/read/financial/ReceivablesList?$select=InvoiceNumber';
set vRequestBody = '';
MasterTable:
SQL SELECT "InvoiceNumber"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "$(vURL)",
HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
BODY "$(vRequestBody)"
);
Which gives the following error:
(Connector error: Unexpected character encountered while parsing value: <. Path '', line 0, position 0.)
I think I'm messing up in the select statement, as the connector seems to work. I've tried a lot there, like using specific XML statements. I'm not sure though. I get the same error no matter what input I give in the select statement.
Any tips?
Postman results (shortened and anonymised):
hello @Koen_D,
In this forum we would be able to help you with questions related to the product Qlik Application Automation.
This question seems to be related to Qlik Sense, if so, could you please post your question in that forum (https://community.qlik.com/t5/Qlik-Sense/ct-p/qlik-sense)?
Hello Qlik Community,
I've managed to get postman to give me the following result after a GET request:
hello @Koen_D,
In this forum we would be able to help you with questions related to the product Qlik Application Automation.
This question seems to be related to Qlik Sense, if so, could you please post your question in that forum (https://community.qlik.com/t5/Qlik-Sense/ct-p/qlik-sense)?
Ah, I'm sorry, I'll delete and repost.