14 Replies Latest reply on Nov 30, 2018 1:52 PM by Ramesh Reddy

    Teiid REST call using dynamic authentication

    Aban Mitra Newbie

      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

        • 1. Re: Teiid REST call using dynamic authentication
          Ramesh Reddy Master

          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)

          • 2. Re: Teiid REST call using dynamic authentication
            Aban Mitra Newbie

            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.

            • 3. Re: Teiid REST call using dynamic authentication
              Ramesh Reddy Master

              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.

              1 of 1 people found this helpful
              • 4. Re: Teiid REST call using dynamic authentication
                Aban Mitra Newbie

                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.

                • 5. Re: Teiid REST call using dynamic authentication
                  Ramesh Reddy Master

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

                  • 6. Re: Teiid REST call using dynamic authentication
                    Arijit Mallick Newbie

                    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":"ryan@bitcraft.com.au","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

                    • 7. Re: Teiid REST call using dynamic authentication
                      Arijit Mallick Newbie

                      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

                      • 8. Re: Teiid REST call using dynamic authentication
                        Ramesh Reddy Master

                        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?

                        • 9. Re: Teiid REST call using dynamic authentication
                          Arijit Mallick Newbie

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

                          • 10. Re: Teiid REST call using dynamic authentication
                            Arijit Mallick Newbie

                            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

                            • 11. Re: Teiid REST call using dynamic authentication
                              Ramesh Reddy Master

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

                              • 12. Re: Teiid REST call using dynamic authentication
                                Arijit Mallick Newbie

                                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

                                • 13. Re: Teiid REST call using dynamic authentication
                                  Ramesh Reddy Master

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