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

IBM Integration Bus - An unsupported value type ''NVARCHAR2'' was returned for column ''XXXX''.

If we have the the issue 'An unsupported value type ''NVARCHAR2'' was returned for column ''XXXX''.', we can workaround with "Java Compute" Node.

public class HTTPInputMessageFlow_JavaCompute extends MbJavaComputeNode {

public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbOutputTerminal alt = getOutputTerminal("alternate");

MbMessage inMessage = inAssembly.getMessage();
MbMessageAssembly outAssembly = null;
try {
// create new message as a copy of the input
MbMessage outMessage = new MbMessage(inMessage);
outAssembly = new MbMessageAssembly(inAssembly, outMessage);
// ----------------------------------------------------------
// Add user code below
                        

// Obtain a java.sql.Connection using a JDBC Type4 datasource - in this example for a 
        // JDBC broker configurable service called "MyDB2"  

        Connection conn = getJDBCType4Connection("MyDB2",
                     JDBC_TransactionType.MB_TRANSACTION_AUTO);

        // Example of using the Connection to create a java.sql.Statement  
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
        ResultSet srs0 = stmt.executeQuery("SELECT NAME, CITY FROM MySchema.MyTable");    

        stmt.executeUpdate("UPDATE MySchema.MyTable SET CITY = \"Springfield\" WHERE Name = \"Bart\"");
       
        ..............
      } catch (SQLException sqx ){
        sqx.printStackTrace();
      }
// End of user code
// ----------------------------------------------------------
} catch (MbException e) {
// Re-throw to allow Broker handling of MbException
throw e;
} catch (RuntimeException e) {
// Re-throw to allow Broker handling of RuntimeException
throw e;
} catch (Exception e) {
// Consider replacing Exception with type(s) thrown by user code
// Example handling ensures all exceptions are re-thrown to be handled in the flow
throw new MbUserException(this, "evaluate()", "", "", e.toString(),
null);
}
// The following should only be changed
// if not propagating message to the 'out' terminal
out.propagate(outAssembly);

}

}

Reference: https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ac30494_.htm

IBM Integration Bus - Assign node with SELECT command in ESQL

For Ex, We have the input below.
<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
<food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
   Two of our famous Belgian Waffles with plenty of real maple syrup
   </description>
    <calories>650</calories>
</food>
<food>
    <name>Strawberry Belgian Waffles</name>
    <price>$7.95</price>
    <description>
    Light Belgian waffles covered with strawberries and whipped cream
    </description>
    <calories>900</calories>
</food>
<food>
    <name>Berry-Berry Belgian Waffles</name>
    <price>$8.95</price>
    <description>
    Belgian waffles covered with assorted fresh berries and whipped cream
    </description>
    <calories>900</calories>
</food>
<food>
    <name>French Toast</name>
    <price>$4.50</price>
    <description>
    Thick slices made from our homemade sourdough bread
    </description>
    <calories>600</calories>
</food>
<food>
    <name>Homestyle Breakfast</name>
    <price>$6.95</price>
    <description>
    Two eggs, bacon or sausage, toast, and our ever-popular hash browns
    </description>
    <calories>950</calories>
</food>
</breakfast_menu>

We just want create the output with the price of food that have name is "Homestyle Breakfast", We could use the SELECT command  in ESQL like below.

SET OutputRoot.XMLNSC.food.price= THE(SELECT FIELDVALUE(c.price) AS price FROM InputRoot.XMLNSC.breakfast_menu.food[] AS c WHERE c.name ='Homestyle Breakfast');

The SELECT command for querying the food with name is 'Homestyle Breakfast' and return a LIST of matched ROW. The "THE" command with the first element in the LIST and return for assigning to OutputRoot.XMLNSC.food.price

We will have the output like:

<food>
       <price>$6.95</price>
</food>

Without the "THE" command, we have use [] like below

