How to use a stored procedure to retrieve data from a JDBC data source

This procedure assumes you have already created the JDBC data source that this data set uses.

1	In Data Explorer, right-click Data Sets, then choose New Data Set.

2	In New Data Set, specify the following information:

1	In Data Source Selection, select the JDBC data source to use.

2	In Data Set Type, select SQL Stored Procedure Query.

3	In Data Set Name, type a name for the data set. Choose Next.

Query displays a template for executing a stored procedure.

3	In Available Items, navigate to the stored procedure. Select the stored procedure, and drag it to the text area. The stored procedure name appears at the insertion point. Type the arguments if the stored procedure uses parameters.

Figure 3-3 shows a stored procedure selected in Available Items. The stored procedure has three parameters. The text area displays the call statement to run the stored procedure.

Figure 3-3  Displaying stored procedures in a database

4	Choose Finish to save the data set.

5	Verify the results returned by the stored procedure, using one of the following steps:

If the stored procedure returns a result set, choose Preview Results to see the data rows. If a stored procedure returns multiple result sets, select the result set you want by specifying the result set’s name or number. To do so, choose Settings and, in Result Set Selection, specify the result set. Figure 3-4 shows an example of selecting the second result set.

Figure 3-4  Selecting a result set

If the stored procedure returns output parameter values, choose Preview Output Parameters. Figure 3-5 shows an example of the values returned by the stored procedure shown in Figure 3-3.

Figure 3-5  Previewing output parameter values