Invoking stored procedures

You can invoke a procedure that is stored in a database using the ESQL CALL statement with an EXTERNAL clause.

When you invoke a stored procedure with the CALL statement, the broker ensures that the ESQL definition and the database definition match:

If you specified a single percent character for the schema name when you created the procedure, this character indicates that the schema name is dynamic and is resolved on the CALL statement (see below).

The following restrictions apply to the use of stored procedures:

Creating a stored procedure in ESQL

When you define an ESQL procedure that corresponds to a database stored procedure, you can specify either a qualified name (where the qualifier is a database schema) or an unqualified name.

To create a stored procedure:

  1. Code a statement similar to this example to create an unqualified procedure:
    CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "myProc";

    The ESQL definition in this example has the same name as the database procedure name. The two names do not have to be the same; the database procedure name that you specify here must match the definition you have created in the database, but you can specify any name you choose for the corresponding ESQL procedure.

  2. Code a statement similar to this example to create a qualified procedure:
    CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "Schema1.Proc1";

    In this example, the ESQL procedure name does not match the database procedure name.

  3. Code a statement similar to this example to create a qualified procedure in an Oracle package:
    CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "mySchema.myPackage.myProc";
  4. Code a statement similar to this example to create a qualified procedure for which the schema name is determined at runtime:
    CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "%.myProc";

    If you create a procedure with this specification, and use the corresponding call statement (shown below), you can decide exactly which stored procedure is called at the point that the call is made.

For examples of stored procedure definitions in the database, see the CREATE PROCEDURE statement.

Calling a stored procedure

  1. Code a statement similar to this example to invoke an unqualified or a fixed qualified procedure:
    CALL myProc('HelloWorld');
  2. Code a statement similar to this example to invoke a qualified procedure with a dynamic schema:
    CALL myProc('HelloWorld') EXTERNAL SCHEMA 'Schema2';
  3. Code a statement similar to this example to invoke a qualified procedure with a dynamic schema that is derived from the input message content:
    CALL myProc('HelloWorld') EXTERNAL SCHEMA InputRoot.XML.Test.SchemaName;
If you have not specified the schema name as a single percent character in the EXTERNAL NAME clause of the CREATE PROCEDURE statement, the EXTERNAL SCHEMA name clause of the CALL statement is ignored if specified.