DECLARE statement

Use the DECLARE statement to define a variable, the data type of the variable and, optionally, its initial value.

You can define three types of variable with the DECLARE statement:

• External
• Normal
• Shared

The following types of broker node can access UDPs(USER DEFINED PROPERTY):

• Compute node
• Database node
• Filter node
• Nodes that are derived from these node types

Take care when specifying the data type of a UDP, because a CAST is used to change the value to the requested DataType.

Example 1:

DECLARE mycolor EXTERNAL CHARACTER 'blue'; 

Example 2:

DECLARE TODAYSCOLOR EXTERNAL CHARACTER;

SET COLOR = TODAYSCOLOR;

where TODAYSCOLOR is a user-defined property that has a TYPE of CHARACTER and a VALUE set by the Message Flow editor.

ESQL variables

ESQL variables can be described as external variables, normal variables, or shared variables; their use is defined in the DECLARE statement.

Types of variable

External

External variables (defined with the EXTERNAL keyword) are also known as user-defined properties (see User-defined properties in ESQL). They exist for the entire lifetime of a message flow and are visible to all messages that pass through the flow. You can define external variables only at the module and schema level. You can modify their initial values (optionally set by the DECLARE statement) by using the Message Flow editor, or at deployment time, by using the Broker Archive editor. You can query and set the values of user-defined properties at run time by using the Configuration Manager Proxy (CMP). For more information, see Setting user-defined properties at run time in a CMP application.

Normal

Normal variables have a lifetime of just one message passing through a node. They are visible to that message only. To define a normal variable, omit both the EXTERNAL and SHARED keywords.

Shared

Shared variables (defined with the SHARED keyword) can be used to implement an in-memory cache in the message flow; see Optimizing message flow response times. Shared variables have a long lifetime and are visible to multiple messages that pass through the flow; see Long-lived variables. They exist for the lifetime of the execution group process, the lifetime of the flow or node, or the lifetime of the node's SQL that declares the variable (whichever is the shortest). They are initialized when the first message passes through the flow or node after each broker startup.

See also the ATOMIC option of the BEGIN ... END statement. The BEGIN ATOMIC construct is useful when a number of changes have to be made to a shared variable and when it is important to prevent other instances seeing the intermediate states of the data.

ESQL statements and what they do

ESQL statements

You can use ESQL statements to manipulate message trees, update databases, or interact with nodes.

The following table summarizes the ESQL statements and what they do.

Statement type	Description
Basic statements:
BEGIN ... END statement	Gives the statements defined within the BEGIN and END keywords the status of a single statement.
CALL statement	Invokes a user-written routine that has been defined using a CREATE FUNCTION or CREATE PROCEDURE statement.
CASE statement	Uses rules defined in WHEN clauses to select a block of statements to execute.
CREATE FUNCTION statement	Like CREATE PROCEDURE, CREATE FUNCTION defines a user-written routine. (The few differences between CREATE FUNCTION and CREATE ROUTINE are described in the reference material.)
CREATE MODULE statement	Creates a module (a named container associated with a node).
CREATE PROCEDURE statement	Like CREATE FUNCTION, CREATE PROCEDURE defines a user-written routine. (The few differences between CREATE FUNCTION and CREATE ROUTINE are described in the reference material.)
DECLARE statement	Declares one or more variables that can be used to store temporary values.
IF statement	Processes a set of statements based on the result of evaluating condition expressions.
ITERATE statement	Abandons processing the current iteration of the containing WHILE, REPEAT, LOOP, or BEGIN statement, and might start the next iteration.
LEAVE statement	Abandons processing the current iteration of the containing WHILE, REPEAT, LOOP or BEGIN statement, and stops looping.
LOOP statement	Processes a sequence of statements repeatedly and unconditionally.
REPEAT statement	Processes a sequence of statements, then evaluates a condition expression. If the expression evaluates to TRUE, executes the statements again.
RETURN statement	Stops processing the current function or procedure and passes control back to the caller.
SET statement	Evaluates a source expression, and assigns the result to the target entity.
THROW statement	Generates a user exception.
WHILE statement	Evaluates a condition expression, and if it is TRUE executes a sequence of statements.
Message tree manipulation statements:
ATTACH statement	Attaches a portion of a message tree into a new position in the message hierarchy.
CREATE statement	Creates a new message field.
DELETE statement	Detaches and destroys a portion of a message tree, allowing its memory to be reused.
DETACH statement	Detaches a portion of a message tree without deleting it.
FOR statement	Iterates through a list (for example, a message array).
MOVE statement	Changes the field pointed to by a target reference variable.
Database update statements:
DELETE FROM statement	Deletes rows from a table in an external database based on a search condition.
INSERT statement	Adds a new row to an external database.
PASSTHRU statement	Takes a character value and passes it as an SQL statement to an external database.
UPDATE statement	Updates the values of specified rows and columns in a table in an external database.
Node interaction statements:
PROPAGATE statement	Propagates a message to the downstream nodes within the message flow.
Other statements:
BROKER SCHEMA statement	This statement is optional and is used in an ESQL file to explicitly identify the schema that contains the file.
DECLARE HANDLER statement	Declares an error handler.
EVAL statement	Takes a character value, interprets it as an SQL statement, and executes it.
LOG statement	Writes a record to the event or user trace log.
RESIGNAL statement	Re-throws the current exception (if any). This is used by an error handler, when it cannot handle an exception, to give an error handler in higher scope the opportunity of handling the exception.