-
2. Re: How to configure webservice as a data-source in teiid?
m.ardito Jun 21, 2017 7:42 AM (in response to kulbhushanc)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?
kulbhushanc Jun 22, 2017 10:09 AM (in response to m.ardito)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?
m.ardito Jun 22, 2017 12:04 PM (in response to kulbhushanc)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)