I have the same problem like Brian here: UPS API OAuth token request fails.
The try it out in Postman works. I get my Access Token.
My task now is to make a working SQL statement also for UPS API.
SELECT CAST(RESPONSEMSG AS CLOB(1000000)),
CAST(responseHttpHeader AS CLOB(1000000))
INTO :W_RSP, :W_RSPHDR
FROM TABLE( SYSTOOLS.HTTPPOSTCLOBVerbose(
CAST(:W_URL AS VARCHAR(255)),
CAST (:HEADER AS CLOB(1K)),
CAST (:PAYLOAD AS CLOB(20K)))
) AS LOGIN;
W_URL = 'https://wwwcie.ups.com/security/v1/oauth/token';
HEADER ='<httpHeader>
<header name="Authorization" value="Basic '+ W_BS64 +'"/><header name="accept" value="application/json"/>
<header name="Content-Type" value="application/x-www-form-urlencoded"/>
<header name="x-merchant-id" value="xp1g0NaY2hAqDZbkoNAeDs8X3R1bH72KCdmNUeV5DYOMlfiU"/></httpHeader>';
PAYLOAD = 'grant_type=client_credentials';
And I encode the W_BS64 like this:
W_Client = '{xxxxclientIDxxxx}:{xxxxxclientsecretxxxxx}'
Values QSYS2.BASE64_ENCODE(:W_Client) Into :W_BS64;
When I debug this in my SQLRPG program, W_RSP is empty and W_RSPHDR includes only weird characters.
When I use the ACS SQL Scripter and use the BASE64 String in the header, I also get the “Invalid/Missing Authorization Header” Error. So I don’t know if it is because of the BASE64 is not right, or if I am missing something.
I thought I did the conversion from the UPS Documentation curl to SQL correctly.
I have not found an example how to use SQL to get a successful response with the Access token yet.
Please let me know, what I am doing wrong.
The main thing to remember when calling web services from the IBM i is that the system and RPG are EBCDIC; whereas the web is UTF8.
I suspect that your issue is that QSYS2.BASE64_ENCODE() encodes a binary string. Meaning, it doesn’t convert that string between EBCDIC and UTF-8.
declare W_Client and W_BS64 as follows:
dcl-s W_Client varchar(100) ccsid(*UTF8);
dcl-s W_BS64 varchar(200) ccsid(*UTF8);
Actually it seems that SYSTOOLS.HTTPPOSTCLOBVerbose() is expecting UTF-8 strings, so you should (probably) declare W_URL, HEADER, PAYLOAD, RESPONSEMSG and responseHttpHeader as *UTF8 also.
I haven’t actually used the SYSTOOLS.HTTPxxx functions in many, many years.
In fact, IBM came out with QSYS2.HTTP_xxxx() functions a few years back that are preferred by many since the don’t use Java like the SYSTOOLS functions. Also, they expect JSON rather than XML.
Honestly, I don’t use either. I’ve been using Scott Klement’s open source HTTPAPI for a long, long time.
If you’re new to calling web services from RPG, I highly recommend Scott’s Options for Consuming REST APIs presentation.
3