CALL statement

Syntax

>>-CALL--+---------------------+--RoutineName --(--ParameterList--)-->
         '-BrokerSchemaName--.-'                                      

>--+------------+--+--------------+----------------------------><
   '-Qualifiers-'  '-INTO--target-'   

BrokerSchemaName

   .-.--------------.   
   V                |   
|------Identifier---+-------------------------------------------|

ParameterList

   .-,--------------.   
   V                |   
|------Expression---+-------------------------------------------|

Qualifiers

|--+-IN--DatabaseSchemaReference----------+---------------------|
   '-EXTERNAL--SCHEMA--DatabaseSchemaName-'   

DatabaseSchemaReference

|--Database--+-------------------------+--.--SchemaClause-------|
             '-.--DatabaseSourceClause-'                    

DatabaseSourceClause

|--+-DatabaseSourceName-------+---------------------------------|
   '-{--DatabaseSourceExpr--}-'   

SchemaClause

|--+-SchemaName-------+-----------------------------------------|
   '-{--SchemaExpr--}-'   

Using the CALL statement

The called routine must be invoked in a way that matches its definition. For example, if you have defined a routine with three parameters, the first two of type integer and the third of type character, the CALL statement must pass three variables to the routine, each of a data type that matches the definition. This technique is called exact signature matching, which means that the signature provided by the CALL statement must match the signature provided by the definition of the routine.

Exact signature matching also applies to the value returned to a routine. If the RETURNS clause is specified on the CREATE FUNCTION statement, or the routine is a built-in function, the INTO clause must be specified on the CALL statement. A return value from a routine cannot be ignored. Conversely, if the RETURNS clause is not specified on the CREATE FUNCTION statement, the INTO clause must not be specified, because there is no return value from the routine.

This variety of implementation means that some of the clauses in the CALL syntax diagram are not applicable (or allowed) for all types of routine. It also allows the CALL statement to invoke any type of routine, irrespective of how the routine has been defined.

When the optional BrokerSchemaName parameter is not specified, the broker SQL parser searches for the named procedure using the algorithm described in the PATH statement (see the PATH clause of the BROKER SCHEMA statement).

When the BrokerSchemaName parameter is specified, the broker SQL parser invokes the named procedure in the specified schema without first searching the path. However, if a procedure reference is ambiguous (that is, there are two procedures with the same name in different broker schemas) and the reference is not qualified by the optional BrokerSchemaName, the Eclipse toolset generates an error in the Problems view that you must correct to deploy the ambiguous code.

The broker-provided built-in functions are automatically placed in a predefined broker schema called SQL. The SQL schema is always searched last for a routine that has not been matched to a user-defined routine. Therefore, a user-defined module takes precedence over a built-in routine of the same name.

Each broker schema provides a unique symbol or namespace for a routine, so a routine name is unique when it is qualified by the name of the schema to which it belongs.

        CALL myProc1() INTO cursor;
        CALL myProc1() INTO OutputRoot.XMLNS.TestValue1; 

The CALL statement passes the parameters into the procedure in the order given to it. Parameters that have been defined as IN or INOUT on the routine's definition are evaluated before the CALL is made, but parameters defined as OUT are always passed in as NULL parameters of the correct type. When the procedure has completed, any parameters declared as OUT or INOUT are updated to reflect any changes made to them during the procedure's execution. Parameters defined as IN are never changed during the cause of a procedure's execution.

Routine overloading is not supported, which means that you cannot create two routines of the same name in the same broker schema. If the broker detects that a routine has been overloaded, it raises an exception. Similarly, you cannot invoke a database stored procedure that has been overloaded. A database stored procedure is overloaded if another procedure of the same name exists in the same database schema. However, you can invoke an overloaded Java method, provided that you create a separate ESQL definition for each overloaded method you want to call, and give each ESQL definition a unique routine name.