Select Procedures

Select the type of stored procedures required in your OTD.

To Select Stored Procedures

1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.

   Figure 47 Select Procedures and specify Resultset and Parameter Information

   

   Note - A fully qualified Procedure Name consists of two parts: Schema Name, and the Stored Procedure Table Name. Code that is generated in a Java Collaboration Definition appears fully qualified when the Use fully-qualified procedure names in the generated Java code checkbox is selected. When this checkbox is not selected, then only the Stored Procedure Table Name appears.

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.

   Figure 48 Add Procedures

   
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.

   Figure 49 Procedure Parameters

   
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.

   Figure 50 Edit Resultset

   

   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 by the “Execute” 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.

   Note - In some situations, stored procedures that uses a Select statement may not return a resultset when a conditional statement is used. In this case, no data is returned when the next() method is called, even if the resultset available() method returns “true”. This result is consistent with the type of driver we use.

   Note - When you use Insert, Update, and Delete operations, in addition to using Select, the stored procedure will return results for each operation used. We recommend invoking the enableResultSetsOnly() method if you only want to return a resultset for the Select statement.