How to Integrate esProc with BIRT

esProc provides the standard JDBC to integrate with reporting tool. Let's take the BIRT for example to demonstrate the integration procedure.

1.  Integrate in IDE1.1 Create profile

Create two configuration files of config.xml and dfxConfig.xml required by esProc JDBC.

1.1.1  config.xml

<?xml version="1.0" encoding="UTF-8"?>

<Config Version="1">

         <!-- registration code-->

<regCode>HOjw6-9da4x-NXkD6-089IX@w</regCode>

<charSet>GBK</charSet>

         <!-- Configure the addressing path for dfx file. This path is the absolute path and multiple paths can be set and separated with “;”. The path to the dfx file can also be put on the class path of application project. The file in this path will be loaded prior to that in the addressing path -->

<dfxPathList>

<dfxPath>D:/dfxs</dfxPath>

</dfxPathList>

<dateFormat>yyyy-MM-dd</dateFormat>

<timeFormat>HH:mm:ss</timeFormat>

<dateTimeFormat>yyyy-MM-ddHH:mm:ss</dateTimeFormat>

         <!-- Data source configuration method 1: Configure the connection pool on the application server. Specify the data source name here -->

<DBList>

         <!-- Data source name, which must be consistent with the data source name in the dfx file. The hsql database is connected here -->

<DB name="demo">

<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo"/>

<property name="driver" value="org.hsqldb.jdbcDriver"/>

<property name="type" value="HSQL"/>

<property name="user" value="sa"/>

<property name="password" value=""/>

<property name="batchSize" value="1000"/>

<property name="autoConnect" value="false"/>

<property name="useSchema" value="false"/>

<property name="addTilde" value="false"/>

</DB>

</DBList>

<mainPath/>

<tempPath>temp</tempPath>

<bufSize>65536</bufSize>

<localHost/>

<localPort>0</localPort>

<logLevel>DEBUG</logLevel>

<callxParallel>1</callxParallel>

<nodeParallel>1</nodeParallel>

</Config>

1.1.2  dfxConfig.xml

<?xml version="1.0" encoding="GBK"?>

<dfxConfig>

         <maxConcurrents>10</maxConcurrents>

         <maxWaits>10</maxWaits>

         <log></log>

</dfxConfig>

1.2  Create the new report and load the driver

Create a new report. Under the DataSources, create a new data source of JDBC Data Source type, and proceed to the next step.

In the new data source window, click Manage Drivers and add the driver for esProc JDBC.

 We need to add five jar packages of dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar, and dom4j-1.6.1.jar. These jar can be obtained from the esProc IDE[installation directory]\esProc\lib directory. If the esProc program also takes other database as the data source, then the corresponding database driver needs to be added. As an example, hsqldb.jar is added in this case.

The two configuration files config.xml and dfxConfig.xml created in the first step must be put under the root path of classpath. For esProc, no function is available to set classpath witha directory. The configuration files can be compressed to the root directory of any above-mentioned jar, such asdm.jar, and the name must be left unchanged.

After the connection test, select the driver class name com.esproc.jdbc.InternalDriver (v1.0) in the Driver Class. Fill the database URL jdbc:esproc:local://, and leave the user name and password blank.

esProc JDBC is a completely embedded computing engine. All computing are completed in the embedded computing engine. So the URL is local, and it is not the complete database because there is no user name and password.

Once filled, click Test Connection till the data source connection test succeed.

1.3  Deploy esProc scripts

Copy the esProc script (dfx) to dfxPath configured in the config.xml. Take the stocks.dfx for example, query the closing price of stock in a certain month. 

1.4  BIRT calling

Create a new Data Sets, select the just configured data source, and then select the stored procedure as data set type. 

In the next step, fill in the query statement {call stocks(?)}. For the esProc scripts, the stored-procedure-like calling method is adopted. In this case, the "stocks" is the dfx name, and "?" is the parameter to pass, which is the month of query in this example. 

Set parameters: 

 The calling succeeds if the query result can be viewed in the data set preview.

2. Deloy on Web side

Let's take Tomcat6.0 as an example to demonstrate the application.

2.1 Prepare Web release package

Download BIRT4.3.0 release package birt-runtime-4_3_0.zip at: http://www.eclipse.org/downloads/download.php?file=/birt/downloads/drops/R-R1-4_3_0-201306131152/birt-runtime-4_3_0.zip

Copy the WebViewerExample directory to the webappsdirectory of Tomcat, and rename it as birt.

2.2  Create configuration file

Prepare two configuration files of config.xml and dfxConfig.xml, as introduced in the above-mentioned section “Integrate in IDE”.

2.3  Copy the relevant jar package

Copy the required files mentioned in above section “Integrate in IDE”: dm.jar, poi-3.7-20101029.jar,log4j_128.jar,icu4j_3_4_5.jar and dom4j-1.6.1.jarto the WEB-INF\lib of application. Compress the two configuration files to dm.jar.

2.4 Publish report

Copy stocks.rptdesign or other developed resulting reports to the root directory of application. Start Tomcat and visit http://localhost:8080/birt/frameset?report=stocks.rptdesign. The deployment succeed if the below contents appear.