Creating an ESQL file

When you include a node in your message flow that requires ESQL code to customize its function, you must code the ESQL statements that provide the customization in an ESQL module in an ESQL file. The Compute, Database, DatabaseInput, and Filter nodes use ESQL code. You can use the same ESQL file for more than one module.

Before you begin

For background information, see ESQL overview.

Optional: You can create an application, library, or integration project before you create an ESQL file. Alternatively, you can create the containing project when you create the ESQL file.

About this task

ESQL files are stored in a file system or in a shared repository. If you are using a file system, you can use the local file system or a shared drive. If you store files in a repository, you can use any of the available repositories that are supported by Eclipse, for example CVS.

To create an ESQL file, complete the following steps.

Procedure

  1. Click File > New > Message Flow ESQL File.

    You can also click New in the Application Development view, then click Message Flow ESQL File.

    The New Message Flow ESQL File wizard opens.
  2. Either select an existing application, library, or integration project in which to create the ESQL file, or click New to create a new container.

    When you store ESQL files in a shared library, you must place the ESQL files inside a schema that is not the default empty schema. For more information, see Shared libraries.

  3. Enter a name for the new ESQL file.
    If you enter a name that is already in use for an ESQL file in this project, the error message The resource <name>.esql already exists is shown and you must specify a different name.

    When creating ESQL files, the overall file path length must not exceed 256 characters, due to a Windows file system limitation. If you try to add a message flow to a BAR file with ESQL or mapping files with a path length that exceeds 256 characters, the compiled message flow is not generated and cannot be deployed. Therefore, make sure that the names of your ESQL files, mapping files, projects, and broker schema are as short as possible.

  4. To define the ESQL file in a specific broker schema, clear Use default broker schema and either select a broker schema from the drop-down list, or enter the name of the broker schema.
  5. Click Finish.

Results

The ESQL file opens in the editor, where you can edit the file, then save it. The ESQL file is shown in the Application Development view, beneath the ESQLs folder of the containing application, library, or integration project.

An ESQL file can also be created automatically for you. You can right-click a Compute, Database, DatabaseInput, or Filter node, then click Open ESQL. If the module identified by the appropriate property does not already exist in the broker schema, a module is created automatically. This module is created in the file <message_flow_name>.esql in the same broker schema in the same project as the <message_flow_name>.msgflow file. If that ESQL file does not already exist, that is also created for you.

The contents of a single ESQL file do not have any specific relationship with message flows and nodes. It is your decision which modules are created in which files (unless the specified module, identified by the appropriate property, is created by default in the file <message_flow_name>.esql as described above). Monitor the size and complexity of the ESQL in each file, and split the file if it becomes difficult to view or manage.

If you create reusable subroutines (at broker schema level) in an ESQL file, you might want to refer to these routines from ESQL modules in another project. To refer to the routines, specify that the project that runs the subroutines depends on the project in which the ESQL file containing them is defined. You can specify this behavior when you create the second project, or you can create a project reference; see Referencing resources in other libraries.