How to use system endpoint with variable

[rflesken]

We 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

[rareddy]

You can define the endpoint in the Data Source definition in the standlone-teiid.xml file for the odata source you defined. See Web Service Data Sources · Teiid Documentation  or look in the "docs/teiid/datasources/ws" directory for sample. When in invokeHTTP method a relative URL is used then it is combined with the endpoint defined in the data source configuration. If the absolute URL is used then it will use what is defined as above. So, the choice is yours.

Also for adding query parameters, use function "QUERYSTRING" from String Functions · Teiid Documentation

Ramesh..

[rflesken]

Hi Ramesh,

Thanks. Can you provide me an example of a relative url being used in combination with a datasource endpoint on EAP server?

I tried below, but that calls the url: '/afgerondeVragenlijsten?vanafDatum=2018-01-18'

BEGIN
DECLARE string VARIABLES.dat = (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);
DECLARE STRING VARIABLES.qp = QUERYSTRING(('/afgerondeVragenlijsten?vanafDatum=' || VARIABLES.dat));
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(action => 'GET', endpoint => VARIABLES.qp, stream => '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

log:

10:14:44,293 DEBUG [org.teiid.COMMAND_LOG] (Worker45_QueryProcessorQueue471) 2pQ88lfRZ3Hn START DATA SRC COMMAND: startTime=2018-01-25 10:14:44.293 requestID=2pQ88lfRZ3Hn.0 sourceCommandID=6 executionID=295 txID=null modelName=a_src_wpb_afgerondevragenlijsten_ows translatorName=ws sessionID=2pQ88lfRZ3Hn principal=dvUser@teiid-security sql=EXEC a_src_wpb_afgerondevragenlijsten_ows.invokeHttp('GET', '/afgerondeVragenlijsten?vanafDatum=2018-01-18', TRUE)

[rareddy]

When you start with "/" at the beginning of the URL, it is not considered relative, remove "/" and try.

[rflesken]

Hi Ramesh,

it worked when i used the full URL on the resource adapter EAP side, and a dynamic QUERYSTRING in the virtual procedure:

example:

EndPoint parameter on Resource adapter on EAP server: https://blabla.com/rest/restservice?vanafDatum=x

Virtual Procedure declare variables.qp:

DECLARE STRING VARIABLES.qp = QUERYSTRING(('?vanafDatum=' || VARIABLES.dat));

Virtual Procedure call:

EXEC a_src_wpb_afgerondevragenlijsten_ows.invokeHttp(action => 'GET', endpoint => VARIABLES.qp, stream => 'TRUE')

Thank you!