topic Re: REST Connector header query parameter in Connectivity & Data Prep

REST Connector header query parameter

klikgevoel — Thu, 23 Dec 2021 14:25:13 GMT

Hi all,

I'm trying to fetch data from an URL, which works fine so far. However, I want to collect only data that is needed, and not the whole shabang. For that to work I want to collect the data that satisfies a certain condition within a header. I already tried it within the REST Connector settings table by defining the header name and parameter, but with no luck. My guess is that I need to define it within a variable and pass it into the URL query string. A sample of my code is given below.

The header is named: Bestedingscategorieen and the condition is: T001112. So I only want to collect that data.

Main:
LET vTopNum 		= 9999;
LET vSkipNum 		= 0;
LET vCounterNum 	= 2;

LIB CONNECT TO 'CBS_CPI_CONNECTOR';

LET vTop 		= vTopNum;
LET vSkip 		= vSkipNum;
LET vCounter 	= vCounterNum;

DO WHILE vCounter > 1
RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"ID",
		"Bestedingscategorieen",
		"Perioden",
		"CPI_1",
		"CPIAfgeleid_2",
		"JaarmutatieCPI_5",
		"JaarmutatieCPIAfgeleid_6",
		"__FK_value"
	FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION
		( Url 
			"https://opendata.cbs.nl/ODataApi/odata/83131NED/TypedDataSet?$top=$(vTop)&$skip=$(vSkip)"
            );


LET vCounter = NoOfRows('RestConnectorMasterTable');

  temp_cbs_load_table:
  LOAD
  *,
  'Reload' AS Source
  RESIDENT RestConnectorMasterTable;
  
  DROP TABLE RestConnectorMasterTable;

  TRACE 'Skip = '		$(vSkip) ;
  TRACE 'Counter =' 	$(vCounter);

  LET vSkip = $(vSkip) + $(vTop);

LOOP

Thanks in advance

Re: REST Connector header query parameter

fosuzuki — Wed, 10 Apr 2019 23:57:59 GMT

Did you try setting the header parameter inside the WITH CONNECTION keyword?

From Help:

WITH CONNECTION (
Url "https://localhost:81/northwind2.xml",
QUERY "type" "XML",
QUERY "size" "",
HTTPHEADER "auth_type" "token",
HTTPHEADER "token" "1234123123123",
BODY "Post this");

Re: REST Connector header query parameter

klikgevoel — Fri, 12 Apr 2019 09:13:56 GMT

I've tried all of them within the URL connection statement. However, it doesn't work. I managed to fix it by editing the URL with some query parameters inside of it, i.e.

https://opendata.cbs.nl/ODataApi/odata/83131NED/TypedDataSet?$filter=Bestedingscategorieen%20eq%20%27T001112%20%20%27&$skip=273

As of now I only fetch the category I want.