SET OutputRoot.XMLNSC.food[]= (SELECT FIELDVALUE(c.price) AS price FROM InputRoot.XMLNSC.breakfast_menu.food[] AS c WHERE c.name ='Homestyle Breakfast');

The same output:

<food>

       <price>$6.95</price>

</food>

Oracle OSB 12C - OSB Always Adds a "Charset=Iso-8859-1" to the Content-Type Header

If you have the issue  Content-Type Header in OSB 12c  because OSB Always Adds a "Charset=Iso-8859-1" to the Content-Type Header, here are the solution.

For 12c you'll need to apply below patches:

• Patch 25434715: Forward Porting Bug 18729796 to 12.2.1.x.x release, which  addresses the HTTP inbound case.

• Patch 26628960: Cont of Bug 26591735 (Remove charset=utf-8 in Content-Type for Outbound...), which addresses the HTTP Outbound case.

• And add Startup parameter: -Dcom.bea.wli.sb.kernel.charsetRequired=false( in setDomainEnv.sh)
• Remove encoding in business if you have

IBM Integration Bus- Database Definition

Securing database connections

mqsisetdbparms broker_name -n jdbc::security_identity -u userID -p password

Download JDBC driver for type 4 connections

Example for Oracle: https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html 

Setting up a JDBC provider for type 4 connections

mqsicreateconfigurableservice LOCALBROKER -c JDBCProviders -o JDBCOracleDBConnector -n connectionUrlFormat,connectionUrlFormatAttr1,connectionUrlFormatAttr2,connectionUrlFormatAttr3,connectionUrlFormatAttr4,connectionUrlFormatAttr5,databaseName,databaseType,databaseVersion,description,environmentParms,jarsURL,jdbcProviderXASupport,maxConnectionPoolSize,portNumber,securityIdentity,serverName,type4DatasourceClassName,type4DriverClassName -v jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1],MYDB,,,,,MYDB,Oracle,11.2,"Oracle DEV",default_none,"C:\temp\jars",false,0,5001,security_identity,dev72.hdd.com,oracle.jdbc.xa.client.OracleXADataSource,oracle.jdbc.OracleDriver

Please note: Please correct  the below information 

- securityIdentity( ex: security_identity is same with step Securing database connections)

- jarsURL(ex: "C:\temp\jars" is folder contains the jdbc driver jar files).

- connectionUrlFormat( ex: "jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]"  is jdbc url  to connect to  the BD).

-  databaseName( Ex: "MYDB").

- serverName( Ex: "dev72.hdd.com")

- type4DatasourceClassName( Ex: com,oracle.jdbc.xa.client.OracleXADataSource)

- type4DriverClassName( Ex: oracle.jdbc.OracleDriver).

- Provider name( Ex: JDBCOracleDBConnector).

Create Database Definition

Create database definition( note that  provider name should be same with jdbc provider name as ablove, ex: JDBCOracleDBConnector )

IBM Integration Bus - How to debug in IBM integration tookit

For debugging, We have to set the debug port with comment below

mqsichangeproperties integrationNodeName -e default  -o ComIbmJVMManager -n jvmDebugPort -v 3920

The port(ex: 3920) is used for debug

We have to stop and start node for effect.

Now, choose the "Launch Debuger", add breakpoint and enjoy.

IBM Integration Bus - Webphere MQ

For using the Webphere MQ in  IIB, We have installed the MQ Client at least. Download MQ Client in https://developer.ibm.com/messaging/mq-downloads/

Create the MQ Security Identity:

Create the MQ Security Identity&nbsp; with below command line:

mqsisetdbparms integrationNodeName -n mq::queueid-u username -p password

The command above, create the MQ Security Identity name is queueid

Config the MQ node

After finish, We create 1 flow, and drag and drop a MQ node( Like: MQInput,...)

We have choose the Properties of Node. In Basic Tab, input the Queue Name

In "MQ Connection Tab":

• Select "MQ client connection Properties" in Connection
• Input Queue Manager, Host name, port, Channel Name and Security Identity( ex: queueid that is create before)