topic REST - queryHeaders as variable? in QlikView

REST - queryHeaders as variable?

robert_mika — Fri, 06 May 2016 15:26:14 GMT

Does anyone have working example of such an implementation?

I can use variable withing URL but not as query header.

In below example:

let v='Assignments';

let v2='Authorization%2WRAP access_token%%2%%3client%%2336b434e68de4f97ba5ad160.26395&user_token%%2db2b40ac154c4dbeb36f5e686899b211.26395%%3%1Accept%2application/json';

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url= https://api.dovico.com/$(v)/?version=5;

timeout=30;method=GET;autoDetectResponseType=true;

keyGenerationStrategy=3;useWindowsAuthentication=false;useCertificate=No;

certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=version%25;

queryHeaders=$(v2);

PaginationType=None;

XUserId=BIZYJNJbaCGLA;

XPassword=cJfbZSUEQLYGXZdOPTcIWMFTcSEXVKLLJCTaB;";

the first variable v works but the second v2 throws an error:

Parameter "$(v2)" has no value, or a misplaced semicolon.

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url= https://api.dovico.com/Assignments/?version=5;

timeout=30;method=GET;autoDetectResponseType=true;

keyGenerationStrategy=3;useWindowsAuthentication=false;useCertificate=No;

certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=version%25;

queryHeaders=$(v2);

PaginationType=None;

XUserId=BIZYJZJbaCGLA;

XPassword=cJfbZSUEQLYGXZdOPTcDCJMFTcSEXVKLLJCTaB;"

Re: REST - queryHeaders as variable?

petter — Fri, 06 May 2016 15:40:36 GMT

The proper way of overriding URL, query parameters, headers or body dynamically it using the WITH CONNECTION clause as part of your SELECT statement:

The format:

<select_stmt> WITH CONNECTION (<connection_item> {,<connection_item>})

connection_item = URL "new url" | QUERY "param name" "param value" | HEADER "header name" "header value" | BODY "request body text"

Example:

Re: REST - queryHeaders as variable?

robert_mika — Fri, 06 May 2016 15:46:02 GMT

I can not see any variable used here.

Could you give more details,please?

Re: REST - queryHeaders as variable?

petter — Fri, 06 May 2016 15:54:57 GMT

You can put $-sign expansion of variables like you normally would like this:

FOR i=1 TO 20
  paramURL = 'http:.....$(i)....';
....
  headerToken = 'adklfjadflkjasdskdfljf';

  SQL SELECT
....
....
  WITH CONNECTION (
      URL "$(paramURL)",
      QUERY "type" "$(querystringType)",
      QUERY "size" "$(querystringSize)",
      HTTPHEADER "auth_type" "$(headerAuthType)",  
      HTTPHEADER "auth_token" "$(headerToken)"
  );
NEXT

Re: REST - queryHeaders as variable?

robert_mika — Fri, 06 May 2016 16:10:14 GMT

Thank you, Petter.

How the REST - CUSTOM CONNECT header will then look like?

What will be the parameter for URL?

Generic or empty or...?

Re: REST - queryHeaders as variable?

petter — Fri, 06 May 2016 16:23:35 GMT

The intial CUSTOM CONNECT statement is valid until a SQL SELECT with the WITH CONNECTION statement is executed. The parameters mentioned in the WITH CONNECT statement will override any of the headers, url, query and body paremeters. You don't need to change the URL if that is static. You can easily change some or all of the QUERY parameters of the URL by just using the QUERY part of WITH CONNECTION or HTTPHEADER.

You should in other words only modify what you need to.

The contents of your v2 variable seems a bit odd to me. Is this a composition of multiple headers?

Re: REST - queryHeaders as variable?

robert_mika — Mon, 09 May 2016 14:40:31 GMT

All works now.

Thank you Petter for your time.

Re: REST - queryHeaders as variable?

Thu, 01 Sep 2016 09:31:31 GMT

Can I also use pagination parameters in "with connection"-statement?

