Invoking stored procedures

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

Stored procedures in SQLServer are not supported as well as overloaded procedures to any database. (An overloaded procedure is one that has the same name as another procedure in the same database schema with a different number of parameters, or parameters with different types.) If the broker detects that a procedure has been overloaded, it raises an exception.

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 indicates that the schema name is dynamic and is resolved on the CALL statement (see below).

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. This is not required; 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.

Related concepts
Message flows
ESQL procedures

Related tasks
Designing a message flow
Defining message flow content
Handling errors in message flows

Related reference
Compute node
Database node
Filter node
ESQL
CALL statement
CREATE PROCEDURE statement