Stored Procedures

[mdahlman]

Can I use a stored procedure with JasperReports (and JasperServer)?

 

Yes.

 

But it's more complicated than just "Yes". As noted in the JR Definitive Guide, "JasperReports uses a java.sql.PreparedStatement behind the scenes to execute that SQL query through JDBC and retrieve a java.sql.ResultSet object to use for report filling." Therefore a stored procedure can be used, but the following conditions follow from the previous statement:

- "The stored procedure must return a java.sql.ResultSet when called through JDBC."
- "The stored procedure cannot have OUT parameters."

 

Most stored procedures follow these conditions. So you can write your SQL query as a single stored procedure call, and everything will just work. The stored procedure can take arguments which you pass in using JR parameters.

 

But the famous exception is Oracle. Oracle stored procedures require an OUT parameter, so the standard JR syntax can't be used. In this case you need a custom query executor. Fortunately Barry Klawans already wrote a JRQueryExecuter for use with Oracle Stored Procedures. It's available in the project OracleStoredProcedures:

http://jasperforge.org/plugins/project/project_home.php?group_id=173

 

A few extra notes to help capture a few more keywords:
- The OracleStoredProcedures project can handle REF_CURSORs.
- Min successfully used this syntax with Microsoft SQL Server: Exec RWsp_PS_PremiumReportProducts_Get $P{iStCd}, $P{iArea}, $P{iBank}, $P{iRptCd}, $P{iMrkt}, $P{iInstrument}, $P{iInstrType}, $P{iCompetitor}

[mdahlman]

Michael wrote on saleseng 19 Aug 2008:

Is it possible to modify the query executer so that it becomes a valid custom datasource in JasperServer?

The query executer is expecting a connection from the parameter map, which is obviously inapplicable in JasperServer, because in JS 3.0 when we define a query executer type custom datasource, we don’t define any datasource at all. Is it possible to modify the query executer so that it can retrieve the DB URL, username and password from the report parameter and create a connection by its own?  (the user will then have to define the input controls for these three properties)

Cheers,

Michael Y. Zhou

JasperSoft Technical Support

 

 

Lucian replied on saleseng 19 Aug 2008:

 

Using an additional SQL-like query executer (that requires a JDBC connection to be set as REPORT_CONNECTION) in JasperServer is possible.

No custom JasperServer datasource has to be implemented, instead the following need to be done:

 

1. Register the query executer in jasperreports.properties. e.g.:

net.sf.jasperreports.query.executer.factory.oracle-sql=..OracleSqlQueryExecuterFactory

 

2. Add the query language to the list of languages supported by the JDBC and JNDI/JDBC data sources.  To do this, edit applicationContext.xml, find a bean named "dataSourceServiceFactories" and change the supportedQueryLanguages property of the DataSourceServiceDefinition beans mapped to JdbcReportDataSource and JndiJdbcReportDataSource. The added lines are <value>oracle-sql</value> in the code below.

 

3. When you upload a JRXML with an Oracle SQL query into a report unit, all you have to do is  select a JDBC or JNDI/JDBC data source as the report unit data source.

 

Regards,

Lucian

Code:

<bean id="dataSourceServiceFactories" class="com.jaspersoft.jasperserver.api.engine.jasperreports.util.DataSourceServiceFactoryImpl">    <property name="factory">        <ref bean="beanForInterfaceFactory"/>    </property>    <property name="serviceDefinitionMap">        <map>            <entry key="com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JdbcReportDataSource">                <bean class="com.jaspersoft.jasperserver.api.engine.jasperreports.util.DataSourceServiceDefinition">                    <property name="serviceBeanName" value="jdbcDataSourceServiceFactory"/>                    <property name="supportedQueryLanguages">                        <set>                            <value>sql</value>                            <value>SQL</value>                            <value>oracle-sql</value>                        </set>                    </property>                </bean>            </entry>            <entry key="com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JndiJdbcReportDataSource">                <bean class="com.jaspersoft.jasperserver.api.engine.jasperreports.util.DataSourceServiceDefinition">                    <property name="serviceBeanName" value="jndiJdbcDataSourceServiceFactory"/>                    <property name="supportedQueryLanguages">                        <set>                            <value>sql</value>                            <value>SQL</value>                            <value>oracle-sql</value>                        </set>                    </property>                </bean>            </entry>

[mdahlman]

Sherman wrote on saleseng 4 Apr 2008:
We can use REF_CURSORs.

From the Oracle Stored Procedures JasperReports data source project: http://jasperforge.org/plugins/project/project_home.php?group_id=173

To do so you have to be using iReport 1.2.6 or later. Follow these steps:

1) Select Options->Query Executers in the main menu bar
2) In the resulting dialog press "New"
3) Set the Language to "plsql" and the Factory Class to "org.jasperforge.jaspersoft.demo.PlSqlQueryExecuterFactory"
4) Download and build this project
5) Copy the build/classes directory to your iReport directory.
6) In iReport select the Data->Connections/Datasources menu
7) In the resulting dialog press "New"
8) Set the Name to "PlSql" and the Type of Connection to "Query Executer Mode" (1.2.6 has this as "Query Executor Mode") and press "Save"
9) Add the Oracle JDBC driver to iReport's lib directory. (The Oracle 9 version is in the lib directory of this project, in classes12.jar. More recent Oracle JDBC drivers are named ojdbc14.jar.)


Now you can create a report that uses an Oracle stored procedure.
1) Create a new report.
2) Add a parameter to your report called "cursor" with the type "java.sql.ResultSet". Be sure that "is for prompting" is turned off.
3) Set the data source to the "PlSql" source defined above.
4) Select the Data->Report Query menu
5) TYPE "plsql" into the Query language field - it won't appear in the list of languages, but you can type into the field.
6) Set the query to "{call your_procedure($P{cursor}, $F{some_field})}". The import thing is that the "cursor" parameter must be where ever the Oracle REF_CURSOR is in the parameter list.

To get this into JasperServer will take some configuration work.

Sherman

[Ravi Kumar]

Hi,

i am using MS SQL Stored procedure In Jasper Server to generate the Report,

but my stored procedure Returns Two Results Sets for two differnet Charts in Dash board Report

how to use those two  results  Sets? 

i am designing the Report using IReport designer 5.6.0.