How do I call a stored procedure from Jasper Report?

How do I call a stored procedure fr开发者_开发百科om Jasper Report?

The JasperReports Ultimate Guide contains this information about using store procedure:

Certain conditions must be met to put stored procedure calls in the SQL query string of a report template:

The stored procedure must return a java.sql.ResultSet when called through JDBC.

The stored procedure cannot have OUT parameters.

Below are the steps to call a stored procedure to build a report using iReport 4.5/4.5.1 JasperReport and using Oracle Express database.... hope this helps....

1.In your iReport designer go to Tools --> Options --> and in the Classpath Tab click Add JAR and add the OJDBC14.jar to the classpath.

2.Go to Query Executer tab and set the following Language: plsql Query Executer Factory: com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory Fields Provider Class: com.jaspersoft.ireport.designer.data.fieldsproviders.SQLFieldsProvider

3.Select Database JDBC Connection

4.Select Oracle as the JDBC Driver as shown in the image below and verify the connection by clicking the Test button (Make sure you check the Save Password check box)

5.Create a blank report by giving a report name and save it.

6.Open the report in the designer and right click on the report name and click on Edit Query

7.Set the query language to plsql

8.Call your procedure with in { } {call PUBLISHER_AND_BOOKS(&P(P_PUBLISHER_ID), &P(ORACLE_REF_CURSOR))} Note: P_PUBLISHER_ID is of type string and ORACLE_REF_CURSOR is of type java.sql.ResultSet data type custom parameter. You can create this by clicking the New Parameter button. If you have more input parameters use ‘,’ as your delimiter as shown in the above example.

9.Click Ok and proceed with the report design.

10.In the designer window right click on Fields and add click Add Fields and make sure all the field name matches the column name in your stored procedure

11.Now right click on the parameters and add the parameters matching the stored procedure NOTE: make sure you uncheck the "Use for Prompt" in the property for your out parameter in our example its ORACLE_REF_CURSOR is the out parameter.

12.Drag and drop the fields in the report detail band as shown below

13.Click preview to run the report you will be prompted with the input parameter

All the steps are captured in detail with images and available in the below link hope this helps...

http://meezageekyside.blogspot.com/#!/2012/04/jasper-reports-ireport-45-using-oracle.html

<queryString>
        <![CDATA[Call procedure_name ($P{parm1},$P{parm2},"$P!{parm3}","$P!{parm4}","$P!{parm5}",$P{parm6},$P{parm7});]]>
    </queryString>

With MySQL You can call the stored procedure just like you call any other query, using the queryString.

Here is how i tackled the issue, you can easily do this by using a JR scriptlet (java bean). Below is the sample java code and once you have the jar file add it to the jasper report classpath and reference the same on your report properties.

-- Please make sure you use identicle names as given in your report when accessing variable/parameter values and in setting Variable values (you can't set parameter values, just the vaiables)

package com.scriptlets;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;

import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRScriptletException;

public class Icdf extends JRDefaultScriptlet {

  public void afterReportInit() throws JRScriptletException {

    // get the current connection from report via parameters
    Connection conn = (Connection) this
      .getParameterValue("REPORT_CONNECTION");

    int userId = 100; //use this.get__ to access from report
    try {
      if (conn != null)
        callOracleStoredProcOUTParameter(conn, userId); // SP call
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  private void callOracleStoredProcOUTParameter(Connection conn, int userId)
  throws SQLException {

    CallableStatement callableStatement = null;

    String getDBUSERByUserIdSql = "{call someStoredProcedureName(?,?,?)}";

    try {

      callableStatement = conn.prepareCall(getDBUSERByUserIdSql);

      // setting parameters of the callablestatement
      callableStatement.setInt(1, userId);
      callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
      callableStatement.registerOutParameter(3, java.sql.Types.DATE);

      // execute getDBUSERByUserId store procedure
      callableStatement.executeUpdate();

      // get the required OUT parameters from the callablestatement
      String userName = callableStatement.getString(2);
      Date createdDate = callableStatement.getDate(3);

      // --just to check, you can view this on iReport console
      System.out.println("UserName : " + userName + "CreatedDate : " + createdDate);


      // set the values to report variables so that you can use them in
      // the report
      this.setVariableValue("variable_name1", userName);
      this.setVariableValue("variable_name2", createdDate);

    } catch (SQLException e) {
      e.printStackTrace();
    } catch (JRScriptletException e) {
      e.printStackTrace();
    }
  }
}

very easy: 1. At tab outline. Right click to file jasper --> select Dataset & Query ... 2. Select DB, language plsql, & call with query follow Exp: {call packageName.procedureName( $P{a}, $P{b}, $P{c}, $P{d}) }

Good luck!

JasperReport doesn't support calling stored procedure/function directly from his SQL datasource. The best way I found to overcome that limitation is to create a Java bean that calls the stored procedure (via JDBC or Hibernate) and returns a collection of objects which represent the result set. If you're using iReport, just change the Data Source Expression to use that Java bean. There's good section on data sources in the (not free) iReport manuals.

Two possibilities for calling a procedure from Jasper reports,

1. If your procedure returns some result set and if you would like to use in the report, then you have to create a function, type and type table (for holding the result set) for calling the procedure.

2. If the procedure does some DML operation, then you can call directly (without a function)

In jasper studio(I'm using v5.5.1) you can call sp like this:

1. Select SQL as language
2. just Exec your sp and pass parameters to it

here is my sp that take to DateTime parameters and returns a set of result

EXEC dbo.SP_Report  @p1=$P{date_from}, @p2=$P{date_to}

In jasper report using queryString we can call a procedure like this

example:

        <![CDATA[{call PACKAGE.PROCEDURENAME($P{PARAM_NAME1},$P{PARAM_NAME2},$P{PARAM_NAME3},$P{PARAM_NAME4},$P{PARAM_NAME5},$P{PARAM_NAME6},$P{PARAM_NAME7},$P{OUT_PARAM_NAME8})}]]>

We can pass IN as well as OUT paramters and If you use cursor as out parameter and parameter class should be resultset(java.sql.ResultSet)