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

[cagreek]

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

[rareddy]

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

[cagreek]

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

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

[rareddy]

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

[cagreek]

Hi Reddy

Thank you for your suggestions, but updating to the latest community will take some time to do and all our other services will needed to be retested so we need an alternative to that.  We looked at the UDF but having trouble with that too.

We need your expertise for any other alternatives or solutions that you may be aware of.  Is there a way we can store the Token value's in the standalone.xml and be able to use it during run-time when the JDV service is called for a specific environment (DEV, QA, UAT, or PROD)

below is the snippet we've been looking at .   We tried the ENV function but without success .  

Is there a way we can store a the TOKEN in the standalone.xml and be able to use it in the DECLARE STRING VARIABLES.envToken = ENV('pi_token'); section of the code below?

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

Thank you.

[rareddy]

Yes, you can also define in the standalone.xml file, see this thread on subject Variable Substitution in standalone.xml

[cagreek]

Thank you for your quick response.   Just to confirm, will the standalone.xml variable substitution work with EAP 6.4  ?

Regards,

Andy