Creating and calling SQL Stored Procedures

M3 Web Services also supports the calling of Stored Procedures. Store Procedures is considered to be an advanced topic, and differs from database to database, therefore, it is difficult to document. If you want to use Stored Procedures, it will be necessary to consult the documentation for your database and JDBC driver.

However, the basic SQL syntax for calling a stored procedure through JDBC is as follows: {call MY_PROCEDURE}

Below, you find a partial example of creating and calling a stored procedure on i5/iSeries which lists an M3 customer table as in our other examples. First, the stored procedure is created.

Note: There are many ways to create the stored procedure, and there are IBM Redbooks and manuals that cover this in more detail.
  1. On the i5/iSeries, we use the SQL command to create the Stored Procedure:

    Create procedure cgjlib/CUST_LIST result sets 1 language SQL BEGIN declare c1 cursor with return for select okcuno, okcunm from mvxsp15/ocusma; open c1; END

    The Stored Procedure is named CUST_LIST, and is placed in the library CGJLIB. This library must be defined within the libraries parameter of the JDBC driver URL in the server configuration.

  2. In the Web Service Repositories view, expand the repository location where the web service is located for which you want to create a method.
  3. Select the web service for which you want to create a method, right- click and select New Method.

    The Create a new Web Service Method window is displayed.

  4. Select type SQL Query from the drop-down list and click Finish. The SQL Editor is opened.
  5. Specify the SQL Statement and ensure that the Stored Procedure check box is selected.
  6. Click Output. The Output section is expanded.
  7. Click Add result set. The Result set data section is expanded.
  8. Specify a name for the result set. In this example, we have used the name new.
  9. Add a result set entry, click New.
  10. Specify the name and type of the entry to contain the output and click Add.
    Note: There are two outputs, okcuno and okcunm. Entries for each of these are needed. It is important to get these in the same order.

    The name of the field can be anything you like (spaces are not allowed), and the type should match the field type from the SQL statement. The field types are JDBC data types, and may not match what you are used to in your specific database. In the main, the numeric fields are easy to identify, but string or character fields are usually represented as type VARCHAR (variable length character field).

  11. Deploy the web service.
  12. Create and run a test case as described in section Testing a Web Service.