Select Procedures

To Select Procedures

1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.
   
2. On the Select Procedures window, enter the name of a Procedure or select a table from the drop down list. Click Search. Wildcard characters can also be used.
3. In the resulting Procedure Selection list box, select a Procedure. Click OK.
   
4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure (see the following figure).
   
5. To restore the data type, click Restore. When finished, click OK.
6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.
7. Click Add to add the type of Resultset node you would like to generate.
   

   The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

   • By Executing Mode — “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes.

   • With Assistance Mode — "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query.

   • Manually Mode — "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query.

     SELECT A, B, 3*C FROM table T

     is generated by the database. In this case, "With Assistance" mode is a better choice.

     If you modify the ResultSet generated with the by the “By Executing Mode” of the Database Wizard, you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.