Jump to: navigation, search

Difference between revisions of "StoredProcedure (BIRT)"

(BIRT Report)
(Description)
Line 15: Line 15:
  
 
== Description==
 
== 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 [http://www.eclipse.org/birt/phoenix/db/ here].  Once this database is loaded you will then need to create the stored procedure used in the reprot.  The source for
 +
the procedure is below.
 +
 
===Stored Procedure SQL===
 
===Stored Procedure SQL===
  
Line 24: Line 28:
 
  END
 
  END
 
  GO
 
  GO
This procedure is called by using the following statement in the Data Set Editor for an SQL Stored Procedure Query.
+
 
 +
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 (?, ?)}
 
  {call OrdersByProductProc (?, ?)}
 +
 +
The first ? referes 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 parameter that is then passed to the second data set, which contains the stored procedure.  
 
The example uses one data set to populate a dynamic parameter that is then passed to the second data set, which contains the stored procedure.  

Revision as of 14:07, 30 January 2007

Stored Procedure

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 reprot. 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 ? referes 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 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.