handle exceptions SQLCode = -981

[Baron]

How is it possible to catch errors arising due to unreachable remote host?

create or replace function myfunction (mysocket varchar(50))
returns long varchar
url 'http://!mysocket/myendpoint'
type 'HTTP:GET'
set 'HTTP(VERSION=1.1)';

Why I cant catch the error here (when the host is unreachable):

begin
select myfunction ('unreachablehost:unreachableport')
exception when others then
SELECT SQLCODE, SQLSTATE
end;

I get the error message: SQLCODE=-981, ODBC 3-Status=HY000"

Is there anyway to catch the error in myfunction?

[VolkerBarth]

Is there anyway to catch the error in myfunction?

I don't think so: In order to handle exceptions, you need to have a code block with an error handler via TRY/CATCH or an EXCEPTIONS clause. In contrast to regular functions and procedures, web client functions and procedures do not have a code block as body but just the URL so there's no "place for that".

The common solution would be to use a wrapper function/procedure that calls the web client function in a code block with exception handling - just like your sample. (And you will probably need such a wrapper function to supply parameters to the web client function and to extract information from its return values anyway...)

---

For certain kind of errors (like SOAP faults), you can use the http option EXCEPTIONS set to OFF (such as "set HTTP(VERSION=1.1; EXCEPTIONS=OFF)' to get responses even when the request failed. But as to the docs, that won't work when the remote host is not available at all, as in your case.

[Baron]

Thanks for your reply!

In my case I go in a loop and call the wrapper function (in my case myfunction) against several remotes.

What I want to achieve, is that even if one of the remotes is not reachable, then I need to continue my loop and jump to the next remote.

Should I understand that this is not possible (in case the host is completely unreachable)?

[Baron]

what about your suggested 'common solution', is it not the same as in my second block?

Why I can't catch the error in my begin/end block?

[Baron]

@volker Barth, thank you very much for the solution.

Now I found the mistake why my second block was not working (I need another set of begin/end) so it works too:

begin
declare strResult long varchar;
begin
set strResult  = (myfunction ('unreachablehost:unreachableport'));
exception when others then
set strResult  = '';
end;
select strResult 
end;