Understanding and Testing Stored Procedures

Topics:

A stored procedure is a subroutine that is referenced by a relational database. The iWayTechnology Adapter for RDBMS provides access to stored procedures and their metadata. In Oracle databases, stored procedures and functions are listed under the stored procedure hierarchy. This section provides an overview of stored procedures and also describes how to generate XML request and response schemas for stored procedures.

Stored Procedures With Constraints

How to:

Certain stored procedures contain constraints, meaning that if no value is supplied for an insert, a default value that meets the constraints is supplied. The adapter does not allow the creation of response schemas unless these constraints are met.

The adapter allows you to input default parameters that meet the criteria of the stored procedure in order to generate the response schema. The values which you input must adhere to the rules of the stored procedure. Once the proper values are entered, you can right-click the stored procedure name again and select the Test option from the context menu.

Procedure: How to View an Error Message and Set Parameter Values

The following image shows a stored procedure selected in the left pane and the Operations menu in the right pane.

To set the parameter values:

  1. Select the stored procedure name.
  2. In the right pane, move the pointer over Operations, and select View error message.

    When you click View error message, a constraint error or primary key violation error is generated. This means that you must first supply a value in order for a response schema to be returned.

    Note: If the parameters are not set, then you will not be able to generate a web service because the response schema is not available.

  3. Click Set Parameter Values.

    The View error message pane opens on the right and contains columns for Parameter Name, Data Type, Column Type, and Default Value.

  4. In the Default Value fields, type all of your default constraints.

    In this example, the P_DNAME field is the one with the constraint, so it requires a default value, for example, ACCOUNTING.

  5. After you enter the value(s), click Update to return to the main properties window.
  6. In the right pane, move the pointer over Operations and select Test Run.

    The Test Run pane opens on the right, where you can test the stored procedure. ACCOUNTING is automatically populated as the default value for P_DNAME.

  7. Enter additional parameter values (for example, 100 and SMITH) and click Test.

    The results appear in the Test Run results window as shown in the following image.

  8. To return to the main properties window where you can generate schemas and create web services for the stored procedure, click OK.

Generating a Schema for a Stored Procedure

How to:

The following procedure describes how generate a schema for stored procedures for relational databases and how to generate a schema for iWay stored procedures for non-relational databases.

Procedure: How to Generate a Schema for a Stored Procedure

To generate a schema for a stored procedure:

  1. If you are not connected to a defined target, connect to one, as described in How to Connect to a Defined Target.
  2. Expand the Schemas node under the desired connection.
  3. Select the database containing the stored procedure for which you want to generate a schema.
  4. Expand the Procedures node.
  5. Select the stored procedure.
  6. In the right pane, move the pointer over Operations and select Generate Schema.

    The Schemas table appears in the right pane containing three columns named Part, Root Tag, and Schema and three rows named Request, Response, and Event as shown in the following image.

    1. To view the request schema, click the ellipsis symbol that is located in the third column of the Request row.
    2. To view the response schema, click the ellipsis symbol that is located in the third column of the Response row.

      The schemas are now ready to use. You can use the generated request schema to create a sample XML document to be used by the adapter.

  7. Click OK.

Testing a Stored Procedure

How to:

This section describes how to test a stored procedure using iWay Explorer.

Procedure: How to Test a Stored Procedure

Perform the following steps to test a stored procedure:

  1. Connect to an available RDBMS target that is configured for an Oracle connection.

    For more information, see How to Define a New Target.

  2. In the left pane, expand the Schemas node.
  3. Expand an available group node that contains the stored procedure you want to test, for example, SYS.
  4. Expand the Procedures node.
  5. Right-click a stored procedure, for example, DATABASE_NAME, and select Test Run from the context menu.

    The Test Run dialog box opens.

    To view an XML representation, click View XML.

    To return to the default view, click Table View.

  6. Click OK when you are finished.