I am a newbie to SQLRPGLE.
I have successfully run the folllowing SQL script using the ACS run SQL scripts.
DROP TABLE ORIONTEST.JSONDATA;
CREATE TABLE ORIONTEST.JSONDATA (JDATA CLOB(10000) CCSID 1208);
INSERT INTO ORIONTEST.JSONDATA VALUES( CAST('
{
"header": {
"transactionId": "12597",
"transactionDate": "2024/02/02 02:30:01 +00:00",
"application": "WMS",
"organizationId": "HKNA",
"buildingId": "DFW01",
"businessUnit": "HKI"
},
"receiptsConfirmation": [
{
-----> plus more json pairs --->
}
' AS CLOB));
insert into ORIONTEST.APRECHEDWM (R@TXID, R@SUPN)
select R@TXID, R@SUPN
from ORIONTEST.JSONDATA, JSON_Table(JDATA, '$'
columns(R@TXID VARCHAR(15) path '$.header.transactionId',
nested '$.receiptsConfirmation[*]'
columns(R@SUPN varchar(20) path '$.supplierId'))
);
I am trying to recreate in a SQLRPGLE program (reading JDATA from JSONDATA) and it simply doesn’t work i.e. nothing retrieved from the json_table expression.
(this is for unit testing purposes only)
//----------------------------------------------------------------------
ctl-opt option(*srcstmt: *nodebugio);
dcl-f APRECHEDWM usage(*output);
dcl-s json_var sqltype (clob:10000);
dcl-s rtnmsg varchar(100);
exec sql SET OPTION COMMIT = *NONE;
exec sql select JDATA into :json_var from JSONDATA where JDATA <> ' ';
//-----------------------------------------------------------------------
// Parse json into APRECHEDWM
//-----------------------------------------------------------------------
exec sql
insert into APRECHEDWM (R@TXID, R@SUPN)
select R@TXID, R@SUPN
from JSONDATA, JSON_Table(:json_var format json, '£'
columns(R@TXID VARCHAR(15) path '£.header.transactionId',
nested '£.receiptsConfirmation[*]'
columns(R@SUPN varchar(20) path '£.supplierId'))
);
I know I’m missing something very basic! The £ is due to my job CCSID.
I tried a version with a simple select into a variable and this parsed the json_var as expected.
Any ideas or response would be greatly appreciated.