Websphere Message Broker

ESQL Samples

Creating content of a message from a text / chars

The following code creates new elements of an XML message.

For more details please view the official IBM documentation:
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04950_.htm

Thanks to FJ for finding an issue with the InputProperties.CodedCharSetId

DECLARE testXML CHAR;
SET testXML = ‘Hawker‘;

DECLARE xmlBlob BLOB;
DECLARE options INTEGER BITOR(FolderBitStream, ValidateContent, ValidateValue);
SET xmlBlob = CAST(testXML as BLOB CCSID 1208);
CREATE LASTCHILD OF inputReference DOMAIN(‘XMLNSC’) PARSE (xmlBlob, InputProperties.Encoding, 1208);

Mapping from XML to JSON

The following code maps data from XML to JSON.

This sample came from something I found on GIT hub from Sriharsha :
https://github.com/sriharsha-at-git/Student2Employee_APP

CREATE COMPUTE MODULE CalifornianCompute_Compute
  CREATE FUNCTION Main() RETURNS BOOLEAN
  BEGIN
    CALL CopyMessageHeaders();

CREATE LASTCHILD OF OutputRoot DOMAIN ‘JSON’;

    SET OutputRoot.JSON.Data.CaliforniaIT.Name.FirstName = InputRoot.XMLNSC.Applicants.Student[vCurrentStudents].Firstname;
    SET OutputRoot.JSON.Data.CaliforniaIT.Name.LastName = InputRoot.XMLNSC.Applicants.Student[vCurrentStudents].Lastname;
    SET OutputRoot.JSON.Data.CaliforniaIT.Age = Environment.Variables.Age

    SET OutputLocalEnvironment.Destination.MQ.DestinationData[1].queueName = CALIFORNIA_LABEL||’.EDU’;
    RETURN TRUE;
  END;
END MODULE;

Throwing a user defined exception

The following code throws a user defined exception.

The original sample comes from this project:
https://github.com/sriharsha-at-git/Generic-ESQL-Utilities/blob/master/exceptions.esql

CREATE PROCEDURE throw(IN exceptionText CHAR) — Throws an exception
BEGIN
THROW USER EXCEPTION Message 3012 VALUES( exceptionText );
END;

The following examples are from from:

https://nokstechnotes.blogspot.com/2017/10/esql-snippets.html

Convert XMLNSC to BLOB

DECLARE myChar CHAR CAST(myBLOB AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);

Convert BLOB to CHAR

DECLARE myChar CHAR CAST(myBLOB AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);

Convert CHAR to BLOB

DECLARE myBlob BLOB CAST( myChar AS BLOB CCSID InputRoot.Properties.CodedCharSetId);

Convert BLOB to XMLNSC

CREATE LASTCHILD OF OutputRoot.XMLNSC DOMAIN(‘XMLNSC’) PARSE(myBlob, InputRoot.Properties.Encoding, InputRoot.Properties.CodedCharSetId);

Left Padding

RIGHT(’0000000000′ || CAST(field AS CHAR),10);

Nil Element & Namespace Declaration

SET OutputRoot.XMLNSC.ns:myElement.(XMLNSC.NamespaceDecl)xmlns:”xsi” =’http://www.w3.org/2001/XMLSchema-instance’;
SET OutputRoot.XMLNSC.ns:myElement.(XMLNSC.Attribute)xsi:nil = ‘true’;

Convert Payload to a String

DECLARE myBlob BLOB;
SET myBlob = ASBITSTREAM(InputRoot.XMLNSC CCSID InputRoot.Properties.CodedCharSetId ENCODING InputRoot.Properties.Encoding);
DECLARE myChar CHAR CAST(myBlob AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);

Backup a JSON Payload without losing arrays

CREATE LASTCHILD OF OutputRoot DOMAIN(‘JSON’) TYPE Name NAME ‘JSON’;
CREATE FIELD OutputRoot.JSON.Data IDENTITY(JSON.Object)Data;
SET OutputRoot.JSON.Data = InputLocalEnvironment.Backup.Data;

Pass Parameters to an HTTP request Node

OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = ‘param1′;

Convert BLOB to JSON

CREATE LASTCHILD OF OutputRoot DOMAIN(‘JSON’) PARSE(InputRoot.BLOB.BLOB);

Select value from an ESQL array

SET name = the(select item fieldvalue(r.Name) from OutputLocalEnvironment.Variables.Person[] as r where r.Id = nameId);

Websphere Message Broker – Code samples