Creating ESQL for a node

Create ESQL code to customize the behavior of a Compute, Database, DatabaseInput, or Filter node in an ESQL file.

Before you begin

This topic assumes that you have created an ESQL file. For more information, see Creating an ESQL file.

About this task

In 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, DatabaseInput, or Filter). Within the module, you can create and use functions and procedures in addition to 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 in 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, complete the following steps.

Procedure

  1. Open the message flow that includes the node for which you want to create ESQL.
    In the Application Development view, expand the appropriate application, library, or integration project, expand the Flows folder, then double-click the message flow.

    The message flow opens in the Message Flow editor.

  2. Right-click a Compute, Database, DatabaseInput, or Filter node, then click Open ESQL.
    The default ESQL file for this message flow, message_flow_name.esql, opens in the editor view. If the file does not already exist, it is created, containing a skeleton module for this node at the end. The exact content depends on the type of node.

    If you have already created the file, it is opened in the editor view and a new module is created and highlighted.

    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 name is message_flow_name_node_type. The main function contains calls to two procedures (described in the following list) that are declared in the Compute node module following the function Main. These calls are commented out. To include the function that they provide, uncomment the 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 DatabaseInput node, the module that is created contains three procedures, ReadEvents, BuildMessage, and EndEvent. Each of these procedures contains boilerplate text, which describes how the procedure works. For more information about configuring DatabaseInput nodes, see Configuring a DatabaseInput node. For a DatabaseInput node, the following first line of the module is created:

    CREATE DATABASEEVENT MODULE module_name

    For a Filter node, the module is identical to the module created for the Database node, except for the first line, which reads:

    CREATE FILTER MODULE module_name
  3. Add ESQL to this file to customize the behavior of the node.

    For Compute, Database, or Filter nodes, start by adding ESQL statements in the Main function, (after the BEGIN statement, and before RETURN TRUE). For DatabaseInput nodes, add ESQL statements in the ReadEvents, BuildMessage, and EndEvent procedures. You can add DECLARE statements in 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 shows a list of valid statements and functions at the point of the cursor. To start this assistance, click Edit > Content Assist. On some systems, you can use the key combination Ctrl+Space. Scroll through the list to find and highlight the statement or function that you want, and press Enter. The appropriate code is inserted into your module.

    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 the current project does not reference them.
    • Database schema and table names after the database correlation name, 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.
    • 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 Problems 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 statements are correctly entered and there is less opportunity for error.

  4. When you have finished working with this module, save and close the ESQL file.

Results

You can also open the ESQL file directly and create the module in that file by using the editor:

  1. Open the ESQL file in which you want to create the module.
  2. 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 also type in this text, but you must ensure that what you type is consistent with the required skeleton, shown earlier. Use content assistance to give you additional help by inserting only valid ESQL, and by inserting matching end statements (for example, END MODULE;) where they are required.
  3. 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; other functions are available. For information about these functions, see ESQL editor.