How to call a stored procedure from a Jasper report?

Question

How to call a stored procedure from a Jasper report?

Answer 1

JasperReports Ultimate Guide contains this information about using the storage procedure:

To make stored procedure calls, certain conditions must be met in the SQL query string of the report template:

The stored procedure should return java.sql.ResultSet when called through JDBC.
A stored procedure cannot have OUT parameters.

Answer 2

JasperReport / SQL- . - Java bean, ( JDBC Hibernate) , . iReport, , Java bean. ( ) iReport.

Answer 3

iReport 4.5/4.5.1 JasperReport Oracle Express.... , ....

1. iReport → → " " " JAR" OJDBC14.jar .

2.Go to Query Executer : plsql Query Executer Factory: com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory Fields Provider Class: com.jaspersoft.ireport.designer.data.fieldsproviders.SQLFieldsProvider

3. JDBC

4. Oracle JDBC, , , "" (, " " )

5. , .

6. " "

7. plsql

8. {}       { PUBLISHER_AND_BOOKS (& P (P_PUBLISHER_ID), & P (ORACLE_REF_CURSOR))}       : P_PUBLISHER_ID , ORACLE_REF_CURSOR java.sql.ResultSet . , " ". , ', , .

9. Ok .

10. " " , .

11. , . . , " " out . ORACLE_REF_CURSOR - out.

12.Drag , .

13. , ,

, , ...

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

Answer 4

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

MySQL , , queryString.

Answer 5

, , JR (java bean). java- , jar, classpath .

- , , , / ( , 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();
    }
  }
}

Hide result

Answer 6

: 1. . jasper → Dataset Query... 2. DB, plsql : {call packageName.procedureName($ P {a}, $P {b}, $P {c}, $P {d})}

!

Answer 7

Jasper,

• , , , table ( ) .

• DML,   ( )

Answer 8

jasper ( v5.5.1) sp :

• SQL
• Exec sp pass

sp, DateTime

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

Answer 9

jasper queryString ,

:

        <![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 the same way as the OUT parameters, and if you use the cursor as the out parameter, and the parameter class should be resultet (java.sql.ResultSet)