How to Call an SPL Script in BIRT
esProc provides its own JDBC driver to become integration-friendly with a reporting tool. Here we take the development environment of BIRT4.6.0 as an example to explain how to call SPL in BIRT.
Put simply, to integrate esProc JDBC with BIRT report designer is to put the necessary jars and configuration files for loading esProc JDBC in BIRT’s corresponding directory. esProc JDBC requires JDK 1.8 or a higher version.
esProc JDBC is like a database JDBC driver without physical tables. It can be regarded simply as a database that only supports the stored procedure. It is a stand-alone computing engine that can perform all calculations independently. A database’s JDBC, however, functions only as an interface and an extra database server is needed to perform calculations.
esProc JDBC has two basic jars, which are all situated in [installation directory]\esProc\lib:
esproc-bin-xxxx.jar esProc computing engine and JDBC driver
icu4j-60.3.jar Handle internationalizationCopy these jars into BIRT’s [installation directory]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212\driver. The driver directory has slightly different higher directory names in different versions of BIRT.
Besides, there are jars for realizing specific functionalities:
To use databases as the data sources in esProc JDBC, their driver jars are required.
To read and write an Office file, poi*.jar and xmlbeans-4.0.0.jar are needed.
To use the graphic function in esProc JDBC, jars for SVG-typed image processing are required, including batik*.jar, js-1.0.jar, pdf-transcoder-1.0.jar, xalan-2.7.0.jar, xercesImpl-2.12.1.jar, xml-apis-xerces-2.7.1.jar and xml-apis-ext-2.5.0.jar.
raqsoftConfig.xml is an important configuration file for deploying esProc JDBC, which is located in [installation directory]\esProc\config. It contains esProc main path, dfx file’s search path, and other information. It should be copied and placed under BIRT’s _[_installation directory]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212\driver.
Its name must not be changed.
Here is the process of calling SPL in BIRT. esProc JDBC is the data source over which SQL-like SPL queries are executed and return a result set. Then the reporting tool calls the result set to build a report.
Create a new report and add JDBC Data Source type under DataSources and name the new data source esprocJDBC.
Click Next button to edit the data source. Select com.esproc.jdbc.InternalDriver ( v1.0) after Driver Class, and enter jdbc:esproc:local:// after Database URL. Leave both user name and password blank.
Click Test Connection button and, if the prompt shows Connection successful!, the data source is connected. Then Click OK to finish the data source creation.
Create a new data set on New Data Set window. Select the esProcJDBC data source. The data set type is SQL Select Query and data set name is SPLData.
Click Next to enter the esProc SPL query.
Create a data table with two fields – baseNum and square2, insert 100 records made up of natural numbers less than and equal to 100 and their squares into it, and return the table as result set. The SPL query is: =100.new(:baseNum,*~:square2), like the following shows:
Click Preview Results on the left to preview the data set:
Click OK to finish data source creation.
Call the data set from the reporting tool:
View the report on the web:
You can access a local file from SPL. esProc supports multiple file types, such as TXT, Excel, JSON, CSV and CTX. Both absolute path and relative path can be used to find a file. A relative path is relative to the main directory set in the configuration file. First, let’s look at how to configure the main directory.
Add the following node under < Esproc></ Esproc > in raqsoftConfig.xml:
<!--esProc main directory, which is an absolute path -->
<mainPath>D:\mainFile</mainPath>Put a to-be-called file, like employee.txt, into the main directory. See the above for how to create a new report and add data source. Here the query is:
=file("employee.txt").import@t()
Preview the result set:
As the calculation is simple, you can also use esProc simple SQL syntax to do it:
$()select * from employee.txt
The symbol $() means accessing a local file system. The two queries get same result.
Parameters are an important part of a SQL statement. So are they in SPL statements. To query records in employee.txt according to the condition that salary is within a range of [12000, 20000] and sort them by salary in ascending order.
Add two parameters param1 and param2 and set their data types as Integer:
Create the data set. The data set type is SQL Stored Procedure Query. The query is:
$()select * from employee.txt where SALARY > ? and SALARY< ? order by SALARY
Configure parameters for the data set:
Preview the data set, where the parameters are by default those set in the above:
Call the data set from the reporting tool. Configure the parameters before viewing the finished report on the web:
Here’s the finished report:
As a computing engine, one of the most important data sources of esProc JDBC is the database. Let’s look at how to call a SPL statement that connects to the database.
First, put in place corresponding database driver jars; then set data source information in raqsoftConfig.xml. Suppose the data source name used in an SPL statement is demo and the database type is HSQL, then you can configure it like this:
First, put HSQL database driver jar hsqldb-2.2.8.jar under BIRT’s [installation directory]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212\driver;
Second, set data source information in the node < Runtime>< /Runtime> in raqsoftConfig.xml.
<DBList>
<DB name="demo"> <!—data source name-->
<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" /> <!—url connection-->
<property name="driver" value="org.hsqldb.jdbcDriver" /> <!—database driver-->
<property name="type" value="13" /> <!—database type-->
<property name="user" value="sa" /> <!—user name-->
<property name="password" /> <!--password-->
<property name="batchSize" value="1000" />
<property name="autoConnect" value="true" /><!--Automatically connect or not. If the value is true, use a SQL statement starting with $ to access the database; if it is false, there will be no automatic connection; establish the connection with connect(db) statement before you can access the database -->
<property name="useSchema" value="false" />
<property name="addTilde" value="false" />
<property name="dbCharset" value="UTF-8" />
<property name="clientCharset" value="UTF-8" />
<property name="needTransContent" value="false" />
<property name="needTransSentence" value="false" />
<property name="caseSentence" value="false" />
</DB>
</DBList>Now we’ll query SALES table from demo in SPL to find records whose SELLERID is 3 and ORDERDATE is from 2014-11-01 to 2014-12-12:
Set three parameters:
Create the data set. The data set type is SQL Stored Procedure Query. The query is:
$(demo)select * from SALES where SELLERID = ? and ORDERDATE>? and ORDERDATE<?
Configure parameters for the data set:
Preview the data set:
Call the data set from the reporting tool. Configure the parameters before viewing the finished report on the web:
Here’s the final report:
By integrating esProc JDBC, BIRT can call and execute a single SPL statement as well as the complicated SPL script (whose extension is dfx).
| A | B | C | |
|---|---|---|---|
| 1 | =demo.query("select NAME as CITY, STATEID as STATE from CITIES") | [] | |
| 2 | for A1 | =demo.query("select * from STATES where STATEID=?",A2.STATE) | |
| 3 | if left(B2.ABBR,1)==arg1 | >A2.STATE=B2.NAME | |
| 4 | >B1=B1 | ||
| 5 | return B1 |
Explanation:
There are two tables – CITIES and STATES. Traverse records in CITIES and find corresponding STATES record by CITIES. STATES, during which if the first letter of STATES.ABBR is equal to parameter arg1, then assign STATES.NAME to CITIES.STATE, and add this record in CITIES to B1. The final result set returned is B1’s value.
The above cellset file receives data from the data source demo and uses parameter arg1:
See the above instances for the data source configuration. The cellset file is saved as city.dfx. A dfx file can be saved in the BIRT’s class path or in the main directory set in raqsoftConfig.xml. If there are many dfx files, we can put them in the dfx’s search path for easy management and maintenance. Here’s how to configure a search path:
Add the following content under < Esproc> <\Esproc> in raqsoftConfig.xml:
<dfxPathList>
<dfxPath>D:\dfxFile</dfxPath>
<!--Configure dfx file’s search path, which is an absolute path; multiple paths are allowed and separated by the semicolon -->
</dfxPathList>Create a report parameter:
Create the data set. The data set type is SQL Stored Procedure Query. The query is:{call city(?)}
Configure a parameter for the data set:
Preview the data set:
Call the data set from the reporting tool. Configure the parameters before viewing the finished report on the web:
Here’s the final report:
