Calling procedures from applications or external routines

Invoking a procedure (also called a stored procedure) that encapsulates logic from a client application or from an application associated with an external routine is easily done with some simple setup work in the application and by using the CALL statement.

Before you begin

The procedure must have been created in the database by executing the CREATE PROCEDURE statement.

For external procedures, the library or class file must exist in the location specified by the EXTERNAL clause in the CREATE PROCEDURE statement.

The procedure invoker must have the privileges required to execute the CALL statement. The procedure invoker in this case is the user ID executing the application, however special rules apply if the DYNAMICRULES bind option is used for the application.

Procedure

Certain elements must be included in your application if you want that application to invoke a procedure. In writing your application you must do the following:

  1. Declare, allocate, and initialize storage for the optional data structures and host variables or parameter markers required for the CALL statement.
    To do this:
    • Assign a host variable or parameter marker to be used for each parameter of the procedure.
    • Initialize the host variables or parameter markers that correspond to IN or INOUT parameters.
  2. Establish a database connection. Do this by executing an embedded SQL language CONNECT TO statement, or by coding an implicit database connection.
  3. Code the procedure invocation. After the database connection code, you can code the procedure invocation. Do this by executing the SQL language CALL statement. Be sure to specify a host variable, constant, or parameter marker for each IN, INOUT, OUT parameter that the procedure expects.
  4. Add code to process the OUT and INOUT parameters, and result sets. This code must come after the CALL statement execution.
  5. Code a database COMMIT or ROLLBACK. Subsequent to the CALL statement and evaluation of output parameter values or data returned by the procedure, you might want your application to commit or roll back the transaction. This can be done by including a COMMIT or ROLLBACK statement. A procedure can include a COMMIT or ROLLBACK statement, however it is recommended practice that transaction management be done within the client application.
    Note: Procedures invoked from an application that established a type 2 connection to the database, cannot issue COMMIT or ROLLBACK statements.
  6. Disconnect from the database.
  7. Prepare, compile, link, and bind your application. If the application is for an external routine, issue the CREATE statement to create the routine and locate your external code library in the appropriate function path for your operating system so that the database manager can find it.
  8. Run your application or invoke your external routine. The CALL statement that you embedded in your application will be invoked.

Results

Note: You can code SQL statements and routine logic at any point between steps 2 and 5.