Use Stored Procedure in JReport

What is the stored procedure?

A stored procedure is a compiled program, consisting of one or more statements and is saved into a database.  The statement in the stored procedure can be a SQL statement or a control flow statement (like If-else statement, Loop statement).  Because the stored procedure is stored in the DBMS and thus can be called locally or remotely. 

As a program, a stored procedure can take parameters.   There are three types of parameters: IN, OUT and INOUT.  The IN parameter lets you pass values to the procedure.  The OUT parameter will return values to the caller.  The INOUT parameter enable you to pass initial values to the procedure and returned the updated value to the caller. 

A stored procedure can use the following ways to return data.  

• Return a result set
• Return a value
• Return values via OUT parameters

How does the stored procedure work with JReport?

In addition to support the table and views in a database, JReport supports the stored procedure.  User can create, design and run a report on the result set returned by a stored procedure.

In JReport system, a report bases on a data set, JReport will generate report on the data in the data set when a report is running.  When a report uses a query, the data set is the result of the query.  In the same sense, when a report uses a stored procedure, the data set is the result set returned by the stored procedure. 

The stored procedure can return a value, single result set or multiple result sets.  Currently JReport supports the stored procedure that returns result set(s), if a stored procedure returns more than one result set, the first result set will( be) used in JReport.

Advantage of stored procedure

The feature of using stored procedure(is) to design and generate report(to)enhances the ability of JReport.  Using the stored procedure in JReport, user can take the advantage of stored procedures.  The advantages are:

Power and Flexibility

In stored procedure, the program can take parameters and control the execution of statement according to the records in database and values of parameters.   So user can implement more complex business logic inside the stored procedure.

In stored procedure, user can insert/delete/update records.   The report base on the stored procedure can not only analysis and represent data in database, but also (can)  perform database manipulations.

A stored procedure will return a result set.   The procedure can perform selection on records in database tables.  so the result set returned is more flexibility.(flexible)

Performance

Because the stored procedure is stored in DBMS, the procedure is normally be compiled or pre-parsed and therefore the execution of statement has more efficiency. 

The result set returned by the stored procedure is processed in the server site, so less bytes will be transferred via the network.

Security

Because the permission to execute a stored procedure is independent, so user can be granted a permission to execute a stored procedure even if the user does not have the permissions on the tables or views which the stored procedure will access.

This help will shows you how to use stored procedure to design and generate a report.

1. Add a procedure to the catalog.

2. Edit parameter values.

3. Create a report using the stored procedure.

4. Edit a report using the stored procedure.

5. Run a report which uses a stored procedure.

1. Add a procedure to the catalog

Before you want to use stored procedures in JReport designer, you should first add the stored procedures to a catalog.  When a stored procedure is being added into a catalog, our program will retrieve the stored procedure's information from DBMS via JDBC; Prompt user to input the value of IN and INOUT parameters if the stored procedure has; Execute the stored procedure once trying to get the result set, and then the field objects of procedure object will be created according to the result set returned.  You will then use the field objects to design your reports.

When user selects New Procedure from toolbar or menu, the Add Procedures dialog box will be displayed.

Add Procedures Dialog

The Add Procedures Dialog displays all the stored procedures in the database, allows user to select the stored procedures and adds them to a catalog.

Procedures:

The panel lists all the stored procedures in three level tree.  The top level is SQL-catalog, second is SQL-scheme and then   Stored Procedures.   User can select one or more stored procedures and add them to the catalog.   Note:Not all DBMSs support database stored procedures, so you may see nothing in the panel in such a case.

Add

Add the selected the stored procedures to the catalog.

Done

Complete adding stored procedures and close the Add Procedures dialog box.

Help

Display this help.

Parameters Dialog

The Procedure Parameters dialog displays all parameters, and provides means to edit the value of parameters.

The Procedure Parameters dialog will appear in two cases:

• When add a procedure with parameters.
• When select Parameters... menu.

Parameters

It lists IN and INOUT parameters in the stored procedures in a table box.  It displays names, values and types in three columns.   Click on Value cell, the editor will appear, in which you can edit the value of the parameter.

Format

Display the value format of the current parameter.

OK

Closes this dialog box and accepts changes.

Cancel

Closes this dialog box and cancels the changes.

Help

Display this help.

2. Edit parameter values

When you adding a stored procedure, you will be asked to input values for its parameters.  Those values will be saved inside the stored procedure object in the catalog and will be used as the default value when executing this procedure.  You can edit the stored procedure parameters at any time .you wish.

3. Create a report using the stored procedures.

To create a report on the result set returned by a stored procedure, you should select the stored procedure object you want in the Data panel in Report Wizard.

When you finish all works, click on the Finish button.   A report which uses a stored procedure is created.

4. Edit a report using the stored procedure

You can select the fields of the stored procedure object and place them in the place where you want them to be in JReport designer just like you insert other fields.

5. Run a report which uses a stored procedure.

Before the report built on a stored procedure runs, You need to specify the values for the IN and INOUT typed parameters defined in the stored procedure.    The values stored inside the stored procedure object in the catalog will be the default values.

When the report is about going to run, the parameter dialog will be displayed, and you can input the values of the stored procedure parameters in this dialog.