IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Configure the module for deployment > Configure the module for outbound processing > Selecting and configuring business objects

Selecting and configuring stored procedures and stored functions

Select and configure business objects corresponding to stored procedures and stored functions in the database.

To select and configure business objects for stored procedures or stored functions, you need to understand the structure of the data in the database and know what objects the module needs to access. In particular, you need to know the parameters passed to the stored procedures or stored functions that your module needs to access.

This task is performed through the external service wizard.

You start in the Find Objects in the Enterprise System window and then work in a Specify the Configuration Properties for 'object' window that is specific to the business object you are configuring.


Procedure

  1. In the Discovered objects list of the Find Objects in the Enterprise System window, expand the node for the schema that contains the wanted stored procedure or stored function, and then expand the Stored Procedures node.
  2. Filter the stored procedures by specifying a valid name or pattern for at least one of the filter fields in the Filter Properties window.

    1. Click Stored Procedures and then click the

      (Create or edit filter.) button, located at the top of the Discovered objects pane.

    2. In the Filter Properties window, type a name or pattern in the Object name or pattern field.
      Use the question mark or underscore (? or _ ) to match a single character and the asterisk or percentage (* or %) to match multiple characters. The name is case-sensitive.

    3. In the Catalog name or pattern field, type the name or a pattern. Use the question mark or underscore (? or _ ) to match a single character and the asterisk or percentage (* or %) to match multiple characters.

    4. Click OK.

  3. Select one or more objects from the Stored Procedures list, and click the > (Add) button to add the object to the Selected objects list.

    Stored procedures that are defined in PL/SQL packages are displayed in the format SPName( PackageName).

    For example, if the EMP_MGMT package contains the CREATE_DEPT stored procedure, the stored procedure is displayed in the list as CREATE_DEPT(EMP_MGMT).

    If the database schema has multiple stored procedures with the same name, and the stored procedures have different parameters, the external service wizard cannot identify which stored procedure is selected. Hence, the stored procedures must have unique names.

    The Specify the Configuration Properties for 'object' window lists the attributes of the stored procedure business object, which include the names and data types of the parameters of the stored procedure, and information about the result sets that are returned.

    For DB2 database, if the table contains a structured data type, the adapter displays only the column name of the structured type. The adapter cannot obtain the type detail.

  4. Select the Use ResultSet business object Mode check box to handle the result set returned by the stored procedure in the business object mode. With this mode, the adapter need not know the number or order of the result sets returned by the external service wizard.

    If you the select the Use ResultSet business object Mode check box, the The maximum number of ResultSets returned from the stored procedure field is not available.

  5. If the stored procedure returns any result set, make sure that the value for the The maximum number of ResultSets returned from the stored procedure field reflects the maximum number expected. The wizard creates the required number of result set business objects to hold the results.

    For Oracle databases, make sure that the number of result sets is correct after you validate the syntax of the stored procedure. The Oracle driver does not always return the information.

    If the number of returned result sets is not correct, set it after validating and before clicking OK to exit the window. After you exit the wizard, you can optionally verify the setting of the MaxNumOfRetRS application-specific information for the stored procedure business object.

    1. For Oracle database, WebSphere Adapter for JDBC supports stored procedures with OUT parameter of Cursor type only. It does not support stored procedures with IN or INOUT parameter of Cursor type.

    2. For DB2 and MSSQLServer databases, WebSphere Adapter for JDBC does not support stored procedures with IN, OUT, and INOUT parameters of Cursor type.

  6. Configure each parameter:

    1. Verify that the Data type field displays the correct data type. The wizard automatically discovers the data type for a parameter that has standard JDBC data type. For other specific types, you must select the data type manually.

    2. In the Sample Value field, type a valid value.

      For Oracle database, if the stored procedure or stored function contains either a Varray or Object attribute with a Date data type and the data type mapping is date to string, then you must specify the sample value in the yyyy-mm-dd hh:mm:ss format instead of the yyyy-mm-dd format for the stored procedure or stored function validation to be successful. The following table provides the format to be followed for each Date data type mapping.

      Date data type formats
      Data type mapping SP/SF with Date data type VArray/Object in SP/SF with Date data type
      Date to String Format: Day-Month-Year (Example: '01-JAN-0001') Format: Year-Month-Day Hour:Minute:Second.[Millisecond] (Example: '0001-01-01 01:00:00.000000000').
      Date to Date Format：Year-Month-Day (Example: '0001-01-01') Format：Year-Month-Day (Example '0001-01-01')

      • The Year parameter can be specified from a minimum of one digit to a maximum of four digits in all the formats.

      • The Month parameter can be specified from a minimum of one digit to a maximum of two digits in all the formats, except in Date to String mapping of the SP/SF with the Date data type where the Month parameter is specified in three alphabetic characters as specified in the example in the previous table.

      • The Day, Hour, Minute, and Second parameters can be specified from a minimum of one digit to a maximum of two digits in all the formats.

      • The Milliseconds, which is an optional parameter, can be specified from a minimum of one digit to a maximum of nine digits in all the formats.

  7. To validate the syntax of the stored procedure using the sample values, click Validate. The result of the validation is displayed in the Result area.

    If the Result area displays the Validation failed message, there is a problem in the information you provided. Use the error message from the database server, which follows Validation failed message, to correct the definition. Verify that the data type of the parameters and the sample data are correct.

    The .log file in the .metadata folder of your workspace contains additional information about the problem.

    The following figure shows the window after a stored procedure has been validated.

    When you see the message Validation was successful, click OK to save the definition of the stored procedure business object.

    If the stored procedure or stored function returns a result set, do not click OK until the validation succeeds. The wizard uses the results returned during validation to create business objects to hold the result. If the stored procedure validation is not successful, the adapter does not return the result set at run time.

  8. To change the configuration of an object from the Selected objects list, select the object name and then click the

    (Edit) icon.


Results

The business objects you configured for stored procedures and stored functions are listed in the Find Objects in the Enterprise System window.


What to do next

In the Find Objects in the Enterprise System window, continue to select and configure other types of business objects.

When you are finished, click Next to set global properties and configure wrapper business objects.

Selecting and configuring business objects


Related concepts:

Business objects

Database tables with multiple parent tables

Stored procedure business object overview


Related tasks:

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Selecting and configuring batch SQL business objects

Selecting and configuring query business objects


Related reference:

Business object information