Teiid REST call using dynamic authentication

[aban.mitra]

Hi,

I am new in Teiid. I try to create a REST connection in VDB, and then using java I'll access it to get the data. I am using "teiid-10.2.1-wildfly-server". Using eclipse designer tool I create connection and then create VDB.

In my VDB, model define as:

<model name="listModel">

     <source connection-jndi-name="java:/listModel" name="listModel" translator-name="ws"/>

     <metadata type="DDL"><![CDATA[

          CREATE FOREIGN PROCEDURE invokeHttp (

          OUT result blob NOT NULL RESULT,

          IN action string(4000),

          IN request object,

          IN endpoint string(4000),

          IN stream boolean NOT NULL,

          OUT contentType string(4000),

          IN headers clob

          ) OPTIONS(UPDATECOUNT '1');

     ]]></metadata>

</model>

And then using this model create rest connection to get the data:

<model name="listViewModel" type="VIRTUAL">

<property name="imports" value="listModel"/>

<metadata type="DDL"><![CDATA[

     SET NAMESPACE 'http://teiid.org/rest' AS REST;

     CREATE VIRTUAL PROCEDURE GetAllMembers (

     ) RETURNS

     TABLE (

     response string(4000)

     ) OPTIONS(UPDATECOUNT '1')

     AS

     BEGIN

     DECLARE STRING VARIABLES.qp = '';

     SELECT

     A.response AS response

     FROM

     (EXEC listModel.invokeHttp(action=>'GET',endpoint=>VARIABLES.qp,stream=>'TRUE',headers=>'{"Authorization":"Web XXXXXXXXXXXXXXXXXX"}')) AS f,      XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS response string PATH 'text()') AS A;

     END;

]]></metadata>

</model>

When I am testing through eclipse using fixed token id [in place of 'XXXXXXXXXXXXXXXXXX'], it works fine. But I want to use this from the java program. So how I am going to pass the token id value dynamically?  I mean pass value as a parameter to VDB. And is there any java sample code to call this?

Thanks in advance.

- Aban

[rareddy]

You can define an input parameter to `GetAllMembers` procedure and use that as the token. Calling from java program is exactly the same as you would call a stored procedure using JDBC. See [1]

[1] Using Stored Procedures (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

[aban.mitra]

Thanks for your replay. But as a new in Teiid, can you help me to "how to define an input parameter to the procedure?".

Is it something like:
EXEC listModel.invokeHttp(action=>'GET',endpoint=>VARIABLES.qp,stream=>'TRUE',headers=>'{"Authorization":"Web ?"}')) AS f,XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS response string PATH 'text()') AS A

How to define "?" variable? is it like:

CREATE VIRTUAL PROCEDURE GetAllMembers (VARIABLES.tokenValue

    ) RETURNS

    TABLE (

    response string(4000)

    ) OPTIONS(UPDATECOUNT '1')

    AS

    BEGIN

    DECLARE STRING VARIABLES.qp = '';

    SELECT

    A.response AS response

    FROM

    (EXEC listModel.invokeHttp(action=>'GET',endpoint=>VARIABLES.qp,stream=>'TRUE',headers=>'{"Authorization":"Web VARIABLES.tokenValue"}')) AS f,      XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS response string PATH 'text()') AS A;

    END;

Note: VARIABLES.tokenValue is using within the double quotes.

And in Java code is it like:

ConnectionImpl connection = getConnection();

CallableStatementImpl stmt = connection.prepareCall("{call GetAllMembers(?)}");

stmt.setString(1, "<token_id>");

ResultSetImpl rs = stmt.getResultSet();

Your assistance is greatly appreciated.

[rareddy]

try something like

CREATE VIRTUAL PROCEDURE GetAllMembers (string authToken) RETURNS TABLE (response string(4000)) 
    OPTIONS (UPDATECOUNT '1') AS
BEGIN
      DECLARE STRING VARIABLES.qp = '';
      SELECT A.response AS response
      FROM (EXEC listModel.invokeHttp(action=>'GET',endpoint=>VARIABLES.qp,stream=>'TRUE',headers=>JSONOBJECT(GetAllMembers.authToken AS "Authorization"))) AS f, XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS response string PATH 'text()') AS A;
END;

Then your java looks fine.

[aban.mitra]

Thanks a lot. Issue resolved. I have done in the following way:

CREATE VIRTUAL PROCEDURE GetAllMembers (

IN tokenId string NOT NULL

) RETURNS

