Question Re: handle exceptions SQLCode = -981 in Technology Q&A

handle exceptions SQLCode = -981

Baron — Tue, 05 Dec 2023 03:33:36 GMT

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?

Re: handle exceptions SQLCode = -981

VolkerBarth — Tue, 05 Dec 2023 05:12:20 GMT

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.

Re: handle exceptions SQLCode = -981

Baron — Tue, 05 Dec 2023 05:30:54 GMT

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)?

Re: handle exceptions SQLCode = -981

Baron — Tue, 05 Dec 2023 05:37:13 GMT

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?

Re: handle exceptions SQLCode = -981

VolkerBarth — Tue, 05 Dec 2023 06:17:00 GMT

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();

Re: handle exceptions SQLCode = -981

Baron — Tue, 05 Dec 2023 07:24:20 GMT

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

Re: handle exceptions SQLCode = -981

PCollins — Wed, 06 Dec 2023 04:20:15 GMT

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.

Re: handle exceptions SQLCode = -981

VolkerBarth — Wed, 06 Dec 2023 04:50:04 GMT

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"...

Re: handle exceptions SQLCode = -981

PCollins — Wed, 06 Dec 2023 04:55:02 GMT

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

Re: handle exceptions SQLCode = -981

VolkerBarth — Wed, 06 Dec 2023 07:11:24 GMT

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.

Re: handle exceptions SQLCode = -981

PCollins — Fri, 08 Dec 2023 04:33:39 GMT

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 🙂