esProc provides standard JDBC to integrate with BIRT and other reporting tools. Here we’ll explain the process of integration of esProc and 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>ISO-8859-1</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’s classpath 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-dd HH: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=”iso-8859-1″?>
<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.
Three basic jars – dm.jar, icu4j_3_4_5.jar and dom4j-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. About the jars for activating other esProc functions (like accessing Office files), see esPrc Integration & Application: Deploying JDBC.
Place the two configuration files – config.xml and dfxConfig.xml – created in the first step in the classpath’s root path (no function of setting a directory-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). config.xml is allowed to be renamed esprocJDBCconfig.xml in case there are already config.xml files for other purposes under classpath. esProc JDBC driver will find and use esprocJDBCconfig.xml first in preference to config.xml, which will be used when the former does not exist. Names of these configuration files’ names should be remain what they are specified here and must not be changed.
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 all computations to be performed in its embedded package, so the database URL is local. But as an incomplete database, it has neither a user name nor a password.
esProc amounts to a database without the storage mechanism. The JNDI connection pool similar to that used with other databases can also be used when BIRT is connecting to the esProc. Configurations vary according to different types of WEB container. For example the configuring of JNDI connection pool under Tomcat requires that Tomcat’s JNDI URL be entered into the Edit Data Source window, as java:comp/env/demo shown in the following figure:
When creating a report in the Integrated Development Environment (IDE), we did the test that a database is connected through JDBC. Under running environments like Tomcat, the convenient JNDI connection pool is employed for application development.
Note: About the configuration of connecting esProc to a database, see esProc Integration & Application: Deploying JDBC.
Having finished setting the above information, click on “Test Connection” to check if the connection has been successfully established.
1.3 Deploy esProc 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:
| A | |
| 1 | =connect(“demo”) |
| 2 | =A1.query(“select * from stockrecords where year(date)+’-’month(date)=?”,arg1) |
| 3 | >A1.close() |
| 4 | Result A2 |
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 is 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 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, icu4j_3_4_5.jar and dom4j-1.6.1.jar necessary 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 (like stocks.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.
