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?

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