REST - queryHeaders as variable?

[robert_mika]

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;"

[pentaxadmin]

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

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...mykeyxxx;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]);

next

DROP TABLE RestConnectorMasterTable;

[pentaxadmin]

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)&k...

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;

next

[prees959]

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

eg

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

Is this possible?

Many thanks for any advice!

Phil

[prees959]

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

[pawwy1415]

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.