Using a stored procedure to retrieve data from a JDBC data source
BIRT Report 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.