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