Using a stored procedure to retrieve data from a JDBC data source
BIRT Designer also supports using a stored procedure to retrieve database data. As its name suggests, a stored procedure is a procedure that is stored in a database. A stored procedure consists of SQL statements used to execute operations or queries on a database. A stored procedure can:
Return a result set, which is a set of rows.
Accept input parameters, which are parameters used to pass data to the stored procedure. For example, a stored procedure runs a query that returns the customer name and credit limit for a specified customer ID. In this case, the stored procedure defines an input parameter to get the customer ID.
Use output parameters to return values. The stored procedure described in the previous point uses two output parameters to return the name and credit limit for a specified customer ID.
To run a stored procedure, use the call statement. The following statement is an example of running a stored procedure named getEmployeeData that contains no parameters. This type of stored procedure typically returns a result set.
{call getEmployeeData()}
The following is an example of running a stored procedure named getClientData that contains three parameters. The first and second parameters are output parameters, and the third is an input parameter.
{call getClientData(?, ?, 103)}
Each ? character is a placeholder for the output parameter value that the stored procedure returns.
BIRT relies on the capabilities of the underlying JDBC driver in its support for stored procedures. For more robust support, use a JDBC driver that fully implements the JDBC interfaces that are related to stored procedures, including those that provide its metadata. The jTDS project on SourceForge.net, for example, provides a pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server, which supports stored procedures.
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 5‑12 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 5‑12 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 methods:
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 5‑13 shows an example of selecting the second result set.
Figure 5‑13 Selecting a result set
If the stored procedure returns output parameter values, choose Preview Output Parameters.
Figure 5‑14 shows the values returned by the stored procedure shown in
Figure 5‑12.
Figure 5‑14 Previewing output parameter values
