I am trying to HTTP PUT to a remote API to update data.
I am on an IBM i, V7R3M0, using SQL command SYSTOOLS.HTTPPUTCLOB. When I do a similar test in Postman (to the same URL, same headers, same JSON in the body), the remote data updates successfully.
But with SYSTOOLS.HTTPPUTCLOB(:myUrl, :myHdr, :myRequest), I get a response, but no data is changed.
For this test, I am trying to update a customer's city and zip code.
IBM says the CLOB parameters are CCSID 1208, so that is what I am using. https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfhttpputclob.htm
I included CCSID 37 just so I could read it. I have tried passing the CCSID 37 parms, and not specifying a CCSID, and none of that improves the results.
**free
dcl-s customers char(7);
dcl-s url varchar(2000);
dcl-s response varchar(5000);
dcl-s reqClob SQLTYPE(CLOB:5000) ccsid(1208);
dcl-s hdrClob SQLTYPE(CLOB:5000) ccsid(1208);
dcl-s reqClob37 SQLTYPE(CLOB:5000) ccsid(37);
dcl-s hdrClob37 SQLTYPE(CLOB:5000) ccsid(37);
customers = 'TEST123'; //my test customer
// format a JSON document
exec sql select json_object(
'zip' value '98756',
'city' value 'Chicago'
)
into :reqClob
from SYSIBM.SYSDUMMY1;
url = 'https://*remoteapi*/customers/'+%Trim(customers);
// format my XML headers
exec sql
with T(tname, tvalue) as (Values
('Authorization', 'Basic *mybase64id*'),
('x-client-id', '111111'),
('x-customer-primary-key', 'customer_number'))
SELECT
XMLGROUP(RTRIM(T.tname) AS "name", RTRIM(T.tvalue) AS "value"
OPTION ROW "header" ROOT "httpHeader" AS ATTRIBUTES)
INTO :hdrClob
From T ;
// convert so I can read it in debug
exec sql select :reqClob INTO :reqClob37 from sysibm.sysdummy1;
exec sql select :hdrClob INTO :hdrClob37 from sysibm.sysdummy1;
// PUT it
exec SQL
select SYSTOOLS.HTTPPUTCLOB(:url, :hdrClob, :reqClob)
into :response
from SYSIBM.SYSDUMMY1;
*inlr = *on;
return;
I successfully receive customer information in the "response" variable. But the city and zip values are the old values from the remote site, and the values on the site remain unchanged. The SQLCOD and SQLSTT values are zero.
Again, when I do this same thing in Postman, the response has my new values, and the data is updated on the remote site.
It appears to me that the third parm (in this case "reqClob") is ignored. Shouldn't a JSON document in this third parm cause an update with an HTTP PUT the same as in the body when using a tool like Postman?
*edit ... tried to get a little more info be running the verbose version HTTPPUTCLOBVERBOSE. I added this code
dcl-s verboseHdr SQLTYPE(CLOB:5000) ccsid(37);
exec SQL
select varchar(responseMsg,2048), varchar(responseHttpHeader,1024)
into :response, :verboseHdr
from table (
SYSTOOLS.HTTPPUTCLOBVERBOSE(:url2, :hdrClob, :reqClob));
The "verboseHdr" variable contains this header info:
<?xml version="1.0" encoding="UTF-8" ?><httpHeader responseCode="200"><responseMessage>OK</responseMessage><header name="HTTP_RESPONSE_CODE" value="HTTP/1.1 200 OK"/><header name="Server" value="Apache/2.4.25 (Ubuntu)"/><header name="Cache-Control" value="no-cache"/><header name="X-Content-Type-Options" value="nosniff"/><header name="Connection" value="keep-alive"/><header name="Vary" value="Authorization"/><header name="Content-Length" value="688"/><header name="Date" value="Fri, 06 Sep 2019 19:32:07 GMT"/><header name="Content-Type" value="application/json"/></httpHeader>
Turns out I needed to include the header for Content-Type: application/json. I added that to the header SQL.
exec sql
with T(tname, tvalue) as (Values
('Authorization', 'Basic *mybase64id*'),
('x-client-id', '111111'),
('x-customer-primary-key', 'customer_number'),
('Content-Type', 'application/json'))
SELECT
XMLGROUP(RTRIM(T.tname) AS "name", RTRIM(T.tvalue) AS "value"
OPTION ROW "header" ROOT "httpHeader" AS ATTRIBUTES)
INTO :hdrClob
From T ;
With the content type specified correctly, I was able to populate the body with just the fields that changed and the remote site is updated while still doing the PUT.