4 Replies Latest reply on Jan 25, 2018 10:56 AM by Ruud Fleskens

    How to use system endpoint with variable

    Ruud Fleskens Newbie

      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
        • 1. Re: How to use system endpoint with variable
          Ramesh Reddy Master

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

          • 2. Re: How to use system endpoint with variable
            Ruud Fleskens Newbie

            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)
            • 3. Re: How to use system endpoint with variable
              Ramesh Reddy Master

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

              • 4. Re: How to use system endpoint with variable
                Ruud Fleskens Newbie

                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!