IBM Integration Bus- ESQL Code Snippets

ESQL Code Snippets

1. Convert XMLNSC to BLOB 

Sometimes you may need to convert your payload to a BLOB(Binary Large Object). To be able to accomplish this the ASBITSTREAM function has to be used. The ASBITSTREAM function will return a bit stream representation of your payload. When I need to put my payload to MQ queue I convert it to a BLOB.

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

2. Convert BLOB to CHAR

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

3. Convert CHAR to BLOB

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

4. Convert BLOB to XMLNSC 

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

5. Left Padding

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

The above will left zero pad field to a 10 long field.

6. 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';   

7. 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);  

8. 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;   

9.Pass Parameters to an HTTP request Node 

 OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'param1';  

Each parameter must be individually set.

10. Convert BLOB to JSON 

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

11. Select value from an ESQL array 

This statement allows you to select the name without iterating through OutputLocalEnvironment.Variables.Person[] where the Id value matches the nameId.

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




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