support

esProc Act as Data Source to JasperReport

esProc packages the computation capacity into a standard JDBC interface, acting as the data source of reporting tool.

The JDBC driver of an average database is just an interface. The actual computation is conducted in the database running as a server. By compassion, esProc JDBC makes a difference by embedding all computation engine. In other words, esProc does not require an independent server. Just put all jars used by esProc JDBC to the class path of application, such as the reporting tool.

esProc JDBC has implemented the calling interface of JDBC stored procedure, executing the program cellset and returning the result set through this interface. Logically, you can regard esProc JDBC as a database having no tables.

Let’s take JasperReport as an example to illustrate the procedure of applying esProc JDBC data sources in the reporting tool.

In the first simple example with no parameters assume the esProc program file below named my.dfx.

A
1 =mysql.query(“select * from employee”)
2 result A1

The computation in A1 cell:

The detailed integration steps in JasperReport:

  1. Create the configuration file dfxConfig.xml for esProc JDBC:

<?xml version=”1.0″ encoding=”ISO-8859-1″?>
<dfxConfig>
<!–the directory to store the dfx file, the above mentioned my.dfx is stored here. Only after the directory has been configured here can the esProc JDBC be correctly located –>
<paths>c:\\</paths>
<!– This is the data source used when configuring the dfx to execute. You can configure multiple data sources below this node. –>
<jdbc-ds-configs>
<!– Single data source configuration –>
<jdbc-ds-config>
<name>tdb</name>
<url>jdbc:mysql://192.168.0.95:3306/test</url>
<driver>com.mysql.jdbc.Driver</driver>
<userName>root</userName>
<password>123456</password>
<dbCharset>ISO-8859-1</dbCharset>
<clientCharset>ISO-8859-1</clientCharset>
<useSchema>false</useSchema>
<caseSentence>false</caseSentence>
<needTranContent>false</needTranContent>
<needTranSentence>false</needTranSentence>
<!–Whether connecting automatically when using this data source in dfx –>
<autoConnect>true</autoConnect>
</jdbc-ds-config>
</jdbc-ds-configs>
</dfxConfig>

Special attention: Please keep the file name of dfxConfig.xml unchanged. Otherwise, esProc JDBC cannot find the file.

2. Put the related jar of esProc JDBC to the classpath (menu item Tools->Options->classpath).
4 in total

  • dm.jar—————–Computation engine
  • log4j_128.jar——–Apache logging package
  • icu4j_3_4_5.jar—–IBM internationalization package
  • poi2.jar—————Excel read/write package by Apache. If not used in dfx, then you can just ignore it.

These jars can be found under the [esProc installation directory] \designer\lib.

Of course, other database driver used in dfx will also be put in this classpath, taking the database driver My SQL in this case for example.

In addition, add an additional directory on the classpath to store the dfxConfig.xml, and put the dfxConfig.xml edited above to this directory (the last row in below fig)

You can add dfxConfig.xml directly to dm.jar (put it under the root directory). Then you will not need to add this directory on the classpath to store this file. However, you may need to repackage this jar for every time you modify the dfxConfig.xml.

3. Create the data source dfxjdbc of Database JDBC connection type, as shown in the below figure:


You can just fill in the Driver and URL. Because there is no independent server, the URL must be local. esProc JDBC is an incomplete database, requiring no user name and password.
JasperReport currently has an additional data connection.

4. In the Report Wizard, use dfxjdbc data source to create the report. In the Query step, enter the “call my()”. Then, the “my” will correspond to the above mentioned my.dfx.

JasperReport will retrieve the fields of my.dfx return result set:

Drag these fields to the IDE panel and the below report will thus be generated:

5. Preview

esProc JDBC also support the parameter. Let’s assume that there is a cellset program file my2.dfx with the below parameters:

A
1 =mysql.query(“select * from employee where sex=? and age>?”,param1,param2)
2 result A1

Set the parameters of param1 and param2 respectively to “male” and “30″ when running and you will get the below result:

Based on the above mentioned reports without parameters, you can proceed with the following steps to use the my2.dfx with parameters:

    1.Edit the Query for the above report (right click and select Edit Query):

    2. Add the two parameters of p1 and p2:

3. Drag the p1 and p2 to the query statement and add the comma and bracket. The final query statement is “call my2($P{p1},$P{p2})”, as shown in the below figure:

4. After editing, run the preview, JasperReport will prompt the below dialogue twice to request the input of P1 and P2 parameter values:

The report preview is as shown below:

Endnote: For further information on esProc JDBC and the particular instruction details on dfxConfig.xml, please refer to the esProc official document (Downloadable from www.esproc.com).