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).
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:
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.
CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "Schema1.Proc1";
In this example, the ESQL procedure name does not match the database procedure name.
CREATE PROCEDURE myProc(IN p1 CHAR) EXTERNAL NAME "mySchema.myPackage.myProc";
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.
CALL myProc('HelloWorld');
CALL myProc('HelloWorld') EXTERNAL SCHEMA 'Schema2';
CALL myProc('HelloWorld') EXTERNAL SCHEMA InputRoot.XML.Test.SchemaName;
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
Notices |
Trademarks |
Downloads |
Library |
Support |
Feedback
![]() ![]() |
ac17040_ |