Calling stored procedures

Business objects are capable of executing stored procedures in response to commands issued by the components of your application. Often, no other business object functionality is required—for example, the procedure might typically move an order from "current" to "submitted", recalculate order-form price totals, and others. However, you might also decide to execute a stored procedure as part of a pipeline execution that also submits or returns field values that will be managed by the business object. In this case, you would include the procedure-call pipeline stage as part of a longer pipeline.

To enable a stored procedure call, you must establish stored procedure mapping and create a method pipeline with at least one stage than uses the mapping. Often, the method will also input parameters to the stored procedure and/or receive output parameter values and/or result tables.

To create a stored procedure mapping:

Before you can create a stored procedure mapping, you must first create the stored procedure in the database and update your local data models. This will ensure that you can see the procedure you need in the Infor e-Commerce Development Studio, together with its required input/output parameters and result tables.

Stored procedure mappings are a type of primary mapping. They are created in the same way as table mappings: right-click anywhere in the Business Object Mappings window for the appropriate data source and select New from the contextual menu; then use the settings provided to specify the business object, stored procedure, and mapping variant name.

To assign procedure input and output parameters

  1. Go to Data Sources.
  2. Right-click on the data source and select Mappings.
  3. Locate the stored-procedure mapping for which you want to set input and/or output parameters. Double-click on this mapping, or right-click and select Procedure Parameters from the context menu.

    The Mapping Procedure Parameters window opens. It shows all of the parameters that exist for the selected stored procedure in your local database model.

  4. Assign the Source/Target and Key settings to establish where the parameter values will be taken from/sent to. See the table below. Remember to click Update to apply each new setting.

    There are three types of parameters; in, out and result. They are indicated by the following icons:

    input parameter Input parameter: This is a parameter that is requested by the stored procedure. When calling the procedure, a value must be submitted for each input parameter.
    output parameter Output parameter: This is a parameter that is returned by the stored procedure. These can be used, for example, to set attribute values on the business object.
    result value Result value: Procedure can either one or zero result values. Typically, these indicate the execution status of the procedure. Result values show no name in the Parameter column.

    For each listed parameter, the Parameter and Type values are established from your local database model.

  5. Continue working until you have set Source/Target and Key settings for each listed parameter.
Source/Target type Description
const Sets a constant value for an input parameter. Output and result parameters do not use this type. Use the Key field to set the value.
resultset-parameter Sets or takes a value to/from a business object parameter. Enter the name of the appropriate business object parameter in the Key field.
request-parameter Takes or sets a value from/to a business object parameter. Enter the name of the appropriate business object parameter in the Key field
result Takes or sets a value from/to the result set being built by the business object. Enter the name of the appropriate business object attribute in the Key field.
pipeline Takes or sets a value from/to the pipeline context. Enter the name of the pipeline context key in the Key field.This is used primarily to transmit values between stages of a single method. It is possible to create new context keys with this type, but care must be taken not to use one of the reserved pipeline keys (such as the one for identifying the result). If you create new keys, try to pick an unlikely value.
stage Takes or sets a value from/to the stage context, which exists only as long as the stage itself executes (it will not be available to other stages). Enter the name of the stage context key in the Key field.