/* json function*/
FUNCTION string2json RETURNS LONGCHAR
(INPUT plcString AS LONGCHAR):
/* TODO: Better search & replace is needed, rather than a brute-force replace method.*/
ASSIGN
plcString = REPLACE(plcString, '\','\\') /* DO NOT CHANGE THE ORDER THIS HAS TO BE DONE FIRST!*/
plcString = REPLACE(plcString, '"','\"')
plcString = REPLACE(plcString, '/','\/')
plcString = REPLACE(plcString, CHR(8),'\b') /*backspace*/
plcString = REPLACE(plcString, CHR(12),'\f') /* Formfeed */
plcString = REPLACE(plcString, CHR(10),'\n') /*newline*/
plcString = REPLACE(plcString, CHR(13),'\r') /*carage return*/
plcString = REPLACE(plcString, CHR(9),'\t'). /*tab*/
RETURN plcString.
END FUNCTION.
FUNCTION addValue RETURN LONGCHAR
(INPUT phnFieldHandle AS HANDLE,
INPUT pinFieldExtent AS INTEGER):
DEFINE VARIABLE lcJSONValue AS LONGCHAR NO-UNDO.
CASE phnFieldHandle:DATA-TYPE:
WHEN 'CHARACTER':U OR
WHEN 'CLOB':U THEN
ASSIGN
lcJSONValue = SUBSTITUTE('"&1"',
string2json(phnFieldHandle:BUFFER-VALUE(pinFieldExtent))
).
WHEN 'DATE':U OR
WHEN 'DATETIME':U OR
WHEN 'DATETIME-TZ':U THEN
ASSIGN
lcJSONValue = SUBSTITUTE('"&1"',
ISO-DATE(phnFieldHandle:BUFFER-VALUE(pinFieldExtent))
).
WHEN 'LOGICAL':U OR
WHEN 'ROWID':U OR
WHEN 'RECID':U OR
WHEN 'HANDLE':U OR
WHEN 'COM-HANDLE':U THEN
ASSIGN
lcJSONValue = SUBSTITUTE('"&1"',
STRING(phnFieldHandle:BUFFER-VALUE(pinFieldExtent))
).
WHEN 'INTEGER' OR
WHEN 'DECIMAL' OR
WHEN 'INT64' THEN
ASSIGN
lcJSONValue = SUBSTITUTE('&1',
phnFieldHandle:BUFFER-VALUE(pinFieldExtent)
).
WHEN 'BLOB' OR
WHEN 'RAW' THEN
ASSIGN
lcJSONValue = SUBSTITUTE('"&1"',
BASE64-ENCODE(phnFieldHandle:BUFFER-VALUE(pinFieldExtent))
).
END CASE.
RETURN lcJSONValue.
END.
FUNCTION table2json RETURNS LONGCHAR
(INPUT hnTableHandle AS HANDLE):
DEFINE VARIABLE lcJsonNotation AS LONGCHAR NO-UNDO.
DEFINE VARIABLE hnQueryHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE hnFieldHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE ifieldPos AS INTEGER NO-UNDO.
DEFINE VARIABLE inFieldExtent AS INTEGER NO-UNDO.
DEFINE VARIABLE chJsonArray AS LONGCHAR NO-UNDO.
IF NOT VALID-HANDLE(hnTableHandle) THEN
RETURN ''.
CREATE QUERY hnQueryHandle.
hnQueryHandle:SET-BUFFERS(hnTableHandle).
hnQueryHandle:QUERY-PREPARE(SUBSTITUTE("PRESELECT EACH &1",
hnTableHandle:NAME)
).
hnQueryHandle:QUERY-OPEN().
IF NOT hnQueryHandle:IS-OPEN THEN
RETURN ''.
ASSIGN
lcJsonNotation = '~{'.
JSON-NOTATION:
REPEAT:
hnQueryHandle:GET-NEXT(NO-LOCK, NO-WAIT).
IF hnQueryHandle:QUERY-OFF-END THEN
DO:
LEAVE JSON-NOTATION.
END.
ASSIGN
lcJsonNotation = lcJsonNotation + SUBSTITUTE('"&1":~{',
hnQueryHandle:CURRENT-RESULT-ROW).
DO ifieldPos = 1 TO hnTableHandle:NUM-FIELDS:
ASSIGN
hnFieldHandle = hnTableHandle:BUFFER-FIELD(ifieldPos).
IF hnFieldHandle:EXTENT GT 0 THEN
DO:
ASSIGN
chJsonArray = '['.
JSON-EXTENT:
DO inFieldExtent = 1 TO hnFieldHandle:EXTENT:
chJsonArray = chJsonArray + SUBSTITUTE('&1,',
addValue(INPUT hnFieldHandle,
INPUT inFieldExtent)
).
END.
ASSIGN
chJsonArray = RIGHT-TRIM(chJsonArray,',').
chJsonArray = chJsonArray + ']'.
ASSIGN
lcJsonNotation = lcJsonNotation + SUBSTITUTE('"&1":&2',
hnFieldHandle:NAME,
chJsonArray).
END.
ELSE
DO:
ASSIGN
lcJsonNotation = lcJsonNotation + SUBSTITUTE('"&1":&2',
hnFieldHandle:NAME,
addValue(INPUT hnFieldHandle,
INPUT hnFieldHandle:EXTENT)
).
END.
ASSIGN
lcJsonNotation = lcJsonNotation + ','.
END.
ASSIGN
lcJsonNotation = RIGHT-TRIM(lcJsonNotation, ',')
lcJsonNotation = lcJsonNotation + '},'.
END.
ASSIGN
lcJsonNotation = RIGHT-TRIM(lcJsonNotation, ',')
lcJsonNotation = lcJsonNotation + '}'.
hnQueryHandle:QUERY-CLOSE().
hnTableHandle:BUFFER-RELEASE().
DELETE OBJECT hnQueryHandle.
RETURN lcJsonNotation.
END FUNCTION.
/* UNIT TEST */
DEFINE VARIABLE chJSON AS LONGCHAR NO-UNDO.
DEFINE TEMP-TABLE ttJSONTEST NO-UNDO
FIELD jsonID AS INTEGER
FIELD a1 AS CHARACTER
FIELD a2 AS DATETIME
FIELD a3 AS INTEGER EXTENT 10
FIELD a4 AS CHARACTER EXTENT 10.
DEFINE VARIABLE inLOOP AS INTEGER NO-UNDO.
DEFINE VARIABLE inExtentLoop AS INTEGER NO-UNDO.
DO inLOOP = 1 TO 100:
CREATE ttJSONTEST.
ASSIGN
ttJSONTEST.jsonID = inLOOP
ttJSONTEST.a1 = ENCODE(STRING(ADD-INTERVAL(NOW, inLOOP, 'DAYS' )))
ttJSONTEST.a2 = NOW.
DO inExtentLoop = 1 TO 10:
ASSIGN
ttJSONTEST.a3[inExtentLoop] = RANDOM(1,1000)
ttJSONTEST.a4[inExtentLoop] = CHR(RANDOM(48,126)) + CHR(RANDOM(48,126)) + CHR(RANDOM(48,126)) + CHR(RANDOM(33,126)).
END.
END.
ASSIGN
chJSON = table2json(TEMP-TABLE ttJSONTEST:DEFAULT-BUFFER-HANDLE ).
/*
ASSIGN
chJSON = table2json(BUFFER CUSTOMERS:HANDLE ). */
COPY-LOB FROM OBJECT chJSON TO FILE 'C:\temp\testtest.txt'.
