4 Replies Latest reply on Jun 22, 2017 12:04 PM by Marco Ardito

    How to configure webservice as a data-source in teiid?

    Kulbhushan Chaskar Master

      Hi,

       

      I need to configure webservice as a data-source in teiid, I need basic information as like we create a connection for mysql database. I am using teiid client api version 9.3.3 and WildFly 10.0.0

       

      Following are the few questions are the show stopper for me.

      1.     Which jdbc driver should I used to create connection for webservice?

      2.     How the VDB content will look like? will it be same as other data-source like mysql?

      3.     How data-source will look like? I am mainly looking here for data-source properties.

       

       

      Thanks,

      Kulbhushan Chaskar.

        • 2. Re: How to configure webservice as a data-source in teiid?
          Marco Ardito Master

          Hi,

          since you are not writing about a public (or known) web service, I guess you need to use a private one. Anyway, I had a similar need in the past because I wished to use a third party json rest service (see thread Need to get data from Mailup email marketing platform ) And maybe I can help you to get the general Idea, in particular if you are not using teiid designer.

           

          1) if your web service has authentication, you have to solve that first, of course

          2) you don't need any jdbc, this is done differently

          3) in my case (see above link) I needed to

          - setup a specific (oauth2) security domain

          - create a  resource adapter and connection definition using that security domain, exposing a jndi URL

          - create a (xml) VDB using that jndi URL

          - from the sql client, use special teiid's sql (complex) syntax to get data from such defined VDB, using calls like invokeHTTP (see Web Services Translator · Teiid Documentation ) and XMLTABLE (see XMLTABLE · Teiid Documentation )

          if you get throught the long thread you'll see many steps but that was also on a quite old teiid version that was then patched to allow that connection ("default token type needs to be "bearer" for OAuth2 access code negotiations.")

           

          After some sample query works, you can then create "views" in your VDB using such complex sql queries, and such views will expose a "tabular" data grid that you can then query using much simpler sql...

           

          I am not using anymore that source, because after all that struggle I discovered that web service api was not useful enough to get what I needed but it worked, although it took a while to get it working, also to get used to the complicated sql teiid uses to transform json objects into tabular data. Hth

           

          Marco

          • 3. Re: How to configure webservice as a data-source in teiid?
            Kulbhushan Chaskar Master

            Hi ,

             

            Using web service as data source  we are able to fetch data  only if API having (@Produces(MediaType.APPLICATION_JSON))  and it's not working for @Produces(MediaType.APPLICATION_XML)

            CODE:

             

             

            String select_par_sql = "SELECT employee.* from "+

            "(call comunicazioni.invokeHTTP(endpoint=>'http://localhost:9229/auth/getemps', action=>'GET', headers=>jsonObject('application/json' as \"Content-Type\", 'application/json' as \"Accept\",0 as  \"Content-length\"))) w, "+ 

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

            "empID string PATH 'employee/id', "+

            "firstname string PATH 'employee/name/first', "+

            "lastname string PATH 'employee/name/last') as employee";

             

            Did't work even if we have modified  content type as XML  .

            STEPS are used to call Web Services as data source:

             

            1) Added resource adapter in as data source in  standalone-teiid xml :

            <resource-adapter id="MUP-webservice">

                                <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>

                                <transaction-support>NoTransaction</transaction-support>

                                <connection-definitions>

                                    <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/mailupDS" enabled="true" use-java-context="true" pool-name="teiid-ws-ds"/>

                                </connection-definitions>

                            </resource-adapter>

             

             

            2) I deployed (success, active) a VDB using the jndi:

             

            <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

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

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

                <model name="comunicazioni">

                    <source name="comunicazioni" translator-name="rest" connection-jndi-name="java:/mailupDS"/>

                </model>

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

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

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

                </translator>

            </vdb>

             

            *********************************************************************************************************************************************************

            I need to use web services as data source for API having  @Produces(MediaType.APPLICATION_XML) .

             

            We are not using teiid designer . we are using Teiid Admin API to implements web services as data source .

            • 4. Re: How to configure webservice as a data-source in teiid?
              Marco Ardito Master
              String select_par_sql = "SELECT employee.* from "+

              "(call comunicazioni.invokeHTTP(endpoint=>'http://localhost:9229/auth/getemps', action=>'GET', headers=>jsonObject('application/json' as \"Content-Type\", 'application/json' as \"Accept\",0 as \"Content-length\"))) w, "+

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

              "empID string PATH 'employee/id', "+

              "firstname string PATH 'employee/name/first', "+

              "lastname string PATH 'employee/name/last') as employee";

              I can't be sure as I can't try your exact setup, but I didn't use a PATH specified like that... mine was something like

               

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

              idRecipient integer PATH ''idRecipient'',

              Email string PATH ''Email'',

              Status string PATH ''Status'',

              Optin_Date string PATH ''Optin_Date'',

              Optout_Date string PATH ''Optout_Date''

              ) mup

               

              so, no forward slashes... maybe? the table name seems to be "employee" so could be not needed specifying field's PATH?

               

              I would try something more like

              "empID string PATH 'id', "+

              "firstname string PATH 'first', "+

              "lastname string PATH 'last'

               

              edit:

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

              Also, you used

              XMLTABLE('/employee' passing JSONTOXML

              while I used

              XMLTABLE('/Items/Items' passing JSONTOXML

               

              ie: I had to double it because due to the json structure served by Mailup it seemed needed... and worked.

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

               

              Anyway before adding the view in my VDB I tried that syntax in SQuirrelSQL until it worked, than used that exactly.

               

              I can't speak about this through API because I never used it, until now... for sure in the VDB I never needed to set something like @Produces(MediaType.APPLICATION_JSON)