Calling Web Services

Extensibility
Enthusiast

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

Tags (2)
7 REPLIES
Teradata Employee

Re: Calling Web Services

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

Enthusiast

Re: Calling Web Services

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

Teradata Employee

Re: Calling Web Services

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 ? 

Teradata Employee

Re: Calling Web Services

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.

Enthusiast

Re: Calling Web Services

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

Re: Calling Web Services

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

Re: Calling Web Services

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.