Mastering ESQL In One Day

Can someone master ESQL in just one day? YES, ESQL is not like any other programming language which has lot of statements and libraries to know. It is the most simplest language that I’ve ever seen.

The statements are limited, syntax is simple and learning is super easy. As an ACE/IIB developer, what do you do most of the time? Writing ESQL code right? Then, are you harvesting the real power of ESQL?

This one day training program is going to show you the real power of ESQL. It is going to explain you from the basics to advanced statements with examples

Here is what we’re going to cover in the course.

  1. Introduction
  2. Data Types
    1. Boolean
      1. TRUE
      2. FALSE
      3. UNKNOWN
    2. DateTime
    3. NULL
    4. Numeric
      1. DECIMAL
      2. FLOAT
      3. INTEGER
    5. Reference
    6. String
      1. BIT
      2. BLOB
      3. CHARACTER
  3. Correlation Names
    1. Root
    2. FirstChild
    3. LastChild
    4. FirstSibling
    5. NextSibling
    6. PreviousSibling
    7. LastSibling
  4. Message Components
    1. Properties
    2. Message Domain
    3. Environment
    4. Local Environment
    5. Exception List
  5. Variables
    1. Declaration
    2. Normal
    3. External
    4. Shared
    5. Scope
  6. Operators
    1. Simple Comparison 
      1. >, >=, <, <=, <> & =
    2. Complex Comparison
      1. BETWEEN
      2. EXISTS
      3. IN
      4. IS
      5. LIKE
      6. SINGULAR
    3. Logical
      1. AND
      2. OR
      3. NOT
    4. Numeric
    5. String
  7. Field References
  8. Field Types
    1. NAME
    2. TYPE
    3. NAMEVALUE
  9. Conditional Statements
    1. IF….ELSEIF…..ELSE
    2. CASE
      1. Simple
      2. Searched
  10. Looping Statements
    1. REPEAT UNTIL
    2. WHILE
    3. FOR
    4. LABELED LOOP
    5. ITERATE
    6. LEAVE
    7. RETURN
  11. Error Handling
    1. THROW
    2. DECLARE HANDLER
  12. Modules
    1. Procedures
    2. Functions
    3. CALL
    4. MOVE
    5. LASTMOVE
  13. Handling NULL values
  14. Propagating Multiple Messages
  15. List Functions
    1. CARDINALITY
    2. EXISTS
    3. SINGULAR
    4. THE
    5. ITEM
  16. Complex Functions
    1. CAST
    2. FORMAT
    3. SELECT
      1. Simple
      2. Complex
    4. ROW
    5. LIST
    6. ROW & LIST Combined
  17. Working with Database
    1. INSERT
    2. SELECT
    3. UPDATE
    4. DELETE
    5. PASSTHRU
    6. Stored Procedures
  18. Manipulating repeating fields
  19. Calling Java methods
  20. String Functions
    1. CONTAINS
    2. ENDSWITH
    3. LEFT
    4. LENGTH
    5. LOWER
    6. LTRIM
    7. OVERLAY
    8. POSITION
    9. REPLACE
    10. REPLICATE
    11. RIGHT
    12. RTRIM
    13. SPACE
    14. STARTSWITH
    15. SUBSTRING
    16. TRANSLATE
    17. TRIM
    18. UPPER
  21. Miscellaneous
    1. ATTACH
    2. DETACH
    3. PATH
    4. BROKER SCHEMA
    5. DECLARE
    6. CREATE FIELD
    7. DELETE FIELD
    8. COALESCE 
    9. SLEEP
    10. UUIDASCHAR
    11. ASBITSTREAM
    12. EXTRACT

As the trainer has more than 15+ years of experience in ESQL, you can ask him any questions related to ESQL. If you’ve complex requirement, ask him how to resolve it during the training. So, why wait ?

Enroll into the training program and become a Master in ESQL in One Day.

Event Address: Online
Contact us at (612) 305-8684 or support@vaithu.com

Course Fee: USD : 75$/ INR 5000

To register the training, click here : shorturl.at/bpuLO

