MySQL Example

Introduction

This example demonstrates using a JDBC driver to call a stored procedure from MySQL.
The Stored procedure takes two arguments. One is an input parameter and specifies what
product name the stored procedure is executed for and returns the number of orders that included the given product. The second parameter is an output parameter and
returns the count of total orders.
Add comments at the bottom of the example.

BIRT Version Compatibility

This example was created and tested wit BIRT 2.1.1. It should also be compatible with newer versions of BIRT.

Example Files

Description

In order to use this example, you will first need to have the Classic Models Database installed on MySQL. This example database is available here. Once this database is loaded you will then need to create the stored procedure used in the report. The source for
the procedure is below.

Once the procedure is created, it can be called by using the following statement in the Data Set Editor for an SQL Stored Procedure Query.

{call OrdersByProductProc (?, ?)}

The first ? refers to the input parameter and the second ? refers to the output parameter. See the dataset parameters in the Data Set editor.

The example uses one data set to populate a dynamic report parameter that is then passed to the second data set, which contains the stored procedure.
The results of the stored procedure are presented in a table element. In the table footer the output parameter of the stored procedure is referenced as follows:

"Total Orders: " + outputParams["totalorders"]

Comments

Please enter comments below by selecting the edit icon to the right.
You will need a Bugzilla account to add comments.

Apache Derby Example

Introduction

This example demonstrates using a JDBC driver to call a stored procedure from Apache Derby.The Stored procedure takes one argument. It is an input parameter and specifies the name of the origination airport in the Flights table in the Derby database, toursDB. It returns the destination airport, departure time, and arrival time of flights available from the origination airport specified.

Add comments at the bottom of the example.

BIRT Version Compatibility

This example was created and tested with BIRT 2.1.1. It should also be compatible with newer versions of BIRT.

Example Files

Description

In order to use this example, you need to have Apache Derby version 10.1 or higher installed. The database accessed in this example is the toursDB database from the %DERBY_HOME%/demo/databases directory which comes with the bin distribution of Derby available for download here. One way to call a stored procedure (in this case a Database-side JDBC procedure) in Derby is:

Create a public Java class with a static method

Create a jar file with this class

Issue SQL to create the stored procedure

Install the jar in the database

Set the database classpath to include the stored procedure

Call the stored procedure using the CALL statement

The public java class, DerbyStoredProc is contained in the zip file available for download above. It contains a static method, departureTimeArrival, which selects the destination airport, departure time and arrival time based on the origination airport used as the input parameter.

Once this class is compiled and a jar file containing this class is created, use the Derby command line tool, ij, to create the stored procedure and install the jar file into the database using the SQL command shown below.

-- install the procedure in the database via the jar file
-- note: edit this command based on the actual location of the DerbyStoredProcJar.jar file
CALL sqlj.install_jar('C:\derby\DerbyStoredProcJar.jar','DerbyStoredProc',0);

-- set the database classpath to include the stored procedure
CALL syscs_util.syscs_set_database_property(derby.database.classpath','APP.DerbyStoredProc');

-- test the stored procedure in Derby prior to calling it in BIRT
CALL DEPT_TIME_ARRIVAL('LAX');

Calling the Stored Procedure in BIRT

Once the procedure is created, it can be called by using the following statement in the Data Set Editor for an SQL Stored Procedure Query.

{call APP.DEPT_TIME_ARRIVAL(?)}

The ? refers to the input parameter, in this case the origination airport. Select the 'Parameters' item in the Edit Data Set window to view the binding of the input value to the stored procedure with the parameter called Orig_Airport.

This example accepts an input parameter of the origination airport which maps to the column orig_airport in the toursdb APP.FLIGHTS table and selects the destination airport, departure and arrival time for the available flights for the origination airport selected as a parameter.

The first image below shows the prompt for the input parameter of the origination airport when previewing the report using the BIRT Report Designer.

The second image shows the report output after specifying an input parameter of SFO for the origination airport.

Comments

Please enter comments below by selecting the edit icon to the right.
You will need a Bugzilla account to add comments.