Question Re: Exception handling in a Web Client function? in Technology Q&A

Exception handling in a Web Client function?

Former Member — Thu, 11 Jun 2020 13:45:44 GMT

I would be grateful for some pointers:

I am trying to use the Web Service feature of SQL Anywhere 16.0.0.2076 to POST to a URL, however I am finding that often, even though the URL is correct, I get an error that states that it cannot connect to the URL. I know the URL is correct as if I try again, it works correctly. What I would like to be able to do is either handle the error and automatically retry, or find a way to use Exception Handling to do it myself.

If I set up a function with an invalid URL to deliberately generate the same error, such as:

CREATE FUNCTION "spaceman"."WebServiceTest"(in "iData" long varchar) 
returns xml
url 'http://www.mynonexistentwebservice.com'
type 'HTTP:POST:text/xml'

and then

select WebServiceTest('Hello')

I get:

Unable to connect to the remote host specified by
'http://www.mynonexistentwebservice.com'
SQLCODE=-981, ODBC 3 State="HY000"

How can I handle this error myself? I cannot put exception handling in the original function, and if I call this function with another procedure that contains the exception handling, it is ignored - I assume as it is not this second procedure that is causing the issue:

Create procedure mysecondproc()
begin
select WebServiceTest('Hello');
    exception
    when others then
    message(string(SQLCODE,'  - ',SQLState))
END

Many thanks indeed

Rip

Re: Exception handling in a Web Client function?

VolkerBarth — Thu, 11 Jun 2020 14:34:25 GMT

You are right, you cannot add exception handling to the web client function as it has no body. So exception handling must be done within the wrapper function/procedure. I guess this does not work as expected in your sample as the procedure just returns the result to the caller, and the error just appears during the opening of the result set. Try to consume the result set within the procedure itself (say, by using a local variable of type xml to store the function's return value, and then selecting the local variable as result set. Then the exception should by caught within the procedure itself.

FWIW, you might try Web client logging to find out why the web request does fail sometimes.

Re: Exception handling in a Web Client function?

Breck_Carter — Thu, 11 Jun 2020 14:45:49 GMT

The problem is that the "select WebServiceTest('Hello');" effectively returns from the procedure immediately, without executing any code that follows.

The solution is to use SET local (or SELECT INTO local) to call the function, then SELECT local to return the successful value... an error in the SET local or SELECT INTO local will be captureed.

I had to code this kludge in many locations throughout Foxhound.

Create procedure mysecondproc2()
begin
declare x XML;
SET x = WebServiceTest('Hello');
SELECT x;
    exception
    when others then
    message(string(SQLCODE,'  - ',SQLState))
END;

CALL mysecondproc2();

-981  - WW050

Re: Exception handling in a Web Client function?

Breck_Carter — Thu, 11 Jun 2020 14:47:14 GMT

In other words, what Volker said 🙂

Re: Exception handling in a Web Client function?

VolkerBarth — Thu, 11 Jun 2020 15:05:27 GMT

Just to add: Here's an explanation why SQL Anywhere handles result sets in procedures that way, quite different from other database products...

Re: Exception handling in a Web Client function?

Former Member — Thu, 11 Jun 2020 16:48:16 GMT

Thank you Breck and Volker, that's just the answer I needed!