Alternative Link : https://docs.google.com/forms/d/e/1FAIpQLSd9GuiYIV7Kh18MwLUjqbwqFHL-n4RGl68Goaxm3LShMvV03A/viewform?entry.2109138769=Yes

How to read excel files using Java Compute Node

Sometimes, we need to read excel files and there is no native connector in IBM Integration Bus. So, the obvious solution is to read them using Java. The most commonly used library is Apache POI.

Here I’ve written a simple program which reads the sheets ( assuming it has a header and multiple rows) and attach them to Environment tree. The java code is

public class ReadExcel_JCN extends MbJavaComputeNode {

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

		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
			
			MbElement environment = inAssembly.getGlobalEnvironment().getRootElement();
			
			File excelFiles = new File((String) getUserDefinedAttribute("ExcelDir"));
			
			Workbook workbook = null;
			
			DataFormatter dataFormatter = new DataFormatter();
			
			List<String> headerNames = null;
			String cellValue = null;
			MbElement cellElm = null;
			
			for (File excel : excelFiles.listFiles()) {
				
				workbook = WorkbookFactory.create(excel); 
				
				for (Sheet sheet : workbook) {
					
						MbElement sheetElm = environment.createElementAsLastChild(MbElement.TYPE_NAME, sheet.getSheetName(), null);
						headerNames = new ArrayList<>();
						for (Row row : sheet) {
							
							if (row.getRowNum() != 0) {
								cellElm = sheetElm.createElementAsLastChild(MbElement.TYPE_NAME, "rows", null);
							}
							for (Cell cell : row) {
								
								cellValue = dataFormatter.formatCellValue(cell);
								if (row.getRowNum() == 0) {
									headerNames.add(cellValue);
								} else {
									
									cellElm.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, headerNames.get(cell.getColumnIndex()), cellValue);
								}
							}
						}
						
						if (sheetElm.getLastChild() == null) {
							sheetElm.detach();
						}
						
				}
				
				workbook.close();
			}
			

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

	}

}

If you’re looking for any support in IIB, please contact us support@vaithu.com.

About the Author

He has worked for various clients and developed more than 800+ message flows. He is an expert in ESQL, Java Compute Node and DFDL. If you’ve any complex code to be written in ESQL, feel free to contact him at +1 6123058684.

Picking files randomly for mock test

There are situations where we need mimic simulation for various scenario like  success, error, failure etc. We do the same by creating a mock test flow which drops files into the destination randomly and here is the ESQL code to pick those random files.

CREATE COMPUTE MODULE RandomFiles
  CREATE FUNCTION Main() RETURNS BOOLEAN
  BEGIN

    DECLARE R DECIMAL RAND(1);
    DECLARE fileName CHARACTER getRootElement(InputRoot);

    SET fileName = CASE

    WHEN R > 0.0 AND R < 0.3 THEN fileName ||'_success.txt'
    WHEN R > 0.3 AND R < 0.6 THEN fileName ||'_error.txt'
    ELSE fileName ||'_timeout.txt'
    END;

    CALL SetDestinationFileName(OutputLocalEnvironment, fileName);

    RETURN TRUE;
  END;

  CREATE PROCEDURE CopyMessageHeaders() BEGIN
    DECLARE I INTEGER 1;
    DECLARE J INTEGER;
    SET J = CARDINALITY(InputRoot.*[]);
    WHILE I < J DO
      SET OutputRoot.*[I] = InputRoot.*[I];
      SET I = I + 1;
    END WHILE;
  END;

  CREATE PROCEDURE CopyEntireMessage() BEGIN
    SET OutputRoot = InputRoot;
  END;
END MODULE;

CREATE PROCEDURE SetDestinationFileName(IN LocalEnvironment REFERENCE,IN Name CHARACTER)
BEGIN
  SET LocalEnvironment.Destination.File.Name = Name;
END;

CREATE FUNCTION getRootElement (In ipRef REFERENCE ) RETURNS CHARACTER
BEGIN
  RETURN FIELDNAME(ipRef.*:*[<].*:*[<]);
END;

If you are looking for IIB training, ESQL training and support, please connect with us at support@vaithu.com/WhatsApp +1 6123058684.

Local Environment Overriding Procedures in ESQL

