Building Methods with Stored Procedures or Function Calls

A method can execute a function call or stored procedure that has already been defined in the database. Stored procedure and function call methods must have a value of P or PR specified for the handlerType attribute of the method element.

•

The value P is for procedures or functions that return data through output parameters.

•

The value PR is for procedures that return data through SELECT statements.

For these two handlerType values, the SQL attribute must include the call keyword in the syntax for executing the function or procedure. Either method type can include input parameters. Input parameters defined in the adapter configuration file can be used to pass parameters to the stored procedure or function.

For PR methods, the SQL statement must be in the following form:

call <procedure_name> (${<input_param_1>}, ...,${<input_param_n>})

where input_param_1 to input_param_n are names of input parameters defined in the adapter configuration file. Parameter names in the configuration file do not need to match database function or procedure parameter names. However, for handlerType=PR, the order input parameters defined in the file must match the order they are referenced in the SQL statement. For example, a method for calling the procedure Proc1 has handlerType = PR and the following input parameters:

<inputParameter
   name="P1"
   type="VARCHAR">
</inputParameter>

<inputParameter
   name="P2"
   type="INTEGER">
</inputParameter>

The following syntax specified in the SQL attribute for this method executes the stored procedure:

call Proc1 (${P1}, ${P2})

For P methods, the SQL statement must be in one of the following forms:

call <procedure_name> (${<input/output_param_1>}, ...,${<input/output_param_n>})

or

${<output_param_x>}=call <procedure_name> (${<input/output_param_1>}, ...,${<input/output_param_n>})

where <input/output_param_1> and <input/output_param_n> are the names of input or output parameters defined in the adapter configuration file. For handlerType=P methods, parameter names in the configuration file do not need to match names defined in function or stored procedure syntax. For example, the procedure PINOUT with the following input and output parameters defined:

CREATE PROCEDURE PINOUT (x1 in VARCHAR, x2 in out SMALLINT, x3 out INT)

has the following input and output parameters defined in the adapter configuration file:

<inputParameter
   name="P1"
   type="VARCHAR">
</inputParameter>

<inputParameter
   name="P2"
   type="INTEGER">
</inputParameter>

<outputParameter
   name="P2"
   type="SMALLINT">
</outputParameter>

<outputParameter
   name="P3"
   type="INTEGER">
</outputParameter>

<outputParameter
   name="Return Value"
   type="INTEGER">
</outputParameter>

For this method, the syntax specified for the SQL attribute might be as follows:

${Return Value} = call PINOUT (${P1}, ${P2}, ${P3})

In this example, the order that parameters are defined in the adapter configuration file is not required to match the order of parameters in the SQL attribute.

For input parameters, the corresponding stored procedure or function parameter must be type in or type in out. For output parameters, the corresponding parameters must be either type out or in out. To use in out parameters, define an input parameter and an output parameter with the same name in the configuration file.