Hello,
I am new to AppConnect and recently I've had a requirement to create a service on ACE that would connect to a Database...then make. a stored procedure call....the Stored Procedure returns an array of objects.
But when I load it to Environment.Variables.StoredProcedureRs eg, it gives me a string representation.
I've tried to take the string , then do a TRIM() on the enclosing double quotes, but it is not able to load the Array properly
Array I want :
{"Notifications":"[{"notification_id":"100001530","notification_name":"Self Service","title":"Test Title 2","t_content":"Test Content 2","updated_date":"06-JUN-2024 00:00:00","is_read":"","is_read_date":""},{"notification_id":"100001531","notification_name":"Self Service","title":"Test Title 2","t_content":"Test Content 2","updated_date":"06-JUN-2024 00:00:00","is_read":"","is_read_date":""}]}
Array I am getting:
"Notifications": "[{\"notification_id\":\"100001530\",\"notification_name\":\"Self Service\",\"title\":\"Test Title 2\",\"t_content\":\"Test Content 2\",\"updated_date\":\"06-JUN-2024 00:00:00\",\"is_read\":\"\",\"is_read_date\":\"\"},{\"notification_id\":\"100001531\",\"notification_name\":\"Self Service\",\"title\":\"Test Title 2\",\"t_content\":\"Test Content 2\",\"updated_date\":\"06-JUN-2024 00:00:00\",\"is_read\":\"\",\"is_read_date\":\"\"}]"
StoredProcedure code:
create or replace PROCEDURE NOTIFICATIONS_HISTORY_IV (
P_SOCIAL_ID IN VARCHAR2,
P_PAGE_NUMBER IN VARCHAR2,
P_PAGE_SIZE IN VARCHAR2,
P_NOTIFICATION_JSON OUT CLOB,
P_STATUS_CODE OUT VARCHAR2,
P_STATUS_DESC OUT VARCHAR2
) IS
v_notification_json VARCHAR2(32767);
v_chunk VARCHAR2(32767);
v_offset INTEGER := 1;
BEGIN
-- Debug: Start of the procedure
DBMS_OUTPUT.PUT_LINE('Start of procedure. SOCIAL_ID: ' || P_SOCIAL_ID || ', PAGE_NUMBER: ' || P_PAGE_NUMBER || ', PAGE_SIZE: ' || P_PAGE_SIZE);
-- Initialize the JSON array
v_notification_json := '[';
DBMS_OUTPUT.PUT_LINE('Initialized JSON array.');
FOR r IN (
SELECT N_NOTIFICATION_ID, N_NAME, N_TITLE, N_CONTENT, N_CREATION_TIMESTAMP, N_IS_READ, N_READ_TIMESTAMP
FROM NOTIFICATION
WHERE N_SOCIAL_ID = P_SOCIAL_ID
OFFSET (TO_NUMBER(P_PAGE_NUMBER) - 1) * TO_NUMBER(P_PAGE_SIZE) ROWS
FETCH NEXT TO_NUMBER(P_PAGE_SIZE) ROWS ONLY
) LOOP
-- Debug: Row data before JSON conversion
DBMS_OUTPUT.PUT_LINE('Processing record: ID=' || r.N_NOTIFICATION_ID || ', Name=' || r.N_NAME || ', Title=' || r.N_TITLE);
-- Build JSON object for each record
v_chunk := '{"notification_id":"' || r.N_NOTIFICATION_ID || '",'
|| '"notification_name":"' || r.N_NAME || '",'
|| '"title":"' || r.N_TITLE || '",'
|| '"t_content":"' || REPLACE(r.N_CONTENT, '"', '\"') || '",'
|| '"updated_date":"' || TO_CHAR(TO_DATE(r.N_CREATION_TIMESTAMP, 'DD-MON-YY'), 'DD-MON-YYYY HH24:MI:SS') || '",'
|| '"is_read":"' || r.N_IS_READ || '",'
|| '"is_read_date":"' || TO_CHAR(TO_DATE(r.N_READ_TIMESTAMP, 'DD-MON-YY'), 'DD-MON-YYYY HH24:MI:SS') || '"},';
DBMS_OUTPUT.PUT_LINE('Generated JSON chunk: ' || v_chunk);
-- Append the chunk to the JSON array
v_notification_json := v_notification_json || v_chunk;
END LOOP;
-- Debug: After processing all records
DBMS_OUTPUT.PUT_LINE('Finished processing records.');
-- Remove the last comma and close the JSON array
IF v_notification_json <> '[' THEN
v_notification_json := SUBSTR(v_notification_json, 1, LENGTH(v_notification_json) - 1);
END IF;
v_notification_json := v_notification_json || ']';
DBMS_OUTPUT.PUT_LINE('Final JSON array: ' || v_notification_json);
-- Assign the final JSON to the output parameter
P_NOTIFICATION_JSON := v_notification_json;
-- Debug: Output JSON length
DBMS_OUTPUT.PUT_LINE('Output JSON length: ' || LENGTH(P_NOTIFICATION_JSON));
-- Set status based on the content
IF LENGTH(P_NOTIFICATION_JSON) > 2 THEN
P_STATUS_CODE := 'I000000';
P_STATUS_DESC := 'Successful Operation';
ELSE
P_STATUS_CODE := 'E900801';
P_STATUS_DESC := 'Social ID not found';
END IF;
-- Debug: Final status
DBMS_OUTPUT.PUT_LINE('Status Code: ' || P_STATUS_CODE || ', Status Description: ' || P_STATUS_DESC);
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_NOTIFICATION_JSON := '[]';
P_STATUS_CODE := 'E900801';
P_STATUS_DESC := 'Social ID not found';
DBMS_OUTPUT.PUT_LINE('No data found exception encountered.');
WHEN OTHERS THEN
P_NOTIFICATION_JSON := '[]';
P_STATUS_CODE := 'E999999';
P_STATUS_DESC := 'Unrecoverable DB Error: ' || SQLERRM;
-- Print detailed error information
DBMS_OUTPUT.PUT_LINE('Unhandled exception encountered:');
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('Call Stack: ' || DBMS_UTILITY.FORMAT_CALL_STACK);
END NOTIFICATIONS_HISTORY_IV;
I even tried a JAVA COMPUTE NODE but not getting any output...
All ddebug breakpoints have passed through the code
Java Compute Node code:
package com.ibm.notifications;
import org.json.JSONArray;
import org.json.JSONObject;
import com.ejada.framework.FrameworkLib;
import com.ibm.broker.javacompute.MbJavaComputeNode;
import com.ibm.broker.plugin.*;
public class NotificationHistoryRq_JavaCompute_II extends MbJavaComputeNode {public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
try {
// Get the input message and create the output message and assembly
MbMessage inMessage = inAssembly.getMessage();
MbMessage outMessage = new MbMessage();
MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly, outMessage);
// Copy headers from input to output message
outMessage.getRootElement().addAsLastChild(inMessage.getRootElement().getFirstChild().copy());
String JsonString = FrameworkLib.getGlobalEnvironmentElementValue(inAssembly, "Variables/StoredProcedureRs/P_NOTIFICATION_JSON");
if (JsonString == null) {
throw new MbUserException(this, "evaluate()", "", "", "JSON data at path not found", null);
}
System.out.println("Decoded Array"+JsonString);
MbElement outRoot = outMessage.getRootElement().createElementAsLastChild(MbJSON.PARSER_NAME);
MbElement jsonData = outRoot.createElementAsLastChild(MbElement.TYPE_NAME, "NotificationsRs", null);
// Create MsgRsHdr and ResponseStatus
MbElement msgRsHdr = jsonData.createElementAsLastChild(MbElement.TYPE_NAME, "MsgRsHdr", null);
MbElement responseStatus = msgRsHdr.createElementAsLastChild(MbElement.TYPE_NAME, "ResponseStatus", null);
responseStatus.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, "StatusCode", "I000000");
responseStatus.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, "StatusDesc", "Successful Operation");
// Parse the notificationsJsonString into a JSONArray
JSONArray notificationsArray = new JSONArray(JsonString);
int notificationsArrayLength = notificationsArray.length();
System.out.println("Passed Array loading "+notificationsArrayLength);
// Add each JSONObject to the Notifications array in the output message
MbElement outputNotificationsElement = jsonData.createElementAsLastChild(MbJSON.ARRAY, "Notifications", null);
for (int i = 0; i < notificationsArray.length(); i++) {
JSONObject notification = notificationsArray.getJSONObject(i);
outputNotificationsElement.createElementAsLastChildFromBitstream(notification.toString().getBytes("UTF-8"), MbJSON.PARSER_NAME, "", "", "", 0, 0, 0);
}
System.out.println("Passed Final payload loading");
// Parse the JSON string
/*JSONArray notificationsArray = new JSONArray(JsonString);
System.out.println("Number of notifications: " + notificationsArray.length());
String jsonArrayString = notificationsArray.toString();
System.out.println(jsonArrayString);
MbElement envVar = inAssembly.getLocalEnvironment().getRootElement().createElementAsLastChild(MbElement.TYPE_NAME_VALUE, "CompleteNotifications", jsonArrayString);
*/
// Propagate the message
out.propagate(outAssembly);
System.out.println("Passed final stage");
} catch (MbException e) {
throw e;
} catch (Exception e) {
throw new MbUserException(this, "evaluate()", "", "", e.toString(), null);
}
}
}
Please suggest a way out of this....no matter what I do in ESQL, it is always parsed as a string resulting in escape characters throughout the payload when constructed as a JSON output, which is the intent here.
------------------------------
Bikramjit Purkayastha
------------------------------