Using the CALL statement to call a user-written routine

The ESQL CALL statement calls routines that have been created and implemented in different ways.

About this task

A routine is a user-defined function or procedure that has been defined by one of the following statements:
  • CREATE FUNCTION
  • CREATE PROCEDURE
You can use the CALL statement to call a routine that has been implemented in any of the following ways:
  • ESQL
  • Java™
  • As a stored procedure in a database
  • As a function that is provided by IBM® Integration Bus

    You can use CALL to call built-in functions and user-defined SQL functions, but typically you use their names directly in expressions .

For details of the syntax and parameters of the CALL statement, see CALL statement. For an example of the use of CALL, see the examples in CREATE PROCEDURE statement.

Calling an ESQL routine

About this task

A routine is called as an ESQL method if the routine's definition specifies a LANGUAGE clause of ESQL or if the routine is a built-in function. An exact one-to-one matching of the data types and directions of each parameter, between the definition and the CALL, is required. An ESQL routine is allowed to return any ESQL data type, excluding List and Row.

Calling a Java routine

About this task

A routine is called as a Java method if the routine's definition specifies a LANGUAGE clause of JAVA. An exact one-to-one matching of the data types and directions of each parameter, between the definition and the CALL, is required. If the Java method has a void return type, the INTO clause cannot be used because no value exists to return.

A Java routine can return any data type in the ESQL-to-Java data-type mapping table, excluding List and Row.

Calling a database stored procedure

About this task

A routine is called as a database stored procedure if the routine's definition has a LANGUAGE clause of DATABASE.

When a call is made to a database stored procedure, the integration node searches for a definition (created by a CREATE PROCEDURE statement) that matches the procedure's local name. The integration node then uses the following sequence to resolve the name by which the procedure is known in the database and the database schema to which it belongs:

  1. If the CALL statement specifies an IN clause, the name of the data source, the database schema, or both, is taken from the IN clause.
  2. If the name of the data source is not provided by an IN clause on the CALL statement, it is taken from the DATASOURCE attribute of the node.
  3. If the database schema is not provided by an IN clause on the CALL statement, but is specified on the EXTERNAL NAME clause of the CREATE PROCEDURE statement, it is taken from the EXTERNAL NAME clause.
  4. If no database schema is specified on the EXTERNAL NAME clause of the CREATE PROCEDURE statement, the database's user name is used as the schema name. If a matching procedure is found, the routine is called.

The chief use of the CALL statement's IN clause is that it allows the data source, the database schema, or both, to be chosen dynamically at run time. (The EXTERNAL SCHEMA clause also allows the database schema which contains the stored procedure to be chosen dynamically, but it is not as flexible as the IN clause and is retained only for compatibility with earlier versions. Its use in new applications is deprecated.)

If the called routine has any DYNAMIC RESULT SETS specified in its definition, the number of expressions in the CALL statement's ParameterList must match the number of parameters to the routine, plus the number of DYNAMIC RESULT SETS. For example, if the routine has three parameters and two DYNAMIC RESULT SETS, the CALL statement must pass five parameters to the called routine. The parameters passed for the two DYNAMIC RESULT SETS must be list parameters; that is, they must be field references qualified with array brackets [ ]; for example, Environment.ResultSet1[].

A database stored procedure is allowed to return any ESQL data type, excluding Interval, List, and Row.