WebSphere Message Brokers
File: ac06009_
Writer: John Prowse

Task topic

This build: July 31, 2007 21:17:49

Using the CALL statement to invoke a user-written routine

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

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 invoke a routine that has been implemented in any of the following ways:
  • ESQL.
  • Java.
  • As a stored procedure in a database.
  • As a built-in (broker-provided) function.

    (Although you can use CALL to invoke built-in (broker-provided) functions and user-defined SQL functions, typically you would use their names in expressions directly.)

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

A routine is invoked 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

A routine is invoked 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 there is no value to return.

A Java routine can return any data type in the ESQL-to-Java data-type mapping table. Note that this excludes List and Row.

Calling a database stored procedure

A routine is invoked 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 broker searches for a definition (created by a CREATE PROCEDURE statement) that matches the procedure's local name. The broker 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 invoked.

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 actual 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.

Related concepts
ESQL overview
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:17:49

ac06009_ This topic's URL is: