3 Replies Latest reply on Nov 26, 2018 11:16 AM by Ramesh Reddy

    JBOSS TEIID Designer view with Token in header properties - how to use DEV/UAT/QA/Production environment specific Tokens in View Model

    Andy B Newbie

      Hello,

       

      Environment EAP 6.4, JBoss designer studio 10.3, Teiid designer version 11.0.1.Final

       

      Looking for some help on configuring various tokens needed an a View Model that is used in one of our VDB's.  

       

      There was a thread that discussed a similar question but was not specific to what we need so i am looking for assistance in being able to pull Token information for a specific server.   For example if its a Development server needs the token information taken from a property file so that the Token can be used in the View Model.   Each server, Dev/QA/UAT/Prod will have its own token so when we deploy our VDB service to the various servers, the view model "code" needs to pull the token for that server.    I tried using the ENV function as shown below but did not work for me.

       

      BEGIN

        DECLARE STRING VARIABLES.token = ENV('SPM_TOKEN');

        SELECT ...

      headers => CONCAT(CONCAT('{"Authorization":"', VARIABLES.token), '"}'

       

      is there a different way to configure "server" specific property files like tokens that can be used within the view model?  and if so, how do we access the variable as seen in the code above?

       

      Is there something we can configure using the ADMIN configurations found on the localhost:9990/console/App.html#home     URL?

       

       

       

      Reviewed this thread but could not get it to work for me

      Re: Environment variable in view model transformation

        • 1. Re: JBOSS TEIID Designer view with Token in header properties - how to use DEV/UAT/QA/Production environment specific Tokens in View Model
          Ramesh Reddy Master

          That looks correct. However by default ENV function is turned off for security reasons, you need to enable it. See System Functions · GitBook

          • 2. Re: JBOSS TEIID Designer view with Token in header properties - how to use DEV/UAT/QA/Production environment specific Tokens in View Model
            Andy B Newbie

            Thank you Remesh,

             

            we are using EAP 6.4.    Is the option referred to in the System Functions link you provided added to the "STANDALONE.XML" file  ?

             

            This is the area in the standalone.xml that references teiid subsystem.   When i try this and restart the server.  it does not seem to pull the information from the environment variables set on windows.

             

             

             

               <subsystem xmlns="urn:jboss:domain:teiid:1.0">

             

               <allow-env-function>true</allow-env-function>

               <async-thread-pool>teiid-async</async-thread-pool>

               <policy-decider-module>org.jboss.teiid</policy-decider-module>

               <resultset-cache infinispan-container="teiid-cache"/>

               <preparedplan-cache infinispan-container="teiid-cache"/>

               <transport name="embedded"/>

               <transport name="odata">

               <authentication security-domain="teiid-security"/>

               </transport>

               <transport name="jdbc" socket-binding="teiid-jdbc" protocol="teiid">

               <authentication security-domain="teiid-security"/>

               </transport>

               <transport name="odbc" socket-binding="teiid-odbc" protocol="pg">

               <authentication security-domain="teiid-security"/>

               <ssl mode="disabled"/>

               </transport>

               <translator name="jdbc-simple" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="jdbc-ansi" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="access" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="db2" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="derby" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="h2" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="hsql" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="informix" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="metamatrix" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="mysql" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="mysql5" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="oracle" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="postgresql" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="greenplum" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="sqlserver" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="sybase" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="sybaseiq" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="teiid" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="teradata" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="modeshape" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="ingres" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="ingres93" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="intersystems-cache" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="netezza" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="file" module="org.jboss.teiid.translator.file"/>

               <translator name="ldap" module="org.jboss.teiid.translator.ldap"/>

               <translator name="loopback" module="org.jboss.teiid.translator.loopback"/>

               <translator name="olap" module="org.jboss.teiid.translator.olap"/>

               <translator name="ws" module="org.jboss.teiid.translator.ws"/>

               <translator name="salesforce" module="org.jboss.teiid.translator.salesforce"/>

               <translator name="salesforce-34" module="org.jboss.teiid.translator.salesforce" slot="34"/>

               <translator name="hive" module="org.jboss.teiid.translator.hive"/>

               <translator name="google-spreadsheet" module="org.jboss.teiid.translator.google"/>

               <translator name="odata" module="org.jboss.teiid.translator.odata"/>

               <translator name="sap-gateway" module="org.jboss.teiid.translator.odata"/>

               <translator name="sap-nw-gateway" module="org.jboss.teiid.translator.odata"/>

               <translator name="mongodb" module="org.jboss.teiid.translator.mongodb"/>

               <translator name="cassandra" module="org.jboss.teiid.translator.cassandra"/>

               <translator name="accumulo" module="org.jboss.teiid.translator.accumulo"/>

               <translator name="solr" module="org.jboss.teiid.translator.solr"/>

               <translator name="excel" module="org.jboss.teiid.translator.excel"/>

               <translator name="impala" module="org.jboss.teiid.translator.hive"/>

               <translator name="prestodb" module="org.jboss.teiid.translator.prestodb"/>

               <translator name="hbase" module="org.jboss.teiid.translator.hbase"/>

               <translator name="hana" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="vertica" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="actian-vector" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="osisoft-pi" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="odata4" module="org.jboss.teiid.translator.odata4"/>

               <translator name="redshift" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="jpa2" module="org.jboss.teiid.translator.jpa"/>

               <translator name="ucanaccess" module="org.jboss.teiid.translator.jdbc"/>

               <translator name="simpledb" module="org.jboss.teiid.translator.simpledb"/>

               </subsystem>

               </profile>

             

             

             

             

             

            Steps 1:   Setup the token key in system environment settings for windows.

             

             

             

             

             

            2: Adjust view model code to use the pt_token in the View Model Variables.

             

             

             

            BEGIN

            DECLARE STRING VARIABLES.qp = QUERYSTRING('https://qa-pmtinsider.na.global.prv/api/v1/user/query', pmtinsider.getcustomerinfo.externalMid AS externalMid);

            DECLARE STRING VARIABLES.envToken = ENV('pi_token');

            SELECT A.userTypeCode AS userTypeCode, A.dateDisplayPreference AS dateDisplayPreference, A.firstName AS firstName, A.lastName AS lastName, A.internal AS internal, A.objArr AS objArr, A.lastLoginDate AS lastLoginDate, A.hyphenated AS hyphenated, A.userName AS userName, A.userId AS userId, A.email AS email, A.statusCode AS statusCode FROM (EXEC pmtinsidersource.invokeHttp(action => 'GET', endpoint => VARIABLES.qp, stream => 'TRUE', headers => CONCAT(CONCAT('{"Authorization":"', envToken), '"}'))) AS f, XMLTABLE('/response/response' PASSING JSONTOXML('response', f.result) COLUMNS userTypeCode string PATH 'userTypeCode/text()', dateDisplayPreference string PATH 'dateDisplayPreference/text()', firstName string PATH 'firstName/text()', lastName string PATH 'lastName/text()', internal string PATH 'internal/text()', objArr string PATH 'objArr/text()', lastLoginDate string PATH 'lastLoginDate/text()', hyphenated string PATH 'hyphenated/text()', userName string PATH 'userName/text()', userId string PATH 'userId/text()', email string PATH 'email/text()', statusCode string PATH 'statusCode/text()') AS A;

            END

             

             

            3. Viewing the data by right clicking the model from the id to enter the parameter value, we get these results in the console.   Seems like the environment variable we setup is not being pulled in for the execution.   

            If i hard code the value in the view, everything works but we need to be able to pass in a value

             

            Do you see any issue on my setup?

             

             

             

             

            PROCESS PLAN =

            ProjectNode(0) output=[userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode] [userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode]

              JoinNode(1) [NESTED TABLE JOIN] [CROSS JOIN] output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

                ProjectNode(2) output=[result] [pmtinsidersource.invokeHttp.result]

                  AccessNode(3) output=[pmtinsidersource.invokeHttp.result, pmtinsidersource.invokeHttp.contentType] EXEC pmtinsidersource.invokeHttp('GET', qp, TRUE, convert(CONCAT(CONCAT('{"Authorization":"', envToken), '"}'), clob))

                XMLTableNode(4) output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

             

             

            ============================================================================

             

             

            ----------------------------------------------------------------------------

            OPTIMIZATION COMPLETE:

            PROCESSOR PLAN:

            ProjectNode(0) output=[userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode] [userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode]

              JoinNode(1) [NESTED TABLE JOIN] [CROSS JOIN] output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

                ProjectNode(2) output=[result] [pmtinsidersource.invokeHttp.result]

                  AccessNode(3) output=[pmtinsidersource.invokeHttp.result, pmtinsidersource.invokeHttp.contentType] EXEC pmtinsidersource.invokeHttp('GET', qp, TRUE, convert(CONCAT(CONCAT('{"Authorization":"', envToken), '"}'), clob))

                XMLTableNode(4) output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

             

             

            ============================================================================

            ASSIGNMENT

            DECLARE STRING VARIABLES.qp = QUERYSTRING('https://qa-pmtinsider.na.global.prv/api/v1/user/query', externalMid AS externalMid);

            ASSIGNMENT

            DECLARE STRING VARIABLES.envToken = null;

            COMMAND STATEMENT:

            SELECT userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode FROM (EXEC pmtinsidersource.invokeHttp('GET', qp, TRUE, convert(CONCAT(CONCAT('{"Authorization":"', envToken), '"}'), clob))) AS f CROSS JOIN XMLTABLE('/response/response' PASSING JSONTOXML('response', result) COLUMNS userTypeCode string PATH 'userTypeCode/text()', dateDisplayPreference string PATH 'dateDisplayPreference/text()', firstName string PATH 'firstName/text()', lastName string PATH 'lastName/text()', internal string PATH 'internal/text()', objArr string PATH 'objArr/text()', lastLoginDate string PATH 'lastLoginDate/text()', hyphenated string PATH 'hyphenated/text()', userName string PATH 'userName/text()', userId string PATH 'userId/text()', email string PATH 'email/text()', statusCode string PATH 'statusCode/text()') AS A;

            STATEMENT COMMAND PROCESS PLAN:

            ProjectNode(0) output=[userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode] [userTypeCode AS userTypeCode, dateDisplayPreference AS dateDisplayPreference, firstName AS firstName, lastName AS lastName, internal AS internal, objArr AS objArr, lastLoginDate AS lastLoginDate, hyphenated AS hyphenated, userName AS userName, userId AS userId, email AS email, statusCode AS statusCode]

              JoinNode(1) [NESTED TABLE JOIN] [CROSS JOIN] output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

                ProjectNode(2) output=[result] [pmtinsidersource.invokeHttp.result]

                  AccessNode(3) output=[pmtinsidersource.invokeHttp.result, pmtinsidersource.invokeHttp.contentType] EXEC pmtinsidersource.invokeHttp('GET', qp, TRUE, convert(CONCAT(CONCAT('{"Authorization":"', envToken), '"}'), clob))

                XMLTableNode(4) output=[userTypeCode, dateDisplayPreference, firstName, lastName, internal, objArr, lastLoginDate, hyphenated, userName, userId, email, statusCode]

             

             

             

             

            ####################################################

            ####################################################

            PROCEDURE PLAN :ProcedurePlan:

            PROGRAM counter 0

            0:  ASSIGNMENT INSTRUCTION: VARIABLES.qp

            1:  ASSIGNMENT INSTRUCTION: VARIABLES.envToken

            2:  CREATE CURSOR RESULTSET INSTRUCTION - null

             

             

            ####################################################

            • 3. Re: JBOSS TEIID Designer view with Token in header properties - how to use DEV/UAT/QA/Production environment specific Tokens in View Model
              Ramesh Reddy Master

              Andy,

               

              I suspect you are hitting the issue [1], I am not sure it is been patched in the release you are working with. You have three options

               

              1) If you are Redhat customer you can raise a ticket with them.

              2) Move to use latest community release of Teiid

              3) You can write a simple UDF function, that mimics ENV and deploy and replace your code using that.

               

              [1]  [TEIID-5308] ENV() function doesn't resolve environment variables but system properties - JBoss Issue Tracker