Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 05 Dec '23, 03:33

Baron's gravatar image

Baron
2.2k143152180
accept rate: 48%


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

permanent link

answered 05 Dec '23, 06:16

Volker%20Barth's gravatar image

Volker Barth
40.4k365553825
accept rate: 34%

converted 14 Dec '23, 04:43

Baron's gravatar image

Baron
2.2k143152180

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.

permanent link

answered 05 Dec '23, 05:12

Volker%20Barth's gravatar image

Volker Barth
40.4k365553825
accept rate: 34%

edited 05 Dec '23, 05:12

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

(05 Dec '23, 05:30) 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?

(05 Dec '23, 05:37) Baron
Replies hidden
1

@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;
(05 Dec '23, 07:24) Baron

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.
permanent link

answered 06 Dec '23, 04:20

pcollins's gravatar image

pcollins
1915612
accept rate: 0%

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

(06 Dec '23, 04:50) Volker Barth

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

(06 Dec '23, 04:55) pcollins
Replies hidden
1

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.

(06 Dec '23, 07:11) Volker Barth
Comment Text Removed
2

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

(08 Dec '23, 04:33) pcollins
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×95
×66
×48

question asked: 05 Dec '23, 03:33

question was seen: 392 times

last updated: 08 Dec '23, 04:33