iReport - Calling Stored Procedures

This article provides information about calling stored procedures in iReport Designer with different databases.

Various query languages are supported:

MySQL

• Choose SQL as query language.

• Call your procedure like below:

  call P_YOUR_PROCEDURE( $P{param_1}, $P{param_2},..., $P{param_X} )
  

Oracle

10g/XE

Important: Calling an Oracle stored procedure requires the use of a Reference Cursor as an OUT parameter. The screen shot below shows a simple stored procedure that includes a reference cursor as an OUT parameter.

• In iReport, define a Report Datasource to connect to your Oracle database. If the name of your JDBC driver displays in red (rather than black) in the dropdown list, then the driver cannot be located in the iReport classpath. Refer to the iReport Ultimate Guide, section 2.9: Creating a JDBC Connection.

  

• Create a new report. Choose a template and click Open this Template and complete the steps. (The Launch Report Wizard only supports SQL queries.)
• Select the datasource you created above.

• Edit the report Query. Choose plsql as query language. Choosing plsql creates a built-in parameter named ORACLE_REF_CURSOR with java.sql.ResultSet as its parameter class.

  

  Note: The plsql query executer was introduced in iReport 3.6. It is not yet integrated into JasperServer as of version 3.7. A separate article explains how to deploy the plsql query executer to JasperServer.

• Call your procedure like below. The parameter $P{ORACLE_REF_CURSOR} represents the oracle reference cursor and it is MANDATORY. Also, don't forget the curly brackets {} around your call.

  {call P_YOUR_PROCEDURE( $P{param_1}, $P{param_2},..., $P{param_n}, $P{ORACLE_REF_CURSOR} )}
  

• Create your fields to match what your procedure returns.

  Example: if your ref cursor returns a column named FIRST_NAME, then create a java.lang.String field named FIRST_NAME ( $F{FIRST_NAME} ).

  

• Drag fields into your report design and run (Preview) your report!

PostgreSQL

Note: this article will not cover all the possibilities of calling PostgreSQL functions.

PostgreSQL allows you to create 4 types of stored functions (or procedures):

• query language functions (SQL)
• procedural language functions (PL/pgSQL or PL/Tcl)
• internal functions
• C-language functions

For further information, please visit PostgreSQL online manual, chapter User-Defined Functions.

Usually, when you call your function in pgAdmin III, you can use this same call in iReport. Take a look at the example below.

SQL Functions Returning TABLE

• Choose SQL as query language.

• Call your procedure like below:

  SELECT *
  FROM your_sql_procedure( $P{param_1}, $P{param_2},..., $P{param_X} )
  

Microsoft SQL

Calling Microsoft SQL Stored Procedures from iReport

See also...

JasperServer - Calling Oracle Stored Procedures