Create ESQL to customize the behavior of a
Compute, Database, or Filter node within an ESQL file.
Before you
start
To complete this task, you must have completed the following
task:
Within the ESQL file, create a module that
is associated with a node in your message flow. A module can be associated
with only one node of a particular type (Compute, Database, or Filter). Within
the module you can create and use functions and procedures as well as the
supplied statements and functions. You can also create local constants and
variables.
If you have created constants, functions, or procedures at
the broker schema level, you can also refer to these within the module. You
can define routines at a level at which many different modules can use them,
which can save you development time and maintenance effort.
To create
ESQL for a node:
- Switch to the Broker Application Development perspective.
- In the Broker Development view, double-click the message flow that
includes the node for which you want to create ESQL. The
message flow opens in the editor view.
- Right-click the node (which must be Compute, Database, or Filter)
and click Open ESQL. The default ESQL file
for this message flow, <message_flow_name>.esql, is
opened in the editor view (the file is created if it does not already exist).
(If
you have already created the default file and you click Open ESQL,
the file is opened in the editor view and a new module is created and highlighted.)
A skeleton module is created for this node at the end of the ESQL
file. Its exact content depends on the type of node.The following module
is created for a Compute node:
CREATE COMPUTE MODULE <module_name>
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
RETURN TRUE;
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER 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;
The module name is determined by the value that you have
set for the corresponding node property. The default is <message_flow_name>_<node_type>.
The Main function contains calls to two procedures, described below, that
are declared within the Compute node module following the function Main. These
calls are commented out. If you want to include the function that they provide,
uncomment these lines and place them at the appropriate point in the ESQL
that you create for Main.
- CopyMessageHeaders
- This procedure loops through the headers contained in the input message
and copies each one to the output message.
- CopyEntireMessage
- This procedure copies the entire contents of the input message, including
the headers, to the output message.
If you create an ESQL module for a Database node, the following
module is created:
CREATE DATABASE MODULE <module_name>
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
RETURN TRUE;
END;
END MODULE;
For a Filter node, the module is identical to that created for the
Database node except for the first line, which reads:
CREATE FILTER MODULE <module_name>
- Add ESQL to this file to customize the behavior of the node.
You should start by adding ESQL statements within the Main function,
that is after the BEGIN statement, and before RETURN TRUE. You can add DECLARE
statements within the module that are not within the Main function. To add
a new line into the file, press Enter.
To help you to code valid ESQL,
the editor displays a list of valid statements and functions at the point
of the cursor. To invoke this assistance, click . On some systems, you
might also be able to use the key combination Ctrl+Space. Scroll through the
list displayed to find and highlight the one that you want, and press Enter.
The appropriate code is inserted into your module, and the list disappears.
Content
assistance is provided in the following areas:
- Applicable keywords, based on language syntax.
- Blocks of code that go together, such as BEGIN END;.
- Constants that you have defined, identifiers, labels, functions, and procedures
that can be used, where the routines can be in any projects, even if these
are not referenced by the current project.
- Database schema and table names after the database correlation name, as
well as table column names in INSERT, UPDATE, DELETE, and SELECT statements,
and, in most cases, the WHERE clauses of those statements.
- Elements of message field reference: runtime domain (parser) names, format
of type expression, namespace identifiers, namespace-qualified element and
attribute names, and format of index expression.
- Content in the Properties folder under the output message root.
- For the DECLARE NAMESPACE statement, target namespaces of message sets
and schema names.
Content assistance works only if the ESQL can be parsed correctly.
Errors such as END missing after BEGIN, and other unterminated block statements,
cause parser failures and no content assistance is provided. Try content assistance
in other areas around the statement where it does not work to narrow down
the point of error. Alternatively, save the ESQL file; saving the file causes
validation and all syntax errors are written to the Tasks view. Refer to the
errors reported to understand and correct the ESQL syntax. If you use content
assistance to generate most statements (such as block statements), these are
correctly entered and there is less opportunity for error.
- When you have finished working with this module, you can close
the ESQL file. Save the file before you close it to retain all your changes
and validate your ESQL.
If you prefer, you can open the ESQL file
directly and create the module within that file using the editor. To do this:
- Switch to the Broker Application Development perspective.
- Select the ESQL file in which you want to create the module. Either double-click
to open this file in the editor view, or right-click and click Open.
- In the editor view, position your cursor on a new line and use content
assistance to select the appropriate module skeleton for this type of node,
for example CREATE COMPUTE MODULE END MODULE;. You can type
this in yourself if you prefer, but you must ensure that what you type is
consistent with the required skeleton, shown above. Use content assistance
to give you additional help by inserting only valid ESQL, and by inserting
matching end statements (for example, END MODULE;) where
these are required.
- Complete the coding of the module as appropriate.
Whichever method you use to open the ESQL file, be aware that the
editor provides functions to help you to code ESQL. This section refers to
content assistance but there are further functions available in the editor.
For information about these functions, see ESQL editor.