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 IDE

1.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.

esProc_birt_integrate_1

 

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

 

esProc_birt_integrate_2

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_birt_integrate_3

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 dfx Path configured in the config.xml. Take the stocks.dfx for example, query the closing price of stock in a certain month.

esProc_birt_integrate_4

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.

esProc_birt_integrate_5

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.

esProc_birt_integrate_6

Set parameters

esProc_birt_integrate_7

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

esProc_birt_integrate_8

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.

esProc_birt_integrate_9

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Reporting tool and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s