StoredProcedure (BIRT)

< To: Report Developer Examples (BIRT)

Examples for the BIRT Project are contributed using Bugzilla. This example is Bugzilla ID 172046. If you would like to contribute an example see the example contribution guidelines.

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

Example Report Zipped

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.

Stored Procedure SQL

CREATE PROCEDURE OrdersByProductProc (IN Product varchar(50), OUT test int   )
BEGIN

SELECT count(*), productName FROM orderdetails, products WHERE orderdetails.productCode = products.productCode AND products.productName = Product GROUP BY   products.productName;
Select Count(*) INTO test FROM orderdetails;
END
GO

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.

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

BIRT Report Design File, Java source, and Stored Procedure jar file Zipped

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.

SQL issued in ij

-- create the procedure in SQL

create procedure DEPT_TIME_ARRIVAL (IN orig_airport varchar(128))
external name 'DerbyStoredProc.departureTimeArrival'
DYNAMIC RESULT SETS 1
parameter style java
language java;

-- 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.

---