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 · Accepted solution]

call the wrapper function (in my case myfunction)

Hm, in my terminology, myfunction is the web client function (i.e. the one with the URL clause), not the wrapper function.

A wrapper function "around the web client function" could be something like that: Try to call the web client function once

• and return the response in case of success
• or in case of an unavailable host, repeat the call (after a wait) some more times until the request succeeds
• or for different errors, return the error to the caller:

create or replace function myWrapperFunction()
   returns long varchar
begin
   declare exp_unable_to_connect_to_host exception for sqlstate 'WW050';
   declare bTryAgain bit = 1;
   declare nMaxRetries int = 5;
   declare tmWaitSpan time = '00:00:05'; -- 5 s
   declare nCntAttempts int = 0;
   declare strResult long varchar;

   while bTryAgain = 1 and nCntAttempts < nMaxRetries loop
   begin
      set nCntAttempts = nCntAttempts + 1;
      set strResult = myfunction ('unreachablehost:1234');
      set bTryAgain = 0;
      exception
         -- remote host unavailable: wait and try again
         when exp_unable_to_connect_to_host then
            message 'myWrapperFunction: SQLSTATE set to ', sqlstate to console;
            waitfor delay tmWaitSpan;
         -- other errors: log and 
         when others then
            message 'myWrapperFunction: SQLSTATE set to ', sqlstate to console;
            set bTryAgain = 0;
            resignal; -- escalate error to caller of the function
   end;
   end loop;
   return strResult;
end;
select myWrapperFunction();

[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;

[PCollins]

I think what you really want to do is set myfunction so it returns HTTP error codes, then you determine the HTTP response, this is done by turning EXCEPTIONS off:

set 'HTTP(EXCEPTIONS=off; VERSION=1.1)'

Then you can code up how to handle all the different 400 and 500 errors as well.

From the CREATE FUNCTION statement [Web service] documentation:

EXCEPTIONS={ ON | OFF | AUTO } (short form EX) This HTTP option allows you to control status code handling. The default is ON.
When set to ON or AUTO, HTTP client functions will return a response for HTTP success status codes (1XX and 2XX) and all codes will raise the exception SQLE_HTTP_REQUEST_FAILED.

[VolkerBarth]

A little caveat: The docs (see my answer) also note

Exceptions that are not related to the HTTP status code (for example, SQLE_UNABLE_TO_CONNECT_TO_HOST) will be raised when appropriate regardless of the EXCEPTIONS setting.

So that particular error (SQLE_UNABLE_TO_CONNECT_TO_HOST, SQLCODE -981) still will throw an exception, and it is quite understandable in my book: If the host isn't available at all, there is no response, so there's no HTTP status to be returned "as is"...

[PCollins]

That is not my experience, I get client HTTP errors reported including 404 and 403 reported

[VolkerBarth]

In my understanding, 403 and 404 and other 4xx and 5xx HTTP status codes are returned by the according webserver (and therefore can be returned with EXCEPTIONS=off, that's my experience as well), whereas the current topic is that the according web server itself is not reachable at all.

[PCollins]

Ah right, I see and sorry I misread, yes in that scenario we just handle with an exception as you have suggested in the wrapper - nice talking, ignore me I'll go back to sleep 🙂