Every time, we either Google or go to Help to find the statement to set dynamically destination queue name, file name, file directory and web service url etc and thought of creating a library which contains all these procedures so that it would be very handy and saves lot of time. Here is a list which you can use freely and save your precious time.

If you are looking for IIB training , support please connect with us at support@vaithu.com/ WhatsApp +1 6123058684.

-- COMPLETE ESQL TO Update
-- LocalEnvironment.Variables,
-- LocalEnvironment.Destination
-- LocalEnvironment.WrittenDestination
-- LocalEnvironment.Adapter
-- LocalEnvironment.Database
-- LocalEnvironment.CD and LocalEnvironment.CD.Transfer
-- LocalEnvironment.DecisionServices
-- LocalEnvironment.File
-- LocalEnvironment.FTE and LocalEnvironment.FTE.Transfer
-- LocalEnvironment.JMS
-- LocalEnvironment.Mapping
-- LocalEnvironment.ServiceRegistry
-- LocalEnvironment.SOAP
-- LocalEnvironment.TCPIP 
-- LocalEnvironment/Aggregation/Timeout'
-- MQ NODE OVVERRIDES STARTS---

CREATE PROCEDURE SetQManager (IN ref REFERENCE,IN myQManagerName CHARACTER)
BEGIN
  SET ref.Destination.MQ.DestinationData.queueManagerName = myQManagerName;
END;	 


CREATE PROCEDURE SetQName(IN ref REFERENCE,IN myQueueName CHARACTER)
BEGIN
  SET ref.Destination.MQ.DestinationData.queueName = myQueueName;
END;
---------------------------------------------------------------------------------------------
-- MQGet node - QueueName InitialBufferSize MQGMO.*

