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

You may be interested in the following posts:

Good IIB ESQL Coding Practice

Properties versus MQMD folder behavior

Create a JSON Array in IBM Integration Bus

Create an SAP Adapter in WebSphere Integration Bus

Before you can develop message flows that use WebSphere SAP Adapter nodes, you must add prerequisite files to the runtime environment.

Ensure that you have the following files .
sapidoc3.jar
sapjco3.jar
sapjco3.dll

Save these files in a library e.g. C:/SAP_LIB

These files are available from the SAP Service Marketplace - . You must have an SAPNet account to be able to access this web site.