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?

asked 03 Mar, 10:04

Frank's gravatar image

Frank
583142036
accept rate: 17%

edited 04 Mar, 07:01

Volker%20Barth's gravatar image

Volker Barth
36.9k343505765

1

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")?
(04 Mar, 06:57) Volker Barth
Replies hidden

Yes, I get the -983 error

(04 Mar, 06:58) Frank
1

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

Please show us some snippets of the code...

(04 Mar, 07:02) Volker Barth

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

(05 Mar, 07:40) Frank
1

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?

(05 Mar, 08:32) Volker Barth

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

permanent link

answered 05 Mar, 08:29

Volker%20Barth's gravatar image

Volker Barth
36.9k343505765
accept rate: 34%

edited 05 Mar, 08:36

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

(09 Mar, 04:10) Frank
Replies hidden

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

(09 Mar, 06:48) Volker Barth
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:

×43
×25

question asked: 03 Mar, 10:04

question was seen: 229 times

last updated: 09 Mar, 06:48