Custom ESQL

You use the Custom ESQL transform to call your own ESQL code from a graphical data map.

In the Graphical Data Mapping editor, select Custom ESQL from the Custom Transforms list. You can then use the transform properties to select ESQL code that is stored in your workspace. When you select the ESQL route, the Parameters table Name and Type columns are populated. You must then select an input element or XPath expression in the Value column for each parameter. You can use the content assist in the Value column to help you to assign the required element, literal, or XPath expression.

The following topics contain further information about ESQL types and functions:

The ESQL file that contains the referenced ESQL module must be visible for mapping to be selectable. Ensure that the application, library, or project references are set to make the ESQL file accessible to the map. When you deploy the map, ensure that the ESQL file is also deployed, and that Compile and in-line resources is not selected.

Requirements for ESQL modules that are called from a graphical data map

The following requirements apply to ESQL modules that are called from a graphical data map:

  • The syntax of an ESQL procedure is shown in CREATE PROCEDURE statement. The procedures that can be called from a Custom ESQL transform in a graphical data map must conform to the following requirements:
    • Only IN parameters are allowed
    • A RETURN is required
    • The RoutineType can be either PROCEDURE or FUNCTION.
    • The procedure must be defined in the global scope in the ESQL file, not in a CREATE MODULE statement. For example:
      
      CREATE FUNCTION stringToBlob (IN field character) RETURNS BLOB
      BEGIN
         RETURN CAST(field AS BLOB CCSID 1208);
      END;
  • A return data type must be a simple scalar.
  • An input parameter data type must be a simple scalar, or an ESQL REFERENCE where the reference variable meets the following criteria:
    • The reference variable is used to access only the input element or descendants of the input element.
    • The input element and any descendants that are accessed by using the reference variable are defined in the input message model.
    • The input element must exist, unless certain conditions are met; see note below. If the input element does not exist, the map fails and reports that the ESQL procedure input is the wrong data type.

      If there is a chance that the input element might not exist, you can prevent the ESQL procedure from being called by adding a condition that uses fn:exists(inputElementVariable). If some optional input elements do not exist and the total number of input elements is 15 or less, the Mapping node inserts null instead of missing elements. If the total number of input elements is greater than 15 and some input elements are missing, the ESQL procedure fails.

  • An ESQL module with no inputs can be used to assign to an output element.
  • Each input parameter to the ESQL module can be taken from an input element that is wired into the custom ESQL transform or specified as a constant.
  • The ESQL must not include SQL calls to a data source. The Graphical Data Mapping editor provides facilities to include database operations in the map. For more information, see Mapping database content.
Note: The target element of a Custom ESQL transform is always created and given the value that is returned by the associated ESQL procedure. If the ESQL procedure returns ESQL NULL, the transform creates an element with an empty value. If you do not want an element to be created, you can add a conditional expression.