sqlrestbase64rpgle

UPS OAuth2.0 Access token request with SQL in RPG-Program "Invalid/Missing Authorization Header" with BASE64ENCODE

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.

Solution

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