systools httpPostClob

[mjhaston]

Been fighting with this for a couple of hours. I'm trying out httpPostClob, so I'm trying to replace an existing/working REST call made with HTTPAPI + YAJL.

Using HTTPAPI, I was able to define response as 100000a. Now that I'm using response in an SQL statement, I had to change back to 32000a.

This concerns me because I'm going to truncate data easily. I'm wondering if anyone else is using httpPostClob and how they work with data limits? Do I need to also use JSON_TABLE to handle the return data? I'm very comfortable with YAJL and how I can deal with arrays and nested array. I'm not sure how JSON_TABLE handles all of that.

Thanks in advance.
Mike

d response        s          32000a   varying   
d returnData      s                   sqltype(clob:1000000) 
d requestData     s                   sqltype(clob:1000000)

exec sql                                            
  select systools.httpPostClob(                     
       :url,                                        
       cast(:header as clob(1k) ccsid 1208),        
       cast(:requestData as clob(20k) ccsid 1208))  
       into :returnData                             
       from sysibm.sysdummy1                        
;

This is the added hoop to jump through since using httpPostClob, because this works with a CLOB, but the YAJL function below doesn't.

exec sql                                                   
 select cast(cast(:returnData as clob(20k) ccsid 1208)     
 as varchar(32000)) into :response from sysibm.sysdummy1   
;

docNode = yajl_buf_load_tree( %addr(response: *data)    
                            : %len(%trimr(response))    
                            : errMsg                    
                            );

[Scott Klement]

Hello,

1. You can find the definition for HttpPostClob here: https://www.ibm.com/support/knowledg...tppostclob.htm As you can see, IBM defined the header as CLOB(10k) and the request and result are CLOB(2g). So there is no problem with data larger than 32000 in the request/response data, but you'll have to define them as CLOB fields since SQL's VARCHAR data type is limited to 32k.

2. You ask if you need to use JSON_TABLE? You don't NEED to... it's up to you. You can use httpPostClob together with YAJL, or HTTPAPI with JSON_TABLE or any mixture of them that you like. Personally, I kinda like JSON_TABLE for very simple documents, but as they get more complex, I don't. JSON_TABLE essentially requires you to figure out how to "flatten" the document into a table, i.e. a single-depth set of rows/columns. YAJL on the other hand doesn't care about that, you can have as many arrays, structures, and more arrays nested inside each other as you wish. When used together with DATA-INTO, I find YAJL easier to use than JSON_TABLE.

3. The yajl_buf_load_tree() function works with a buffer.. (That's why it has "buf" in the name). There are also functions like yajl_string_load_tree and yajl_stmf_load_tree that do the same thing but are easier to use with the various data types. You can certainly use yajl_buf_load_tree to read your CLOB, though... If you look at your RPG compile listing, you'll see that the SQLTYPE(CLOB:1000000) gets converted into a data structure in RPG, and it has fields for the data and the length. You simply pass the address of the data subfield in the first parameter to yajl_buf_load_tree, and the length in the second parameter.

[mjhaston]

Thanks as usual for the in-depth answers. This code works perfectly.

docNode = yajl_string_load_tree(returnData_data    
                               : errMsg            
                               );