DB2 SYSTOOLS.HTTPPUTCLOB ignores JSON in third parm, REQUESTMSG (body?)

Question

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>

Note that the HTTP functions use Java behind the scenes...since there overhead to starting the JVM in a job, it sticks around. So reconsider if using in lots of interactive job or short lived batch jobs. — Charles, Sep 06 '19 at 19:27
Consider using the open source [HTTP API](http://scottklement.com/httpapi/) and [YAJL](http://scottklement.com/yajl/) port as outlined in the [Working with JSON in RPG](http://scottklement.com/presentations/Working%20with%20JSON%20in%20RPG.pdf) article — Charles, Sep 06 '19 at 19:31
The response header looks fine, but you've still not shown what's in the response. — Charles, Sep 06 '19 at 19:45
The response is a JSON document, starting with {"id":2,"client_id":103792,"customer_number":"TEST123","alternate_id":"","name":"Image Corp - Test ","attn_name":"Partner","address_line1":"152-01 Maynard St","address_line2":"Suite 123","city":"Northlake","zip":"12345", — Mike Robinson, Sep 06 '19 at 19:57

score 1 · Accepted Answer · answered Sep 10 '19 at 20:32

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.

score 0 · Answer 2 · answered Sep 06 '19 at 20:11

0

I don't see any problems...

From the header, the web service appears to be accepting what you're sending. Which is a bit of a surprise, since you only send two attributes.

Normally, PUT requires all of the entity's attributes, PATCH is used just to update selected attributes. see RESTful API Design — PUT vs PATCH

Are you sure the Postman call is working like you think it is? Are you by chance doing a PATCH in postman rather than the PUT you are using from Db2 for i?

Can you work with the web service provider to see what's going on from their end?

answered Sep 06 '19 at 20:11

Charles

21,637
1
20
44

Postman is definitely working. They have a web portal where I can inquire on the customer to see the current address. Address updates immediately every time with Postman. HTTPPUTCLOB is definitely not updating the address because I verify in the web portal after each test. I send the same two attributes in both calls. I can check with the service provider, but thought I would check first to see if I am using the IBM command wrong. Since it works with Postman, I don't think it is their failure, but more likely my failure to get the RPG side right. – Mike Robinson Sep 06 '19 at 20:20
If you were doing something wrong in the RPG, I'd expect something beside the 200 - OK... are you positive you're not using PATCH in postman? – Charles Sep 06 '19 at 20:23
Yes. I would post an image here, but not sure how. But it is definitely PUT. I have not tried Patch in Postman. Possibly I could try taking the whole response JSON and make a single mod to see if that works in RPG. Are you aware of an HTTPPATCHCLOB opcode? I have not come across that command. – Mike Robinson Sep 06 '19 at 20:31
I tried taking the whole response that I received from the api, changing city and zip, and PUT'ing that back to HTTPPUTCLOB. No change to city and zip in the response or on the web portal. – Mike Robinson Sep 06 '19 at 21:12
@MikeRobinson hate to ask, "Is it plugged in" but are you sure you're looking at the same endpoints/environments? It's not a DEV/QA/PROD issue is it? Because, again, I don't see any issues with your code...and the web service is responding with 200-OK.... – Charles Sep 06 '19 at 21:56
I appreciate your followup. I currently only have access to their sandbox. We have not gone live yet. Both my tests are pointed at that same environment. I reached out to them to get a list of all fields in the request to test the PUT vs PATCH observation you brought up. I will update here when I get that full list of fields. – Mike Robinson Sep 09 '19 at 12:48

DB2 SYSTOOLS.HTTPPUTCLOB ignores JSON in third parm, REQUESTMSG (body?)

2 Answers2