SAP Knowledge Base Article - Public

1217404 - How to pass a parameter value to a Java Bean data source

Symptom

A Crystal report is using a Java Bean to connect to a data source. How do you pass a parameter value to the Java Bean to restrict the results returned?

Resolution

To pass a parameter value, modify the 'getResultSet' method of your Java Bean class to accept a parameter and then use this parameter in the SQL query.

For example:

import java.sql.*;

/**

* <p>Title: Java Bean Data Source Sample</p>

* <p>Description: Sample application to show how to use a Java Bean as a Data Source for CR 9</p>

* @version 1.0

*/

/* For information on how to use JDBC and ResultSets see Sun's tutorials and guides at <<http://java.sun.com/j2se/1.3/docs/guide/jdbc/inde x.html>> This site will explain the URL, Connection, Statement

/* and JDBC-ODBC Bridge driver that are used to populate the java.sql.ResultSet

*/

public class CR9SampleDataSourceBeanWithParam

{

private ResultSet resultSet = null;

private Connection con = null;

private String url = "jdbc:odbc:Xtreme Sample Database 9";

private String JDBCBridge = "sun.jdbc.odbc.JdbcOdbcDriver";

private String sqlQuery;

public CR9SampleDataSourceBeanWithParam ()

{

try

{

/* Ensure JDBC-ODBC Bridge exists */

Class.forName(JDBCBridge);

/* Create a connection to 'Xtreme Sample Database 9' ODBC DSN */

con = DriverManager.getConnection(url, "", "");

} catch (ClassNotFoundException e) {

System.out.println("Check to ensure that the JDBCODBCBridge driver is installed");

e.printStackTrace();

} catch (SQLException e) {

System.out.println("SQL Exception #" +

e.getErrorCode() + " : " + e.getLocalizedMessage());

e.printStackTrace();

}

}

public ResultSet getResultSet(int param) throws

java.sql.SQLException

{

/* Pass a parameter as an argument to the 'getResultSet' method and then append the  parameter to the SQL query */

sqlQuery = "SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name` FROM `Customer` `Customer` WHERE `Customer`.`Customer ID`=";

sqlQuery = sqlQuery + param;

/* Create an SQL statement to execute */

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

/* Execute the select statement to populate the ResultSet */

resultSet = stmt.executeQuery(sqlQuery);

return resultSet;

}

}

 

Keywords

PARAMETERS VALUES SEND SELECTION NARROW RESULT GET BACK DATA JAVABEAN Crystal Reports Java Bean Connectivity Parameters Result Set , c2015416 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

SAP Crystal Reports 9.0