TABLE ..... (EXEC allMemberSrc.invokeHttp(action => 'GET', endpoint => VARIABLES.qp, stream => 'TRUE', headers => JSONOBJECT(<view_name>.GetAllMembers.tokenId AS xyzAuthorization))) AS f, XMLTABLE('/response' PASSING JSONTOXML('response', f.result) COLUMNS.....

And in java:

String tokenId="xxxxxxx";

ConnectionImpl connection = getRESTConnection();

String query = "select * from ( exec <view_name>.GetAllMembers(' "+ tokenId +" ')) as X_X";

And then execute the statement to get the result.

[rareddy]

Great to hear. Curious to know what kind of usecase you are solving?

[arijit.inn]

Hi Ramesh,

I am working with Aban on this where we have a token based auth, and to get token we need to pass username name password

our first call is like https://.../services/Authentication/Login/Web?UserName=test&Password=test

returning

{...,"TokenId":"d40a4cd4-eed2-4e7a-9cc2-6dae404b846a",...}

they we call the actual rest call to get data https://.../services/Views?Type=viewbyname&viewname=All&viewentityname=Persons with the token as header to the call as AptifyAuthorization=d40a4cd4-eed2-4e7a-9cc2-6dae404b846a

returning JASON

{"count":"4856", "viewId":"1272", "columnwidthresize":"FALSE", "result":[{"MemberType":"Affiliate","ID":133434,"FirstName":"BitCraft","LastName":"BitCraft","Company":null,"Title":"","AddressLine1":null,"City":null,"State":null,"ZipCode":null,"Country":null,"PhoneAreaCode":null,"Phone":null,"PhoneExtension":null,"Email1":"[e-mail]","PrimaryFunction":null,"CompanyMemberType":null,"StatusName":"Active ","Specialisation__c":"","row_number":1},...

]}

note with change in parameter Type=viewbyname&viewname=All&viewentityname=Persons the data set will changes

this is similar to Need to get data from Mailup email marketing platform

The VDB would be like

<vdb name="WEBCALL" version="1">

    <description>Shows how to call Web Services</description>

    <model name="WEBCALL">

        <source name="WEBCALL" translator-name="rest" connection-jndi-name="java:/WEBCALLDS"/>

    </model>

    <translator name="rest" type="ws">

                <property name="DefaultBinding" value="HTTP"/>

                <property name="DefaultServiceMode" value="MESSAGE"/>

    </translator>

    <model name="MODELS" type="VIRTUAL">

     <metadata type="DDL"><![CDATA[

                CREATE VIEW PERSON(

                    MemberType string,

                    ID string

                ) AS

               select person.* from

               (call APTIFY.invokeHTTP(

                   endpoint=>'https://.../services/Views?Type=viewbyname&viewname=All&viewentityname=Persons ',

                   action=>'GET',

                   headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length"))

               ) w, 

               XMLTABLE('/result' passing JSONTOXML('result', w.result) columns

                    MemberType string PATH 'MemberType',

                    ID string PATH 'ID'

          ) AS person;

   ]]> </metadata>

     </model>

</vdb>

i am also trying from code as

String url = "jdbc:teiid:WEBCALL@mm://localhost:31000;version=1";

String sql = "select person.* from " +

        "(call WEBCALL.invokeHTTP( " +

        //"    endpoint=> 'EndPoint', " +

        "    action=>'GET', " +

        "    headers=>jsonObject('application/json' as \"Content-Type\", 'application/json' as \"Accept\",0 as  \"Content-length\"))          " +

        "    ) w, " +

        "    XMLTABLE('/' passing JSONTOXML('/', w.result) columns " +

        "        UserId string PATH 'UserId', " +

        "        UserName string PATH 'UserName' " +

        " ) AS person ";

This works when i am calling for token but gives error when calling for actual data as TEIID30504 WEBCALL: TEIID15005 Error Calling HTTP Service - 404 Not Found because I cant pass token dynamically

[arijit.inn]

The outcome we want is to have VDB views that we can query and join with the views

i.e. view from WebService, CSV, DB's can be joined and queried upon like standard SQL's

[rareddy]

Are you asking that you want to pass the userid and password and automatically grab the Token to make the further calls?  Or you defined the call right now as procedure and you want to define it as View as shown above with token in there? such that you can join with others?

[arijit.inn]

yes, I want to pass the userid and password and automatically grab the Token to make the further calls

[arijit.inn]

Once we can pass the token to other rest call, ideally the result (JSON) of those would be wrapped as a view (as your suggestion to the other post) and exposed to application to query on

Internally in VDB/ teiid config it could be any layer of abstraction (JSON to XML to proc/ view) as long as the final is a view in a VDB model for use through Teiid JDBC connection like

String url = "jdbc:teiid:WEBCALL@mm://localhost:31000;version=1";

String sql = "select * from from person p,  transaction t where p.x = t.y ....

From the application point, we want to leverage teiid as the  data federation point where the application would have only one database (where VDB's as schema) as source which is teiid and application queries/ joins etc is to teiid only 

Hope I am making sense

[rareddy]

Yes, it does make sense, that is what Teiid does

Basically what you want to do is start out with another virtual procedure that takes userid, and password and fetch the token first and then use that token to pass into the procedure that takes the token as defined above earlier posts. So, you have two virtual procedures one stacked on another.

Lets say you defined another procedure called

CREATE VIRTUAL PROCEUDRE GetAllMemers(userid string, password string) AS
BEGIN
...
END

Once you have then you can define a view as

CREATE VIEW Person(
    MemberType string OPTIONS(UPDATABLE false),
    Id string primary key OPTIONS(UPDATABLE false),
    username string OPTIONS(UPDATABLE false, SELECTABLE false),
    password string OPTIONS(UPDATABLE false, SELECTABLE false)
  ACCESSPATTERN(username, password)
) AS select * from GetAllMembers;

Now when you issue a query like "select MemberType, Id from Person" the engine will force you to supply a query like "select MemberType, Id from Person where username = 'foo' and password='blah'" then it will convert those where clause values into to input parameters on the GetAllMembers procedure.

Obviously, it goes without saying that supplying the usernames, password like in SQL queries in not a good practice, typically these should be handled through security layers, but that is lot more work to develop security domains where you can passthrough the credentials from logged in user which way beyond this forum post.

Ramesh..

[arijit.inn]

Thanks Ramesh, I will try this and get back

Basically we will have

1. getTokenProc() returning token string - hardcode user/pass until building security layer and this proc will call WS to get token

2. getperson() returning table structure - this proc will call getTokenProc()  to get token and use this token to call WS to get person

3. create PERSON view on the same return data/ table structure of getperson() and will call getperson()

so for every new WS call, point 2 and 3 is defined and all these are put under the same VDB model

[rareddy]

I would do getTokenProc be part of getPerson so that when you are creating the view it is a bit easier.

[pmthirumaran]

Hi Aban,

Do you have any sample project for this? I want to implement the same based on the environment I have to change the RESTFul service endpoint, header authorization token. It will be helpful for us. How to create JAVA class and how to call it?

Thanks in advance.