Dynamic HTTP Header for Web Client procedure

[Baron]

I need to call an external API to add a product on another system. For this call I need to attach a security token as an Authoization header to the POST HTTP Request (together with another Header Request information like Content-Type ...).

My code looks like this:

create or replace function AddProductAPI ("JsonLoad" long varchar)

returns long varchar

url 'http://localhost/api/v1/product'

type 'http:post:application/json'

Header 'Content-Type: application/json';-- line5

---

create or replace procedure AddProduct()

begin

call AddProductAPI ('{"Product_Name": "Mouse","ProductID": "123"}');--*line9

end;

In Web Service I can make use of the function HTTP_HEADER in order to fetch the Header information being passed to me as a Web service/server, but how I can set such header information when I am consuming an external services?

I tried to do it on line5 above! (something like):

Header 'Content-Type: application/json, Authorization' + select @MyToken; -- Syntax Error

When I try to do this using SA_SET_HTTP_HEADER(), on the line before line9, then there is no any syntax error, but also no any effect (it does nothing)!

Any ideas please!

[VolkerBarth · Accepted solution]

The system procedure sa_set_http_header() is meant for web services, not for web client functions so using the HEADER clause within the CREATE FUNCTION statement is the right appraoch AFAIK. But you cannot add a query result there, instead you can use the substitution parameters (i.e. the "!param" syntax) to provide variable contents to your web client function's definition.

Here's a sample from the docs - I guess it's quite easy to use that as a starting point:

CREATE PROCEDURE test(uid CHAR(128), pwd CHAR(128), headers LONG VARCHAR)
    URL 'http://!uid:!pwd@localhost/myservice'
    HEADER '!headers';

[Baron · Accepted solution]

Thank you very much.

It was very helpful.

I have another question: How can I issue a PATCH Request? In the documentation is mentioned that there are only 5 types available for http-type-spec-string (so, only GET, POST, PUT, DELETE, HEAD).

If I use this type (PATCH), then I get an error message Invalid Procedure subtype 'PATCH' for type 'HTTP'

[VolkerBarth · Accepted solution]

Well, v17 also lists the OPTIONS method but I guess PATCH simply is not supported then...

But of course you are free to ask to add support for method PATCH as a product suggestion here, because it seems to be used more frequently with REST APIs... Just add it as a separate question with tag product-suggestion or the like...

[Baron · Accepted solution]

what is meant with the OPTIONS method?

I added a product-suggestion!

[VolkerBarth · Accepted solution]

The OPTIONS method has been added with v17, see the docs. I guess it's main use is for CORS.