How to use system endpoint with variable
rflesken Jan 24, 2018 9:19 AMWe use an invokeHttp procedure to get OData result from an external REST service and create a table in DV.
At this moment the endpoint for this service is configured in the source model that invokes the webservice.
It is called now with this procedure:
BEGIN DECLARE STRING VARIABLES.qp = ''; SELECT A."d:ID" AS "d:ID", A."d:VolledigeNaam" AS "d:VolledigeNaam", A."d:Email" AS "d:Email", A."d:PersoneelsNummer" AS "d:PersoneelsNummer", A."d:Medewerker_OrganisatieOnderdeel" AS "d:Medewerker_OrganisatieOnderdeel", A."d:Medewerker_Gebruiker" AS "d:Medewerker_Gebruiker", A."d:Mederwerker_Functie" AS "d:Mederwerker_Functie" FROM (EXEC a_src_alg_medewerker_ows.invokeHttp(action => 'GET', endpoint => VARIABLES.qp, stream => 'TRUE')) AS f, XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom', 'http://schemas.microsoft.com/ado/2007/08/dataservices' AS d, 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' AS m), '/feed/entry/content/m:properties' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS "d:ID" string PATH 'd:ID/text()', "d:VolledigeNaam" string PATH 'd:VolledigeNaam/text()', "d:Email" string PATH 'd:Email/text()', "d:PersoneelsNummer" string PATH 'd:PersoneelsNummer/text()', "d:Medewerker_OrganisatieOnderdeel" string PATH 'd:Medewerker_OrganisatieOnderdeel/text()', "d:Medewerker_Gebruiker" string PATH 'd:Medewerker_Gebruiker/text()', "d:Mederwerker_Functie" string PATH 'd:Mederwerker_Functie/text()') AS A; END
The endpoint is defined with
endpoint => VARIABLES.qp
So when VARIABLES.qp is empty, then the endpoint parameter from datasource adapter on the server is being used.
Is there a way to concat this server endpoint a procedure variable?
This doesnt work:
invokeHttp('GET', null, endpoint || Variables.parameter, 'TRUE')
Also tihs doesnt work:
invokeHttp('GET', null, sourcemodel.endpoint || variables.parameter, 'TRUE')
We don't want to declare the URL in the procedure itself but on server level. Is there a way to do this?
Right now we can only declare the URL in the procedure it self to resolve:
BEGIN DECLARE string VARIABLES.endpoint = 'https://anonimised.com/rest/afgerondeVragenlijsten?vanafDatum='; DECLARE string VARIABLES.epc = (VARIABLES.endpoint || (SELECT FORMATDATE(TIMESTAMPADD(SQL_TSI_DAY, -1, MAX(b_vbl_edwh.STG_MDX_HSE_WPB_AFGE_VR_LIJST.ETL_STG_DATUM)), 'YYYY-MM-dd') FROM b_vbl_edwh.STG_MDX_HSE_WPB_AFGE_VR_LIJST)); SELECT A.RevisieNummerVragenlijst AS RevisieNummerVragenlijst, A.Vragenlijst_Onderwerp AS Vragenlijst_Onderwerp, A.Vraag AS Vraag, A.Vragenlijst_Bezoek AS Vragenlijst_Bezoek, A.Antwoord AS Antwoord FROM (EXEC a_src_wpb_afgerondevragenlijsten_ows.invokeHttp('GET', null, VARIABLES.epc, 'TRUE')) AS f, XMLTABLE('/response/response' PASSING JSONTOXML('response', f.result) COLUMNS RevisieNummerVragenlijst string PATH 'RevisieNummerVragenlijst/text()', Vragenlijst_Onderwerp string PATH 'Vragenlijst_Onderwerp/text()', Vraag string PATH 'Vraag/text()', Vragenlijst_Bezoek string PATH 'Vragenlijst_Bezoek/text()', Antwoord string PATH 'Antwoord/text()') AS A; END