Consume external WebService

[Former Member]

Hello!

I need to query an external webservice to get a value, how can I do this? thank you!

DRauber

[graeme_perrow]

You can create a procedure that contains a URL. Calling the procedure returns a result set that contains the information returned from the web service. Here's an example:

create procedure consume_service() url 'http://example.com/service' type 'http:get';
select * from consume_service() with (attribute long varchar, value long varchar);

The result set contains:

• a row with Attribute='Status'; value contains the HTTP status (eg. HTTP/1.1 200 OK)
• a row with Attribute='Body'; value contains the body of the response
• one row for each HTTP header sent by the HTTP server where Attribute=the header type and Value=the value of the header

There are options for using HTTPS instead of HTTP, using a proxy server, using SOAP, providing parameters, etc. The v17 documentation for this version of the CREATE PROCEDURE statement is here.

[Former Member]

Assuming by external you mean external to the database server, then, the SQL Anywhere database server can consume web content and services.

With SQL Anywhere you can create "Web Service" procedures and functions that can request content from an external webservice that you have rights/permissons to. This can be either an HTTP or SOAP request. The take on a variation of the {CREATE|ALTER} {FUNCTION|PROCEDURE} statements which show up in the index as "CREATE .... [Web service]" entries.

After the request is satisfied you can also access that information using many XML- (OpenXML), HTTP-, and HTML-aware functions as we as the stable of normal functions available in the database to parse and extract the specific informaion you want.

You will have to check for this on DCX a little later (currently, and temporarily, unavailable). Alternatively you can always just install the documentation locally and refer to that as well.

The above is the most direct way of doing that. You may also want to familiarize yourself with our "external enviroments" (PHP, PERL, JAVA, CLR, ...) which might open up other approaches or allow for more involved web requests or non-standard content.

HTH

[Former Member]

Thank you! I received http://deltainfws.com:8080/dltadm/DltAdmService?Tester URL to test the operation. I need to pass three arguments and get the answer a string. If they want to see details, can pass the arguments: 03703992000101 SICONFI 20160129A

As you can see, it is a request / response SOAP

How i can create function to get this?

Thanks

[Michael_Fischer]

You can directly use a complex SOAP Service out of the database but in real life, it is definitely more complicated than just specifiying the web procedure call. Basically, it involves building a soap request envelope, sending it to a "raw" web procedure call" and then parse the returned envelope. We have successfully done that a lot of times but as I said, it's nothing you describe in a minute. DRauber, you are not Germany-based, are you?

[VolkerBarth]

While I would certainly agree that it's usually more complicated "in real life", the mentioned SOAP service consisting of a request with three simple arguments and a string as response seems not too complex, IMHO, and could be handled without the need to build the SOAP envelope in code...

You might have a look at the "soap.globalweather.sql" and "soap.gov.weather.sql" samples in the SQL Anywhere HTTP samples directory.