Re: REST - queryHeaders as variable?

petter — Mon, 20 Feb 2017 15:18:06 GMT

Yes you can. If you choose Custom Pagination that is how you have to do it.

Re: REST - queryHeaders as variable?

pentaxadmin — Wed, 01 Mar 2017 20:43:11 GMT

Petter:

I believe to have a similar problem, but I am not quite sure if I understand everything.

I have the following API: https://api.census.gov/data/2015/acs5?get=NAME,GEOID,B17024_001E,B17024_001M,B17024_002E,B17024_002M&for=school+district…

In the example above, I am not able to use in=state:* - a wildcard, and was looking for a solution how to create a dynamic variables

Thanks for your input.

Branislav

Re: REST - queryHeaders as variable?

pentaxadmin — Thu, 02 Mar 2017 16:00:01 GMT

Petter:

I was able to follow your logic with help from Qlik's Architect, but now I have another issue:

The loop goes through all states, but the output is only one state:01 that is specified in the connection string

1. Connection:

'ABC'

(https://api.census.gov/data/2015/acs5?get=NAME,GEOID&for=school+district+(unified):*&in=state:01&key=mykeyxxx)

2. Script:

for i= 01 TO 56

paramURL = 'https://api.census.gov/data/2015/acs5?get%2NAME,GEOID&for%2school+district+(unified):*&in%2state:$(i)&key%2mykeyxxx;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=xxx;XPassword=xxx;';

LIB CONNECT TO 'ABC';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

"@Value",

"__FK_root"

FROM "root" FK "__FK_root" ArrayValueAlias "@Value")

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

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(paramURL)"

WITH CONNECTION (

URL "$(paramURL)"

);

[root]:

LOAD [@Value] AS [@Value],

[__FK_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_root]);

DROP TABLE RestConnectorMasterTable;

Re: REST - queryHeaders as variable?

pentaxadmin — Mon, 13 Mar 2017 19:03:15 GMT

Petter:

I have figured out what was the issue. I was mixing CUSTOM CONNECT TO with WITH CONNECTION

Working Script:

LIB CONNECT TO 'ABC';

let paramURL = 'https://api.census.gov/data/2015/acs5?get=NAME,GEOID&for=school+district+(unified):*&in=state:$(i)&key=***';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

"@Value",

"__FK_root"

FROM "root" FK "__FK_root" ArrayValueAlias "@Value")

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

WITH CONNECTION (

URL "$(paramURL)"

);

[root]:

LOAD

[@Value] AS [@Value],

[__FK_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_root]);

DROP TABLE RestConnectorMasterTable;

Re: REST - queryHeaders as variable?

prees959 — Thu, 17 Aug 2017 16:24:28 GMT

Hi,

It it possible to store the Authorization token inside a variable that. I need to run the API inside a loop

and on each pass update the authorization token..

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://.mydomain/;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=;queryHeaders= 'headers'%1Authorization%2Token token%%$(APIToken);PaginationType=None;XUserId=xxx;XPassword=99999;";

Is this possible?

Many thanks for any advice!

Phil

Re: REST - queryHeaders as variable?

prees959 — Mon, 04 Sep 2017 12:10:05 GMT

Hi Petter,,

The API i have been told to use will only bring back 100 records at a time. I have been told to use 'Limit' and 'Offset' in my query parameters - nut cannot get this to work. Do I need any more information from the developers for this?

Thanks for any advice,

Phil

Re: REST - queryHeaders as variable?

pawwy1415 — Tue, 03 Sep 2019 05:36:59 GMT

Hi ,
I am looking for the solution to filter the data from rest connector.
I have a query like below
SQL SELECT
"id" as "id",
"name" as "name_u1"
FROM JSON (wrap on) "root" PK "__PK_root";

From name field I have to filter only Approved Data i.e., name= 'Approved'.
Could you please help me to write the condition on API. I have tried query Parameters but no luck.
Thanks in advance.