esProc provides its own JDBC driver to become integration-friendly with a reporting tool. Here we take the development environment of JasperSoft Studio 6.19.1 as an example to explain how to call SPL in JasperReport.

Integrating esProc JDBC

Put simply, to integrate esProc JDBC with JasperReport’s iReport designer is to add the necessary jars and configuration files for loading esProc JDBC to JasperReport’s JDBC data source. Note that esProc JDBC requires JDK 1.8 or a higher version.

1. Create the configuration file

       Create the configuration file raqsoftConfig.xml required by esProc JDBC in [installation directory]\esProc\config. The file’s name must not be changed.

The raqsoftConfig.xml file contains esProc main path, spfx file’s search path, and other information.

2. Load driver jars

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. Besides, it is a completely embedded computing engine that can perform all computations in JDBC. A database’s JDBC, however, functions only as an interface and an extra database server is needed to perform computations.

The third-party jar files used in the rest part can be found in [installation directory]\esProc\lib.

esProc JDBC requires two basic jars:

	esproc-bin-xxxx.jar   //esProc computing engine and JDBC driver
	icu4j*.jar	//For handling internationalization

Besides, there are jars for achieving specific functionalities:

To use databases as data sources in esProc JDBC, their driver jars are required.

To read and write Office files, poi*.jar and xmlbeans*.jar are needed.

To use the chart plotting functionality in esProc JDBC, jars for SVG-typed image processing are required, including batik*.jar and xml-apis*.jar.

Create SPL-jdbc directory in JasperSoft Studio’s installation directory and put the necessary jars listed above in the directory. Besides, compress the configuration file created in Step 1 into config.jar and put it to the SPL-jdbc directory.

Call SPL from JasperReport

The principle of calling SPL from JasperReport is to use esProc JDBC as the data source, take the SQL-like esProc SPL statement as a data set and execute it, and call the returned result set in the report.

Create a data source

Click File->New->Data Adapter and enter SPLJdbc.jrdax as the file name:

Click “Next” to have the following list to create the connection.

Click “Next” and enter SPLJdbc as the Adapter name. On the “Driver Classpath” tab, add jars located in SPL-jdbc directory, as shown below:

On the “Database Location” tab, type in com.esproc.jdbc.InternalDriver as the driver class name and jdbc:esproc:local:// as JDBC URL, and leave username and password blank, as shown below:

Click “Test” and wait “Successful” message to pop up. Then click “Finish” to finish creating the data source in eProc JDBC.

Build a report

Let’s move on to look at how JasperReport executes different types of SPL query to build a report according to different query statements.

Execute SPL queries

For example, to create a data table with two fields – baseNum and square2, insert 100 records made up of natural numbers within 100 and their squares into it, and return data in the table as the result set. The SPL statement for performing the task is =100.new(~:baseNum,~*~:square2)

Click File->New->Jasper Report and select template Cherry:

Click “Next” and enter SPL1.jrxml as the file name, as shown below:

Click “Next” to select the two specified fields to the right, as shown below:

Click “Next” and then “Next” to finish building the report.

Click “Preview” and we can view the newly-created report:

Access a local file from SPL

We can access various types of local files from SPL, including 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 raqsoftConfig.xml. 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>C:\Program Files\TIBCO\Jaspersoft Studio-6.19.1\SPL-jdbc</mainPath>

Put a to-be-called file, like students_scores.txt, in the main directory. Code for creating a new report is the same as that explained in the above. Here the query is: =file("students_scores.txt").import@t()

Preview the report and we can get this:

For such a simple task, we can also use esProc simple SQL syntax to do it: $()select * from students_scores.txt
The symbol $() means accessing a local file system. The two queries get same result set.

Call SPL statements using parameters

Parameters are an important part of a SQL statement. So are they in SPL statements. To query data of students_scores.txt according to the condition that math score is within the range [75, 95] and sort them by math score in ascending order:

On the “Dataset and Query” dialog, add two parameters Parameter1 and Parameter2 and set their data types as integer. Their default values are 75 and 95. Drag the parameters to their appropriate positions in the query statement. The final query is: $()select * from students_scores.txt where Math > $P{Parameter1} and Math<$P{Parameter2}  order by Math

Enter parameter values (or the default values) and preview the report:

And we get the following report:

Call a SPL statement having the data source

As a computing engine, one of esProc JDBC’s most important data sources is the database. Let’s look at how to call a SPL statement that has the data source from JasperReport.

First, put in place corresponding database driver jars; then set data source information in raqsoftConfig.xml. Suppose the data source name used in a SPL statement is demo and the database type is HSQL, then you can make the following configurations:

First, put HSQL database driver jar hsqldb-2.7.3-jdk8.jar in SPL-jdbc directory. Open SPLJdbc.jrdax and add the jar to it:

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" />   <!--username-->
                <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:

Create a new report as the previous example shows. Then set three parameters – param1, param2 and param3, and drag them to their positions in the query. The final query is: $(demo)select * from  SALES  where  SELLERID = $P{param1} and  ORDERDATE>$P{param2} and ORDERDATE<$P{param3}

Enter parameter values and preview the report:

And get the query result:

Execute SPL script

After integrating esProc JDBC into JasperReport, we can directly execute a single SPL statement as well as calling a more complicated SPL script (whose extension is splx).

Here is a splx file:

	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|A2
5	return B1

Explanation:

Traverse records in CITIES and find corresponding STATES record through CITIES.STATES, during which when the first letter of STATES.ABBR is the value of parameter arg1, assign STATES.NAME to CITIES.STATE, and add this record in CITIES to B1, which returns the final result set.

The above cellset file receives data from the data source demo and uses parameter arg1:

See the above instances for the data source configurations. The cellset file is saved as city.splx. A splx file can be stored in the main directory set in raqsoftConfig.xml. If there are many splx files, we can put them in the splx’s search path for the convenience of management and maintenance. Below is how to configure a search path:

Add the following content under <esProc> <\esProc> in raqsoftConfig.xml:

            <splPathList>
                <splPath>C:\Program Files\TIBCO\Jaspersoft Studio-6.19.1\SPL-jdbc\splx</splPath>
            </splPathList>
            <!--Configure splx file’s search path, which is an absolute path; multiple paths are allowed and separated by the semicolon -->

Create a new report as the previous instances show. Then create a parameter, set its data type as String, and drag it to its position in the query statement. The final query is: call city($P{Parameter1})

Enter the parameter value and preview the report:

And get the query result: