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)

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.

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

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

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

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?

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

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

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

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

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

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.