CREATE PROCEDURE SetMQGetQName (IN InputLocalEnvironment REFERENCE,IN MQGETQueue CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.QueueName = MQGETQueue;
END;

CREATE PROCEDURE SetMQGETWaitInter(IN InputLocalEnvironment REFERENCE,IN InterWaitTime CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.MQGMO.WaitInterval = InterWaitTime;
END;

CREATE PROCEDURE SetMQGETOptions(IN InputLocalEnvironment REFERENCE, IN MQGMO_ACCEPT_TRUNCATED_MSG CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.MQGMO.Options = MQGMO_ACCEPT_TRUNCATED_MSG;
END;

CREATE PROCEDURE SetMQGETBuffer(IN InputLocalEnvironment REFERENCE, IN BufferSize CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.InitialBufferSize = BufferSize;
END;
-- MQGet node Overrides ----ENDS-----
-- HTTPAsyncRequest node Ovverrides
-- Compression Overrides the Use compression property
CREATE PROCEDURE SetHTTPAsyReqeqCompression(IN OutputLocalEnvironment REFERENCE,IN gzip CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Compression = gzip;
END;
-- To set a minimum size (in bytes) at which compression is applied, use the following override:
CREATE PROCEDURE SetHTTPAsyReqeqMinCompressionSize(IN OutputLocalEnvironment REFERENCE,IN CompressionSize CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.MinimumCompressionSize = CompressionSize;
END;
--RequestLine.RequestURI Overrides the RequestURI, which is the path after the URL and port
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = '/abc/def';

CREATE PROCEDURE SetHTTPAsyReqRequestURI(IN OutputLocalEnvironment REFERENCE ,IN URI CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = URI;
END;
--RequestLine.HTTPVersion Overrides the HTTP version property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = 'HTTP/1.1';
CREATE PROCEDURE SetHTTPAsyReqHTTPVersion(IN OutputLocalEnvironment REFERENCE ,IN HTTPVersion CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = HTTPVersion;
END;
--RequestLine.Method Overrides the HTTP method property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = 'GET';

CREATE PROCEDURE SetHTTPAsyReqMethod(IN OutputLocalEnvironment REFERENCE ,IN GetorPost CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = GetorPost;
END;
--RequestURL Overrides the Web service URL property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestURL = 'http://ibm.com/abc/';
CREATE PROCEDURE SetHTTPAsyReqRequestURL(IN OutputLocalEnvironment REFERENCE ,IN RequestURL CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = RequestURL;
END;
--SSL authentication alias Overrides the SSL authentication alias for the client-side of an HTTP connection on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = 'Key1Alias';
CREATE PROCEDURE SetHTTPAsyReqKeyAlias(IN OutputLocalEnvironment REFERENCE ,IN KeyAlias CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = KeyAlias;
END;
--SSLProtocol Overrides the SSLProtocol. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = 'TLS';
--Valid values are: SSL, SSLv3, and TLS.
CREATE PROCEDURE SetHTTPAsyReqSSLProtocol(IN OutputLocalEnvironment REFERENCE ,IN SSL0rSSLv3orTLS CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSL0rSSLv3orTLS;
END;
--SSLCiphers Overrides the Allowed SSL Ciphers property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers =
-- 'SSL_RSA_FIPS_WITH_3DES_EDE_CBC_SHA';

CREATE PROCEDURE SetHTTPAsyReqSSLCiphers(IN OutputLocalEnvironment REFERENCE ,IN SSLCiphers CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSLCiphers;
END;
--UserContext You can store BLOB context data in the following location in the local environment. The HTTPAsyncResponse node can later retrieve this data.
--SET OutputLocalEnvironment.Destination.HTTP.UserContext = x'aabbccddeeff11223344556677889900';
--Data stored in the UserContext must be in BLOB format.
CREATE PROCEDURE SetHTTPAsyReqUserContext(IN OutputLocalEnvironment REFERENCE ,IN UserContext BLOB)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = UserContext;
END;
--Enable CRL checking Overrides the Enable Certificate Revocation List checking property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = 'true';
CREATE PROCEDURE SetHTTPAsyReqEnableCRLCheck(IN OutputLocalEnvironment REFERENCE ,IN EnableCRLCheck CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = EnableCRLCheck;
END;

--ProxyConnectHeaders Specifies additional headers that are used if the outbound request is an SSL connection through a proxy. These additional headers are sent with the initial CONNECT request to the proxy. For example, you can send proxy authentication information to a proxy server when you are using SSL. You can send multiple headers but each one must be separated by a carriage return and a line feed (ASCII 0x0D 0x0A), in accordance with RFC2616; for example:
--DECLARE CRLF CHAR CAST(X'0D0A' AS CHAR CCSID 1208);
--SET OutputLocalEnvironment.Destination.HTTP.ProxyConnectHeaders =
--'Proxy-Authorization: Basic Zm5lcmJsZTpwYXNzd29yZA==' || CRLF ||
--'Proxy-Connection: Keep-Alive' || CRLF;
--This setting is used only if the request is an SSL request through a proxy server. To send proxy authentication information for a non-SSL request, specify the individual headers in the HTTPRequestHeader folder, as shown in the following example:
--SET OutputRoot.HTTPRequestHeader."Proxy-Authorization" =
-- 'Basic Zm5lcmJsZTpwYXNzd29yZA==';
--SET OutputRoot.HTTPRequestHeader."Proxy-Connection" = 'Keep-Alive';
--ProxyURL Overrides the HTTP(S) proxy location property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = 'my.proxy';
--QueryString Allows the setting of outbound query string parameters. Each parameter must be set individually. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'my"Value"1';
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param2 = 'my"Value"2';
--The above ESQL results in the following query string being encoded (according to http://tools.ietf.org/html/rfc3986) and sent with the outbound request:
--?param1=my%22Value%221&param2= my%22Value%222
--If the destination URL already has one or more query parameters, additional parameters specified here are appended to the existing list.
--QueryStringCCSID Specifies that, before encoding, the query string parameters must be converted into a character set other than the default, which is UTF-8. Any query string parameters are first converted into the specified CCSID before the resulting string is encoded, according to RFC3986. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryStringCCSID = 943;
--The above ESQL results in any QueryString parameters being converted to the 943 code page before they are encoded. Note: Any query string parameters must contain the data in unicode.
-- HTTPAsyncRequest node Ovverrides ENDS ----------------------
-- HTTPAsyncResponse node Overrides
--You can retrieve context data that was stored by the HTTPAsyncRequest node from the following location in the local environment:
--SET myVar = InputLocalEnvironment.Destination.HTTP.UserContext;
--Context data is stored as a BLOB. To retrieve context data, assign the variable as a BLOB type or use a CAST. For example:
--DECLARE myVar BLOB;
--SET myVar = InputLocalEnvironment.Destination.HTTP.UserContext;
CREATE FUNCTION GETHTTPResUserContext(IN InputLocalEnvironment REFERENCE) RETURNS BLOB
BEGIN
  RETURN InputLocalEnvironment.Destination.HTTP.UserContext;
END;
-- HTTPAsyncResponse node Overrides ENDS ------------------------
-- HTTPRequest node Overrides
--RequestURL Overrides the Web service URL property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestURL = 'http://ibm.com/abc/';
CREATE PROCEDURE SetHTTPReqRequestURL (IN OutputLocalEnvironment REFERENCE, IN RequestURL CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = RequestURL;
END;
--Timeout Overrides the Request timeout (sec) property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.Timeout = 42;
CREATE PROCEDURE SetHTTPReqTimeout (IN OutputLocalEnvironment REFERENCE, IN Timeout CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Timeout = Timeout ;
END;
--TimeoutMillis Overrides the Request timeout (sec) property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.TimeoutMillis = 5000;
--This property defines the timeout in milliseconds. The value of TimeoutMillis overrides the value for Timeout if both values are set.
CREATE PROCEDURE SetHTTPReqTimeoutMillis (IN OutputLocalEnvironment REFERENCE, IN TimeoutMillis CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.TimeoutMillis = TimeoutMillis;
END;
--ProxyURL Overrides the HTTP(S) proxy location property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = 'my.proxy';
CREATE PROCEDURE SetHTTPReqProxyURL (IN OutputLocalEnvironment REFERENCE, IN ProxyURL CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = ProxyURL;
END;
--RequestLine.RequestURI Overrides the RequestURI, which is the path after the URL and port. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = '/abc/def';
CREATE PROCEDURE SetHTTPReqRequestURI (IN OutputLocalEnvironment REFERENCE, IN RequestURI CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTPRequestLine.RequestURI = RequestURL;
END;
--RequestLine.HTTPVersion Overrides the HTTP version property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = 'HTTP/1.1';
CREATE PROCEDURE SetHTTPReqHTTPVersion (IN OutputLocalEnvironment REFERENCE, IN HTTPVersion CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = HTTPVersion;
END;
--KeepAlive Overrides the Enable HTTP/1.1 keep-alive property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeepAlive = TRUE;
CREATE PROCEDURE SetHTTPReqKeepAlive (IN OutputLocalEnvironment REFERENCE, IN KeepAlive BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeepAlive = KeepAlive;
END;
--RequestLine.Method Overrides the HTTP method property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = 'GET';
CREATE PROCEDURE SetHTTPReqMethod (IN OutputLocalEnvironment REFERENCE, IN Method CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = Method;
END;
--SSLProtocol Overrides the SSLProtocol. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = 'TLS';
--Valid values are: SSL, SSLv3, and TLS.
CREATE PROCEDURE SetHTTPReqSSLProtocol(IN OutputLocalEnvironment REFERENCE, IN SSLorSSLv3orTLS CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSLorSSLv3orTLS;
END;
--SSLCiphers Overrides the Allowed SSL Ciphers property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers =
-- 'SSL_RSA_FIPS_WITH_3DES_EDE_CBC_SHA';
CREATE PROCEDURE SetHTTPReqSSLCiphers (IN OutputLocalEnvironment REFERENCE, IN SSLCiphers CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers = SSLCiphers;
END;
--ProxyConnectHeaders Specifies additional headers that are used if the outbound request is an SSL connection through a proxy. These additional headers are sent with the initial CONNECT request to the proxy. For example, you can send proxy authentication information to a proxy server when you are using SSL. You can send multiple headers but each one must be separated by a carriage return and a line feed (ASCII 0x0D 0x0A), in accordance with RFC2616; for example:
--DECLARE CRLF CHAR CAST(X'0D0A' AS CHAR CCSID 1208);
--SET OutputLocalEnvironment.Destination.HTTP.ProxyConnectHeaders =		
--'Proxy-Authorization: Basic Zm5lcmJsZTpwYXNzd29yZA==' || CRLF ||
--'Proxy-Connection: Keep-Alive' || CRLF;
--This setting is used only if the request is an SSL request through a proxy server. To send proxy authentication information for a non-SSL request, specify the individual headers in the HTTPRequestHeader folder, as shown in the following example:
--SET OutputRoot.HTTPRequestHeader."Proxy-Authorization" =
-- 'Basic Zm5lcmJsZTpwYXNzd29yZA==';
--SET OutputRoot.HTTPRequestHeader."Proxy-Connection" = 'Keep-Alive';
--UseFolderMode Sets the UseFolderMode. Use for bitstream generation; for certain parsers this changes the output bitstream. For example:
--SET OutputLocalEnvironment.Destination.HTTP.UseFolderMode = TRUE;
CREATE PROCEDURE SetHTTPReqUseFolderMode (IN OutputLocalEnvironment REFERENCE, IN UseFolderMode BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = UseFolderMode;
END;
--QueryString Allows the setting of outbound query string parameters. Each parameter must be set individually. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'my"Value"1';
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param2 = 'my"Value"2';
--The above ESQL results in the following query string being encoded (according to http://tools.ietf.org/html/rfc3986) and sent with the outbound request:
--?param1=my%22Value%221&param2= my%22Value%222
--If the destination URL already has one or more query parameters, additional parameters specified here are appended to the existing list.
--QueryStringCCSID Specifies that, before encoding, the query string parameters must be converted into a character set other than the default, which is UTF-8. Any query string parameters are first converted into the specified CCSID before the resulting string is encoded, according to RFC3986. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryStringCCSID = 943;
--The above ESQL results in any QueryString parameters being converted to the 943 code page before they are encoded. Note: Any query string parameters must contain the data in unicode.
--Compression Overrides the Use compression property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.Compression =
-- 'gzip';
CREATE PROCEDURE SetHTTPReqCompression (IN OutputLocalEnvironment REFERENCE, IN Compression CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Compression = Compression;
END;
--To set a minimum size (in bytes) at which compression is applied, use the following override:
--SET OutputLocalEnvironment.Destination.HTTP.MinimumCompressionSize = 1048576;
--SSL authentication alias Overrides the SSL authentication alias property for the client-side of an HTTP connection on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = 'Key1Alias';
CREATE PROCEDURE SetHTTPReqKeyAlias(IN OutputLocalEnvironment REFERENCE, IN KeyAlias CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = KeyAlias;
END;
--Enable CRL checking Overrides the Enable Certificate Revocation List checking property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = 'true';
CREATE PROCEDURE SetHTTPReqEnableCRLCheck (IN OutputLocalEnvironment REFERENCE, IN EnableCRLCheck BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = EnableCRLCheck ;
END;
-- HTTPRequest node Overrides ENDS -----------


--LocalEnvironment.File
--LocalEnvironment.File.Read
--LocalEnvironment.WrittenDestination.File
--LocalEnvironment.Destination.File
--LocalEnvironment.Destination.File.Remote
--LocalEnvironment.Wildcard.WildcardMatch
--LocalEnvironment.FTE
--LocalEnvironment.WrittenDestination.FTE
--LocalEnvironment.Destination.FTE
--LocalEnvironment.CD
--LocalEnvironment.CD.Transfer
--LocalEnvironment.Destination.CD
--LocalEnvironment.WrittenDestination.CD

 

ESQL Exercise -1

In a series of ESQL exercises which makes anyone to start writing ESQL very simple and efficient, here is a sample program to demonstrate the same.

The requirement is, you will get a message which will have multiple items inside a complex type called Purchases. Now you need to send the same message as output but Purchases should have only one item at a time. In other words, you need to split the items and each message will have one item.

The input message is

<?xml version="1.0" encoding="UTF-8"?>
<Invoice>
 <InvoiceNo>7</InvoiceNo>
 <InvoiceDate>2000-12-07</InvoiceDate>
 <InvoiceTime>12:40:00</InvoiceTime>
 <TillNumber>3</TillNumber>
 <Cashier StaffNo='089'>Mary</Cashier>
 <Customer>
   <FirstName>Andrew</FirstName>
   <LastName>Smith</LastName>
   <Title>Mr</Title>
   <DOB>20-01-70</DOB>
   <PhoneHome>01962818000</PhoneHome>
   <PhoneWork />
   <Billing>
     <Address>14 High Street</Address>
     <Address>Hursley Village</Address>
     <Address>Hampshire</Address>
     <PostCode>SO213JR</PostCode>
   </Billing>
 </Customer>
 <Payment>
   <CardType>Visa</CardType>
   <CardNo>4921682832258418</CardNo>
   <CardName>Mr Andrew J. Smith</CardName>
   <Valid>1200</Valid>
   <Expires>1101</Expires>
 </Payment>
 <Purchases>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>The XML Companion</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Addison-Wesley</Publisher>
     <PublishDate>October 1999</PublishDate>
     <UnitPrice>27.95</UnitPrice>
     <Quantity>2</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>A Complete Guide to DB2 Universal Database</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Morgan Kaufmann Publishers</Publisher>
     <PublishDate>April 1998</PublishDate>
     <UnitPrice>42.95</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Hardcover' Edition='0'>JAVA 2 Developers Handbook</Title>
     <ISBN>0782121799</ISBN>
     <Author>Philip Heller, Simon Roberts </Author>
     <Publisher>Sybex, Inc.</Publisher>
     <PublishDate>September 1998</PublishDate>
     <UnitPrice>59.99</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
 </Purchases>
 <StoreRecords/>
 <DirectMail/>
 <Error/>
</Invoice>

and the above message contains, three items under Purchases.

<Purchases>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>The XML Companion</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Addison-Wesley</Publisher>
     <PublishDate>October 1999</PublishDate>
     <UnitPrice>27.95</UnitPrice>
     <Quantity>2</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>A Complete Guide to DB2 Universal Database</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Morgan Kaufmann Publishers</Publisher>
     <PublishDate>April 1998</PublishDate>
     <UnitPrice>42.95</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Hardcover' Edition='0'>JAVA 2 Developers Handbook</Title>
     <ISBN>0782121799</ISBN>
     <Author>Philip Heller, Simon Roberts </Author>
     <Publisher>Sybex, Inc.</Publisher>
     <PublishDate>September 1998</PublishDate>
     <UnitPrice>59.99</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
 </Purchases>

So, there should be three output messages each containing one item and the message structure should be same as Input. Note the Purchases should be after Payment and the order should not be altered.

The below code ESQL does the splitting and sends the output in the desired format.

BROKER SCHEMA com.vaithu.iib
CREATE COMPUTE MODULE ESQLGuru_Compute
  CREATE FUNCTION Main() RETURNS BOOLEAN
  BEGIN
    
    DECLARE ipRef REFERENCE TO InputRoot.[<].[<];
    DECLARE opRef REFERENCE TO ipRef;
    
    FOR I AS ipRef.Purchases.[] DO
      
      CALL CopyEntireMessage();
      MOVE opRef TO OutputRoot.[<].[<].{'Purchases'};
      MOVE opRef PREVIOUSSIBLING;
      SET OutputRoot.[<].[<].{'Purchases'} = NULL;
      CREATE NEXTSIBLING OF opRef AS opRef NAME 'Purchases';
      SET opRef.{FIELDNAME(I)} = I;
      PROPAGATE;
      
    END FOR;
    
    RETURN FALSE;
  END;

  CREATE PROCEDURE CopyMessageHeaders() BEGIN
    DECLARE I INTEGER 1;
    DECLARE J INTEGER;
    SET J = CARDINALITY(InputRoot.*[]);
    WHILE I < J DO
      SET OutputRoot.*[I] = InputRoot.*[I];
      SET I = I + 1;
    END WHILE;
  END;

  CREATE PROCEDURE CopyEntireMessage() BEGIN
    SET OutputRoot = InputRoot;
  END;
END MODULE;

If you are looking for ESQL training, support and optimization tricks please connect with us at support@vaithu.com/WhatsApp @ +1 6123058684.