How to handle HTTP error codes

[fvestjens]

In a stored function/procedure I want to handle the HTTP error code received from a HTTP:POST procedure. I tried with on exception resume and with try catch but both don't seem to be able to catch the error.

What I'm trying to do is in a function I use a cursor to loop through records and post a json to a web service. However when I receive a HTTP error the cursor stops and all the remaining records are not processed.

How can I achieve that the HTTP error code is caught and the cursor continues?

[VolkerBarth]

Do you

• get a SQLCODE -983 error "HTTP request failed. Status code '%1'" when calling the web client procedure
• or is the HTTP error contained in the web procedure's result set (in the row with Attribute = "Status")?

[fvestjens]

Yes, I get the -983 error

[VolkerBarth]

And your exception handler code does not catch the -983 error?

Please show us some snippets of the code...

[fvestjens]

This is the HTTP:POST procedure

CREATE OR REPLACE FUNCTION "usr"."ISCV_API_PostShipment"(IN in_json LONG VARCHAR, IN baseurl LONG VARCHAR, IN token LONG VARCHAR ) 
returns long varchar
url '!baseurl/shipments/shipments'
type 'HTTP:POST:application/json'
set 'HTTP(VERSION=1.1)'
HEADER 'Accept: text/plain\\nAuthorization: Bearer !token';

GRANT EXECUTE ON "usr"."ISCV_API_PostShipment" TO usr;

This is the procedure calling the HTTP:Post procedure

CREATE OR REPLACE PROCEDURE "usr"."Test500"()
ON EXCEPTION RESUME
BEGIN
   DECLARE l_test varchar(10);
   declare l_tokenRes long varchar;
   declare l_token long varchar;
   DECLARE sqlcd varchar(5);
   DECLARE sqlst varchar(5);

   BEGIN TRY
       set l_token = 'test';
       select usr.ISCV_API_PostShipment('{"reference":"1699725","customerReference":"Test multi compartment","status":"accepted","carrier":{"code":"Den Hartogh Liquid Logistics B"},"department":"Intermodal","shipper":{"id":"84","uiir":"","code":"BASLUD","name":"BASF SE","address":"Tor 15 Carl Bosch Strasse 38","postalcode":"67117     ","place":"Ludwigshafen","country":{"code":"DE","name":"Germany"},"contact":{"name":"jan","email":"zo"},"position":{"latitude":0,"longitude":0}},"buyer":{"id":"34403","uiir":"","code":"ADMHAM","name":"ADM Hamburg AG","address":"Nippoldstrasse 117","postalcode":"20457","place":"Hamburg","country":{"code":"DE","name":"Germany"},"contact":{"name":"Piet","email":"anders"},"position":{"latitude":10,"longitude":10}},"equipment":{"number":"","adrClassificationCode":"","length":0,"width":0,"height":0,"tareWeight":{"value":0}, "products":[{"code":"1,4BUTAN","adrClassificationCode":"9","unNumber":"3077","plannedQuantity":{"value":0},"plannedTemperature":0,"plannedTime":{"timeFrom":"2019-07-24T09:00","timeTo":"2019-07-24T10:00"},"actualQuantity":{"value":0},"actualTemperature":null,"actualTime":{"timeFrom":"2019-07-24T10:00","timeTo":"2019-07-24T11:00"}},{"code":"CN 386","adrClassificationCode":"2","unNumber":"1912","plannedQuantity":{"value":0},"plannedTemperature":0,"plannedTime":{"timeFrom":"2019-07-24T09:30","timeTo":"2019-07-24T09:30"},"actualQuantity":{"value":0},"actualTemperature":0,"actualTime":{"timeFrom":"2019-07-24T10:00","timeTo":"2019-07-24T10:00"}}]}}', usr.getOption('INFORIT_baseURL_ISCV'), l_token);
   END TRY
   BEGIN CATCH
         Message sqlcd || ' ' || sqlst type info to client;
   END CATCH;
END;

when we execute call usr.Test500() in I-SQL then we get the following error:

There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. HTTP request failed. Status code '500 Internal Server Error' SQLCODE=-983, ODBC 3 State="HY000" Line 1, column 1

[VolkerBarth]

Is there a particular call that does return the error? Is the error also returned if you directly call the web client function with the according data?

[VolkerBarth · Accepted solution]

Are you using v17.0.4 or above?

If so, the following FAQ may be of help. At least the error message looks alike.

I suspect you need to change the web client function to a web client procedure in order to get the HTTP Status, AFAIK functions do only return the response's body, not the full response. Note, that's just from my memory...

[fvestjens · Accepted solution]

Changing the web client function to a procedure solved the issue. Thanks Volker

[VolkerBarth · Accepted solution]

Glad to hear that - feel free to accept that answer then:)