Invoking stored procedures

To invoke a procedure that is stored in a database, use the ESQL CALL statement. The stored procedure must be defined by a CREATE PROCEDURE statement that has a Language clause of DATABASE and an EXTERNAL NAME clause that identifies the name of the procedure in the database and optionally, the database schema to which it belongs.

About this task

When you invoke a stored procedure with the CALL statement, the integration node checks that the ESQL definition and the database definition match:

The external name of the procedure must match a procedure in the database.
The number of parameters must be the same.
The type of each parameter must be the same.
The direction of each parameter (IN, OUT, INOUT) must be the same.

The following restrictions apply to the use of stored procedures:

Overloaded procedures are not supported. (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 integration node detects that a procedure is overloaded, it raises an exception.
In an Oracle stored procedure declaration, you are not permitted to constrain CHAR and VARCHAR2 parameters with a length, and NUMBER parameters with a precision or scale, or both. Use %TYPE when you declare CHAR, VARCHAR, and NUMBER parameters to provide constraints on a formal parameter.
SQL Server and Sybase consider OUTPUT parameters from stored procedures to be INPUT/OUTPUT parameters. If you declare them as OUT or OUTPUT parameters in your ESQL, a type mismatch error occurs at run time. To avoid the mismatch, declare SQL Server and Sybase OUTPUT parameters as INOUT in your ESQL.
Avoid changing the type or number of parameters of a procedure without also changing the name. If changes of this nature cannot be avoided, any integration servers with procedures with changed parameters called by the ESQL must be restarted.
Avoid the use of commit or rollback calls from within a database-stored procedure or any subsequent database-stored procedures that it might call. When you use commit or rollback calls within a database stored procedure, they cannot be tracked by IBM® App Connect Enterprise and happen outside of the unit of work. The use of these calls can lead to database errors when the flow completes its work, as IBM App Connect Enterprise tries to commit or rollback any database work already done.

Creating a stored procedure in ESQL

About this task

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:

Procedure

Code a statement similar to this example to create an unqualified procedure:
```
CREATE PROCEDURE myProc1(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL NAME "myProc";
```
The EXTERNAL NAME that you specify must match the definition that you have created in the database, but you can specify any name you choose for the corresponding ESQL procedure.

Code a statement similar to this example to create a qualified procedure:

CREATE PROCEDURE myProc2(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL NAME "Schema1.myProc";

Code a statement similar to this example to create a qualified procedure in an Oracle package:

CREATE PROCEDURE myProc3(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL 
                 NAME "mySchema.myPackage.myProc";

Results

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

Calling a stored procedure

Procedure

Code a statement similar to this example to invoke an unqualified procedure:
```
CALL myProc1('HelloWorld');
```
Because it is not defined explicitly as belonging to any schema, the myProc1 procedure must exist in the default schema (the name of which is the user name that is used to connect to the data source) or the command fails.
The following example calls the myProc procedure in schema Schema1.
```
CALL myProc2('HelloWorld');
```
Code a statement similar to this example to invoke an unqualified procedure with a dynamic schema:
```
DECLARE Schema2 char 'mySchema2';
CALL myProc1('HelloWorld') IN Database.{'Schema2'};
```
This statement calls the myProc1 procedure in database Schema2, overriding the default username schema.

Calling a stored procedure that returns two result sets

About this task

To call a stored procedure that takes one input parameter and returns one output parameter and two result sets:

Procedure

Define the procedure with a CREATE PROCEDURE statement that specifies one input parameter, one output parameter, and two result sets:
```
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
  LANGUAGE DATABASE
  DYNAMIC RESULT SETS 2
  EXTERNAL NAME "myschema.myproc1";
```

To invoke the myProc1 procedure by using a field reference, code:

/* using a field reference */
CALL myProc1(InVar1, OutVar2, Environment.ResultSet1[], 
            OutputRoot.XMLNS.Test.ResultSet2[]);

To invoke the myProc1 procedure by using a reference variable, code:

/* using a reference variable*/
DECLARE cursor REFERENCE TO OutputRoot.XMLNS.Test;

CALL myProc1(InVar1, cursor.OutVar2, cursor.ResultSet1[], 
                     cursor.ResultSet2[]);