All Forums Extensibility
andydoorey 35 posts Joined 05/09
17 Oct 2013
Calling Web Services

Does anyone know if there is a way of calling a web service from teradata?  
I'm looking at replacing a process that currently uses oracle.  We have stored procedures created which build some XML and then call a web service.  This uses the oracle package UTL_HTTP.
I can build the XML using XML Services, but I'm not aware I can send it to a web service from within teradata.  Can anyone let me know if this is possible.  Otherwise we'll have to set up something outside of Teradata to select the data and then send it on to the web service.
Thanks

tomnolan 594 posts Joined 01/08
17 Oct 2013

The Teradata Database supports Java Stored Procedures. You can create a Java Stored Procedure that calls a web service.

andydoorey 35 posts Joined 05/09
18 Oct 2013

I don't suppose anyone has done this and has an example do they?

ММ186094 1 post Joined 01/14
05 Nov 2014

I see that the post is quite old, but did you actually succeed in creating something that works ? 
At the moment I am working on a Java UDF that calls the Google Geocoding API. When I run it from Eclipse or the terminal everything works. So I am sure that my Java code is correct. The issue I am facing is that after deploying the jar to Teradata the Host becomes unreachable. I tried to access it with the hostname and the ip and both failed. 
Any ideas ? 

tomnolan 594 posts Joined 01/08
06 Nov 2014

Many organizations have their Teradata Database system behind a firewall so that neither inbound nor outbound Internet connections are possible.
 
The Google Geocoding API uses HTTP or HTTPS to hostname maps.googleapis.com, so in order for your Java UDF to contact that host, your firewall would need to permit HTTP on port 80 (or HTTPS on port 443) to that hostname.
 
Another point that you need to consider is that the Google Geocoding API has rate-limiting. You are limited to a certain number of API calls per day, and a certain number of API calls per second. A Java UDF that is invoked for thousands or millions of rows will easily exceed those rate limits imposed by Google, and then Google will block subsequent API calls.
 

Raja_KT 1246 posts Joined 07/09
08 Nov 2014

Did you check with MyBatis? An instant web service calls from stored proc. You can see much benefits with xml dealings and wsdl.......

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sbhanda3 2 posts Joined 04/15
06 Apr 2015

Hi ММ186094,
Could you please share the steps to build the Java UDF which did the web service call. If possible, please share the code as well.
Thanks,
Sachin

babar.sattar 3 posts Joined 04/15
24 Oct 2015

Creating something within Teradata to create and XML for the sole purpose of calling a webservice and send the XML as payload is architecturally a very strange process.
You should design your web services layer on top of your data warehouse (teradata) so you can register it with a SOA broker. Then the webservice you exposed via this WS layer selects data from Teradata as needed and responds to any requester as needed. You can then extend this in future to a full fledged SOA architecture. But I'd suggest you think in those lines rather than using Java UDFs / SPRs to do something that is not meant to be done within an RDBMS architecturally.

Regards,
Babar

You must sign in to leave a comment.