ESQL short course
ESQL
• Extends the constructs of the SQL language to define the behavior of
nodes in a message flow
– Test, calculate, and manipulate fields in logical message
– Reference message header fields
– Change field properties
• Based upon standard SQL 3
Examples:
RETURN [Link] = 'IBM';
SET [Link] = 'IBM';
SET [Link]=InputBody."[Link]";
OrderMsg sample message
<OrderMsg>
<OrderData Nr=”300524” Date=”2001-11-05”/>
<Customer Nr=”123456” FirstName=”Andrew” LastName=”Smith” />
<OrderItem Nr=”111” Price=”27.95” InStock=”100”>
<Quantity>2</Quantity></OrderItem>
<OrderItem Nr=”222” Price=”8765.12”>
<Quantity>2</Quantity></OrderItem>
<OrderItem Nr=”333” Price=”3.75” InStock=”456”>
<Quantity>10</Quantity>
<Discount>3</Discount></OrderItem></OrderMsg>
Some ESQL syntax
• ESQL keywords not case-sensitive
SET set SeT.....
• CorrelationNames and field references are case-sensitive
OutputRoot, [Link] .....
• ‘Character constants’ and literals enclosed with single quotation
marks (‘ ’)
• “Field references” enclosed with double quotation marks (“ ”) if:
– Special characters in name, such as a blank space
– Name is an ESQL reserved word
• Statement delimiter: semicolon (;)
• Comments
one line: -- (two dashes)
block: /*..comment here ..*/
ESQL data types: Numeric and string
• DECIMAL
precision 1 to 31, scale 0 to 30
SQL_C_CHAR
• FLOAT
1.23e-45 6.7890E2 (64bit)
SQL_C_DOUBLE
• INTEGER, INT
+123 -890 0x123ab 0X3B
64-bit 2-complement form
SQL_C_LONG
• BIT
b'10110'
• BLOB
x'3C493E'
8-bit bytes, 2 digits per byte
SQL_C_BINARY
• CHARACTER
'ABC '='ABC'
SQL_C_CHAR
DECLARE MyDec DECIMAL 12345678.1111112;
SET OutputRoot."BLOB"."BLOB"=x'3C493E';
ESQL data types: Date, time, boolean
• DATE
DATE '2001-10-03'
SQL_C_DATE
• TIME
TIME '23:09:01.123456'
SQL_C_TIME
• TIMESTAMP
TIMESTAMP '2001-10-03
23:09:01.123456'
SQL_C_TIMESTAMP
• GMTTIME, GMTTIMESTAMP
Greenwich Mean Time
• INTERVAL
either YEAR and MONTH
variable length
or DAY HOUR MINUTE SECOND
fixed length
INTERVAL '90' MINUTE
INTERVAL '1-06' YEAR TO MONTH
• BOOLEAN
TRUE, FALSE, UNKNOWN (NULL)
SQL_C_BIT
Declare V1 Interval (CURRENT_DATE - Date '2000-01-01') DAY;
Declare V2 Interval (CURRENT_TIME - Time '09:00:00') HOUR TO
SECOND;
Moving reference variables
• Move cursor
PARENT
PREVIOUSSIBLING
NEXTSIBLING
FIRSTCHILD
LASTCHILD
DECLARE myRef REFERENCE TO [Link];
MOVE myRef FIRSTCHILD;
MOVE myRef NEXTSIBLING NAME 'OrderItem';
MOVE myRef LASTCHILD TYPE [Link];
MOVE myRef PREVIOUSSIBLING REPEAT TYPE;
Check success of MOVE or DECLARE...REFERENCE
WHILE LASTMOVE(myRef) DO...
Field references in tree structures
• Paths are dot separated
[i] Index (Origin 1; <1 is LAST)
[ ] Array of (a list)
( ) Search for type (Name, [Link] and so forth)
: Namespace followed by name
* Any element of any type, name, value (or namespace)
{ } Evaluate this expression (must yield CHAR string)
Examples:
DECLARE i INTEGER CARDINALITY(InputRoot.*[]);
SET rowCust.{'Customer-'||C} = [Link][<2];
SET [Link]:[Link]:postcode = 'ZZ01
4WW';
SET [Link].A.([Link])B = 3;
ESQL operators
• Arithmetic
+ -
numeric, datetime, intervals
* /
numeric, intervals
unary (negation) -
numeric, intervals
concatenate ||
strings
Order of precedence
• Logical
AND, OR, NOT
Boolean
• Comparison
< > <= >= <> =
BETWEEN
3 BETWEEN 1 AND 10
IN
'A' IN (1,'ABC',123.4,'A')
LIKE
multiple characters: %
'ABC' LIKE 'A%' true
single character: _
'ABC' LIKE 'A_' false
IS, IS NOT
test for NULLs
NULL, UNKNOWN, TRUE, FALSE
myVar IS NOT NULL
Deleting and reordering fields
• Delete a field or structure
– Delete and free memory; useful for large messages
DELETE FIELD [Link];
DECLARE myRef REFERENCE TO
[Link][1];
DELETE NEXTSIBLING OF myRef;
– Set to NULL
SET [Link][2]=NULL;
– Detach (cut and paste)
DETACH myRef;
• Reorder: paste detached structure to new position in message tree
ATTACH myRef TO [Link]
AS FIRSTCHILD;
CREATE
• Create new fields (tags) or attributes
– At any point in message
– A scalar field
SET OutputRoot=InputRoot;
CREATE FIELD [Link]
TYPE [Link] VALUE 'C';
DECLARE myRef REFERENCE TO [Link];
CREATE PREVIOUSSIBLING OF myREF DOMAIN 'MQRFH2';
– Or a structure (per dupli cation)
DECLARE myRef REFERENCE TO [Link];
CREATE FIRSTCHILD OF myRef
FROM [Link][3];
NULL value
• NULL is distinct state
– Not 0 or ' '
– SQL_NULL_DATA
– All ESQL datatypes (except REFERENCE)
• If value is
– Unknown
– Undefined
– Uninitialized
• General rule: If any operand is NULL then value is NULL
• Use NULL to delete field or structure
Example:
SET [Link]=NULL;
SET [Link].F2= [Link];
CAST: Transform into other data type
• CAST can convert character string into BLOB or BIT and vice versa,
if CCSID and ENCODING specified
• ESQL generally does not do automatic casting
– Runtime error if SET MyChar=MyInteger;
– Some implicit CASTs for comparison and arithmetic operators
– Recommendation: If in doubt, CAST
• CAST can use format patterns as in EXCEL and ICU
DECLARE B BLOB X'48656c6c6f';
DECLARE S CHAR CAST(B AS CHAR); Output: 'X''48656c6c6f'''
SET S = CAST(B AS CHAR CCSID 850); Output: 'Hello'
SET S = CAST(CURRENT_DATE AS CHAR FORMAT 'dd-MM-yy')
Output: '24-10-05'
Some functions for date and time
DECLARE D DATE CURRENT_DATE;
DECLARE T TIME CURRENT_TIME;
DECLARE TS TIMESTAMP CURRENT_TIMESTAMP;
DECLARE GT GMTTIME CURRENT_GMTTIME;
DECLARE IV INTERVAL LOCAL_TIMEZONE;
DECLARE I INTEGER;
SET I = EXTRACT(HOUR FROM CURRENT_TIME);
Some string functions
DECLARE I INTEGER;
SET I = POSITION('or' IN 'Hello World'); Output: 8
SET I = LENGTH('Hello World'); Output: 11
DECLARE S CHARACTER;
SET S = SUBSTRING('Hello World' FROM 8 FOR 2); Output: 'or'
SET S = OVERLAY('Hello World'
PLACING 'My' FROM 1 FOR 5); Output: 'My World'
SET S = REPLACE ('Hello','l','xx'); Output: 'Hexxxxo'
SET S = REPLICATE ('Hello',3); Output: 'HelloHelloHello'
SET S = TRANSLATE ('Hello World','eo','a'); Output: 'Hall Wrld'
SET S = TRIM(LEADING '!' FROM '!Hello!'); Output: 'Hello!'
SET S = TRIM(BOTH ' ' FROM ' Hello '); Output: 'Hello'
SET S = UPPER('Hello'); Output: 'HELLO'
SET S = LOWER('Hello'); Output: 'hello'
SET S = 'Hello' || 'World'; Output: 'HelloWorld'
Some numeric functions
CEIL(3.673); Result: Round up to next integer, returns 4.0
FLOOR(3.673); Result: Round down to previous integer, returns 3.0
ROUND(3.673, 1); Result: Round with given precision, returns 3.7
TRUNCATE(3.673, 1); Result: Truncate to given precision, returns 3.6
ABS(-3.673); Result: Absolute value, returns 3.673
MOD(3,7); Result: Modulus (remainder), returns 1
SQRT(4); Result: Square root, returns 2E+1
DECLARE I INTEGER;
SET I = BITAND(7,12); Result: Bitwise AND for integers, returns 4
Other functions: BITNOT, BITOR, BITXOR
Many more mathematical functions for trigonometry, and so forth
Some list functions
•EXISTS Are there any elements in a list (TRUE or FALSE)
•SINGULAR Is there exactly one element in a list? (TRUE or FALSE)
•CARDINALITY How many elements is a list (INTEGER count)
•THE Returns the first element of a list
>--EXISTS--(--ListExpression--)-->>
>--SINGULAR--(--ListExpression--)-->>
>--CARDINALITY--(--ListExpression--)-->>
>--THE--(--ListExpression--)-->>
where ListExpression is any expression which returns a list
•Any SELECT expression,
•LIST constructor
•Field reference having the array indicator [ ]
IF statement
IF [Link] = 'Sales‘
THEN
SET [Link] = 'S';
SET [Link] = '4711';
ELSE
SET [Link] = 'X';
SET [Link] = '9999';
END IF;
CASE function
• Like ?: Expression in C, but more powerful
• Simple form: 1:n test
SET [Link] = CASE [Link]
WHEN 'Sales' THEN 'S'
WHEN 'Distribution' THEN 'D'
ELSE 'X'
END;
• Searched form: n:n test
SET [Link] = CASE
WHEN [Link] = '007' THEN 'TopSecret'
WHEN [Link] > 100000 THEN 'HighValue'
ELSE 'Normal'
END;
• Specialized: NULLIF
SET [Link] =
NULLIF([Link],'007')
CASE statement
• Like SWITCH statement in C, but more powerful
• Evaluates and executes multiple statements
• Simple form: 1:n test
CASE [Link]
WHEN 'Sales' THEN SET [Link] = 'S';
WHEN 'Distribution' THEN CALL handleDistribution();
ELSE
CALL handleUnknown();
RETURN;
END CASE;
• Searched form: n:n test
CASE
WHEN [Link] = '007' THEN
SET [Link]='TopSecret';
WHEN [Link] > 100000
THEN CALL handleHighValue();
END CASE;
WHILE
Use a WHILE loop to repeat a sequence of statements, for example:
DECLARE I INTEGER 1;
WHILE I <= 10 DO
SET [Link][I] = I;
SET I = I + 1;
END WHILE;
Procedures
• Subroutines with IN, OUT, and INOUT parameters and optional RETURNS value
– Can be used recursively
– Language can be ESQL, Java or database (stored procedure)
CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer
CHARACTER)
LANGUAGE ESQL
BEGIN
SET answer = answer||’Reached Field...Name:’||FIELDNAME(root);
DECLARE cursor REFERENCE TO root;
MOVE cursor FIRSTCHILD;
IF LASTMOVE(cursor) THEN
SET answer = answer || ’Field has children... drilling down ’;
ELSE
SET answer = answer || ’Listing siblings... ’;
END IF;
WHILE LASTMOVE(cursor) DO
CALL navigate(cursor, answer);
MOVE cursor NEXTSIBLING;
END WHILE;
SET answer = answer || ’Finished siblings... Popping up ’;
END;
Inserting, updating, and deleting fields
• SET modifies field or structure if it exists
• SET appends field or structure (and the entire path) if it does not
exist
• SET...=NULL deletes field or structure
• Order of ESQL statements determines field order
Example:
CALL CopyMessageHeaders();
SET [Link].A.B.C = 1;
SET [Link].E = 2;
SET [Link].D = 3;
SET [Link].E.B.C = 4;
SET [Link].A.B.C = 5;
SET [Link].E.B = NULL;
Powerful ESQL syntax
PRODUCTS table:
PRODNO DESCR
333 Grapes
222 Oranges
111 Apples
ESQL:
SET [Link][]=
(SELECT [Link], [Link] AS Description
FROM [Link][] as I,
[Link] as D
WHERE [Link] = [Link]);
Input Message:
<OrderMsg>
<OrderItem>
<Nr>111</Nr>
<Price>27.95</Price>
<Quantity>2</Quantity>
</OrderItem>
<OrderItem Discount="3">
<Nr>333</Nr>
<Price>100.75</Price>
<Quantity>1</Quantity>
</OrderItem>
</OrderMsg>
Output Message:
<OrderMsg>
<OrderItem>
<Nr>111</Nr>
<Description>Apples</Description>
</OrderItem>
<OrderItem>
<Nr>333</Nr>
<Description>Grapes</Description>
</OrderItem>
</OrderMsg>
Typical node ESQL
• Compute, Filter, and Database nodes are associated with one and
only one ESQL module
– Must be module for the type of node
CREATE FILTER MODULE <MyFilterModule>.......
– Modules can be reused by other nodes
• Each module contains a function called Main which is the entry point
at which the processing of the node starts
• RETURN value controls message propagation to output terminals
– Filter node
return TRUE; return FALSE; return UNKNOWN;
Propagates to respective terminal
RETURN;
Propagates to FAILURE terminal
– Compute and Database node
RETURN; and RETURN TRUE; Propagates to OUT terminal
RETURN FALSE or UNKNOWN Message is not propagated
Some ESQL syntax
• ESQL keywords not case-sensitive
SET set SeT.....
• CorrelationNames and field references are case-sensitive
OutputRoot, [Link] .....
• “Character constants” and literals enclosed with apostrophes
• “Field references” enclosed with quotation marks if:
– Special characters in name, such as blank
– Name is an ESQL reserved word
• Statement delimiter: ; (semicolon)
• Comments
one line: -- (two dashes)
block: /*..comment here ..*/
Variables
• Scalar and fixed data type
• Tree structures (ROW and REFERENCE)
>-DECLARE-+----Name-----+--+----------+-DataType-+--
+---------------+-->>
+--<<--,--<<--+ +-CONSTANT-+ |+-InitialValueExpr-+
+--NAMESPACE----------+
+--NAME---------------+
DECLARE var, I INTEGER 1;
DECLARE addr NAMESPACE ‘[Link]
DECLARE course CONSTANT CHAR ‘WM663’;
Variable scope, lifetime, and sharing
• Scope: Where the variable appears
Examples: node, flow, execution group, broker
• Lifetime: Variable duration
Examples: code block, module, thread
• Sharing: Thread visibility
– Flow or execution group stopping ends SHARED variable lifetime
– ATOMIC blocks can serialize access to SHARED variables
Example:
DECLARE s_counter SHARED INT 1;
……
BEGIN ATOMIC
SET s_counter = s_counter+1;
END;
Special ESQL data types for tree structures
• ROW: named tree with dynamic data type for leaf nodes
– Copies or subsets of other ROWs > Like Root
– SELECT results
– Built using ROW() function
DECLARE rowCust ROW [Link];
SET [Link] = ROW('Hursley' AS Town, 'UK' AS
Country);
– REFERENCE: tree cursor
Warning: reference to non-existing field points to Root, so check success of
declaration with FIELDNAME or LASTMOVE
DECLARE refCust REFERENCE TO [Link];
IF LASTMOVE(refCust) THEN……
IF FIELDNAME(refCust) <> ‘Customer’ THEN... .
– SET refCust not allowed
Sample Compute node ESQL for MRM
CREATE COMPUTE MODULE Output_in_3_formats_with_missing_elements
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
SET [Link]='My_Set';
SET [Link]='AddressesMsg';
-- omit HomeAddress fields
SET [Link][1]='Mail Point 135';
SET [Link][2]='Hursley Park';
SET [Link]='UK';
SET [Link]='SO21 2JN';
SET [Link]='Binary1';
PROPAGATE DELETE NONE;
SET [Link]='XML1';
PROPAGATE DELETE NONE;
SET [Link]='Text1';
RETURN TRUE;
END;
1. ESQL to fill AddressesMsg and output same content in three
physical formats.
2. PROPAGATE DELETE NONE propagates the message but does not
delete the output buffer.
3. RETURN TRUE statement is identical to PROPAGATE statement
ESQL Database
Insertion:
INSERT INTO [Link]
(PRODUCT_NAME, ITEM_PRICE, STATUS)
VALUES ([Link],
[Link],'OPEN');
PASSTHRU example
• PASSTHRU function to issue complex SELECTS
Example:
SET [Link][]= PASSTHRU(
'SELECT WORKDEPT, MAX(SALARY) AS MaxSalary
FROM [Link]
GROUP BY WORKDEPT
HAVING MAX(SALARY) > ?'
TO [Link]
VALUES([Link]));
•PASSTHRU statement to issue administrative commands to
Database
•Use parameter markers for performance
THE (SELECT …)
• SELECT returns a list
SET [Link][]=
(SELECT [Link]
from [Link] as E);
Msg
Name LASTNAME Haas
Name LASTNAME Thompson
• THE (SELECT …) returns the first element of a list
SET [Link] =
THE(SELECT [Link]
from [Link] as E);
Msg Name LASTNAME Haas
SELECT (ITEM …)
• SELECT (ITEM..) gets value only
– Without the ITEM keyword, the database table column name or XML tag will
be used as message tag
– Can be overridden by AS keyword in SELECT statement
SET
[Link][]=
(SELECT [Link] from
[Link] as E);
Msg
Name LASTNAME Haas
Name LASTNAME Thompson
SET
[Link][]=
(SELECT ITEM [Link] from
[Link] as E);
Msg
Name Haas
Name Thompson