esProc Integration & Application: Integration by BIRT

esProc provides standard JDBC to integrate with BIRT and other reporting tools. Here we’ll explain the process of integration of esProcand BIRT in development environment and WEB application environment.

1.Integration in development environment

1.1.Create configuration files

Create the two configuration files – config.xml and dfxConfig.xml – necessary for 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 dfx file’s searching path, which is an absolute path. There can be multiple paths separated by semicolon(s). The dfx file can also be placed in the application’sclasspath which has the priority over a searching path for loading the file-->

<dfxPathList>

<dfxPath>D:/dfxs</dfxPath>

</dfxPathList>

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

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

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

<!--Method one for datasource configuration: configure connection pool in the application server and specify the datasource name-->

<DBList>

<!--Datasource name must be consistent with that in the dfx file. Here HSQL database is used-->

<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 a report and load the driver jars

Create a report, add JDBC Data Source to the list of “DataSources”, then move on to the next step.

Click “Manage Drivers” on the New Data Source window to add esProc JDBC’s driver jars. 

Five jars - dm.jar,poi-3.7-20101029.jar,log4j_128.jar,icu4j_3_4_5.jaranddom4j-1.6.1.jar– need to be added. They can be found in esProc IDE’s [installation directory]\esProc\lib directory. If esProc also uses other databases as the datasources, then the driver jars of these databases need to be added too. For instance, hsqldb.jar has been added here.

Placethe two configuration files – config.xml and dfxConfig.xml – created in the first step in the classpath’s root path (no function of setting adirectory-style classpath is provided here and the configuration files can be compressed into a root directory of any of the jars above like dm.jar), with their names remaining unchanged.

After that, select com.esproc.jdbc.InternalDriver( v1.0)in the drop-down list of the Driver Class and enter the database URL jdbc:esproc:local://. Leave both the user name and the password blank. 

esProc JDBC is a fully-embedded computing engine, enabling to perform all computations in its embedded package, so the database URL is local. But as an incomplete database, it has neither a user name nor a password.

Having finished setting the above information, click on “Test Connection” to check if the connection has been successfully established.

1.3. DeployesProc script

Copy the esProc script (dfx) to the dfxPath configured in config.xml. Suppose to query the closing stock prices of a certain month, the script stocks.dfx is as follows: 

1.4.  Invocation in BIRT

Create a new data set by selecting the datasource just configured. The data set type is stored procedure. 

Then define a query text - {call stocks(?)}. The way of invoking an esProc script is similar to that of invoking a stored procedure. “stocks” is the dfx’s name and “?” represents a passed parameter, which is the month being queried. 

Set the parameter. 

Click on Preview Results to see the query results. At this point, the invocation of esProc script has been completed. 

The above is the process of integration of esProc and BIRT development environment. Now let’s look at how to deploy both of them in the WEB server-side.

2. Deploy web server-side

Select the appropriate application server. Here Tomcat6.0 is used as an example to deploy the application.

2.1. Prepare web distribution package

Download BIRT4.3.0 distribution package birt-runtime-4_3_0.zip. The link ishttp://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 Tomcat’s webapps directory and rename it birt.

2.2. Create configuration files

Two configuration files – config.xml and dfxConfig.xml – are needed. The configuration information is the same as that for integration in development environment.

2.3. Copy necessary jars

Copy dm.jar,poi-3.7-20101029.jar,log4j_128.jar,icu4j_3_4_5.jaranddom4j-1.6.1.jarnecessary for the above “integration in development environment” to the application’s WEB-INF\lib directory and compress the above two configuration files into dm.jar.

2.4. Publish the report

When the report (likestocks.rptdesign) is completed, copy it to the application’s root directory, launch Tomcat and access the website http://127.0.0.1:8080/birt/frameset?report=stocks.rptdesign where we can see the result as displayed below. Now we can call it a successful deployment.