Data Process Engine: application

Showing posts with label application. Show all posts

2/17/2015

esProc Integration & Application: Integration with JasperReport

esProc provides standard JDBC interface to be integrated easily with the reporting tool. This article will explain respectively the integration of esProc and JasperReport in development environment and the deployment in WEB server-side.

1. Integration in development environment

Access esProc JDBC to call the esProc script using JasperReport iReport Designer through the following steps:

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"?>

</dfxPathList>

</DB></DBList>

<logLevel>DEBUG</logLevel>

</Config>

1.1.2. dfxConfig.xml

<?xml version="1.0" encoding="GBK"?>

</dfxConfig>

1.1.3.JAR Load driver jars

esProc JDBC driver includes five jars - dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar. 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, mysql-connector-java-5.1.5-bin.jar has been added here. Place all these jars in the classpath’s root path.

Except for these jars, the directory (such as D:\lib\config) of the two configuration files – config.xml and dfxConfig.xml – created in the first step needs to be placed in the classpath’s root path too. Or compress the configuration files into the root directory of any of the jars above, with their names remaining strictly the same.

Configure the classpath on the Tool Option bar, as shown in the following figure:

1.2 Create a datasource

Create a new datasource – esproc – on the datasource configuration window. Select the JDBC driver com.esproc.jdbc.InternalDriver and enter the JDBC URL as jdbc:esproc:local://. Leave the username and password blank.

esProc JDBC is a fully embedded computing engine. All computations are performed in its embedded package, thus the URL is local. On the other hand, since it isn’t a complete database, it has neither the username nor the password.

After filling all necessary information, click on “Test” to see whether the connection to the datasource has been successfully established.

1.3 Deploy esProc script

Copy the esProc script (dfx) to dfxPath configured in config.xml (such as D:/dfx configured in the above). Here we’ll take the query of the closing stock price in a certain month as an example. The esProc script is as follows:

1.4 Call esProc script in IDE

Create a report and set the query

Create a new report – esProcReport, write the query statement and call the esProc script using call dfxName(), in which dfx name is stocks.

Select all the fields and execute the code by clicking on Next Step to the end.

Because the esProc script needs to receive the parameter representing the year and the month, it is necessary to set the parameter for use in the query for the report after it is created. In the following figure, add the parameter representing the year and the month and edit the query for the report.

Then drag the parameter to the specified position of the query statement, thus the query becomes as follows:

Edit the report

Drag all fields under Fields to the report’s detail band and complete the edition, as shown below:

Preview the report

Click on Preview and enter the parameter to see the query result:

This is the integration of esProc script and JasperReport in the development environment. Next we’ll move on to explain how to deploy them in the web server-side.

2. Deploy web server-side

Select a suitable application server. For this purpose, Tomcat6.0 is used here for illustration.

2.1 create a web application

Create a J2EE application, which is named, say, JasperReport, and copy it to Tomcat’s webapps directory.

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 Deploy necessary jars

JasperReport’s driver jar

Deploy jars necessary for JasperReport into the application’s WEB-INF\lib directory. The JasperReport jar can be found in ireport\modules\ext directory of iReport Designer’s installation directory.

esProc JDBC’s driver jar

Copy the jars - dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar - used in the above integration in the development environment and MYSQL’s driver jar - mysql-connector-java-5.1.5-bin.jar – to the application’s WEB-INF\lib directory. Then copy config.xml and dfxConfig.xml to the application’s WEB-INF\classes directory.

2.4 Copy the report

Copy the completed report (whose file extension is .jasper) to the corresponding directory of the application. For example, copy esProcReport.jasper to the application’s reportFiles directory.

2.5 Define and Publish the JSP report

The following code is to define and publish the JSP report, such as showReport.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<%@ page

import="java.io.*,

net.sf.jasperreports.engine.*,

net.sf.jasperreports.engine.util.*,

java.util.*,java.sql.*,

net.sf.jasperreports.engine.export.*"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Show JasperReport</title>

</head>

<body>

String report = request.getParameter("report");

File reportFile = new File(application.getRealPath("reportFiles/"

+ report));

//Load report object

JasperReport jasperReport = (JasperReport) JRLoader

.loadObject(reportFile.getPath());

//Pass report parameter through Map

Map parameters = new HashMap();

//Assign value to parameter p1 of the report

parameters.put("p1", 200901);

//Connect to esProc

Class.forName("com.esproc.jdbc.InternalDriver");

Connection conn = DriverManager.getConnection(

"jdbc:esproc:local://", "", "");

//Export the report to the page

JasperPrint jasperPrint = JasperFillManager.fillReport(

jasperReport, parameters, conn);

JRHtmlExporter exporter = new JRHtmlExporter();

exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);

exporter.setParameter(

JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN,

Boolean.FALSE);

exporter.exportReport();

out.flush();

conn.close();

</body>

</html>

2.6 Preview the report

Launch Tomcat and access the website http://127.0.0.1:8080/JasperReport/reportJsp/showReport.jsp?report=esProcReport.jasper to preview the report as shown below, which means the deployment is completed.

1/14/2015

esProc Integration & Application: User-defined Functions

Besides the system-provided functions, esProc supports invoking user-defined functions to handle some special operations or the encapsulation of certain computations. This article will briefly explain how to invoke user-defined functions with invoke function and how to use parameters and return the results as needed.

1. Basic method of invoking user-defined functions

invoke function can be used to invoke static method(s) in a specified user-defined Java class. Take the following Java class test.Calc01 as an example:

package test;

public class Calc01 {

public static Double distance1(Number loc) {

double len = Math.abs(loc.doubleValue());

len = Math.round(len*1000)/1000d;

return Double.valueOf(len);

}

In this simple class, the static method distance1 computes the distance between a given coordinate and the origin, with the value being rounded to three decimal places. The methods being invoked in esProc must be both static and public. Before invocation, the class of user-defined functions needs to be placed in esProc’s classpath, [installation directory]\esProc\classes path; if invoked in the web server-side, it needs to be placed in WEB-INF/classes path correspondingly. After that, the user-defined functions can be invoked by invoke function in the cellset:

As shown in this example, when using invoke function, first specify the full path of the class being invoked and the static method name and then list the necessary parameters in order. The parameters can be cell values in a cellset and cellset parameters, or can be typed in. After computation, results of A2 and A3 are as follows:

There can be multiple user-defined functions in a single class. Now add another method:

public static Double distance2(Number loc1, Number loc2) {

double len = Math.abs(loc1.doubleValue()-loc2.doubleValue());

len = Math.round(len*1000)/1000d;

return Double.valueOf(len);

}

The newly-added distance2 computes the distance between two coordinates in a number axis. As esProc invokes methods by their names, different user-defined functions need to have different method names. Once the class(es) has been place in the application’s classpath, the user-defined functions can be invoked in the same cellset, no matter they are from a single class or multiple classes:

In the invoke function, the number and type of parameters need to match the methods being invoked. Results of A1 and A2 are as follows:

2. Return results as needed

The user-defined functions may or may not return results. For example:

public static void distance3(Number loc1, Number loc2) {

double len = Math.abs(loc1.doubleValue()-loc2.doubleValue());

len = Math.round(len*1000)/1000d;

System.out.println(Double.toString(len));

}

When the computation with static method distance3 is completed, the output data will be handled with print spooling and won’t be returned. Such functions are similar to the executable cells in a cellset, in which invoke function can start with >. For example:

Now the output data can be viewed in the console. To access the console, click Tool>Console on the menu bar. The result is as follows:

But if the user-defined functions return results, they need to use data types supported in esProc; otherwise errors may occur during presentation or invocation. Following is the correspondence between commonly-used data types in esProc and those in Java:

Integer java.lang.Integer

Long integer java.lang.Long

Floating point number java.lang.Double

Big decimal java.math.BigInteger

Real number java.lang.Number

Boolean java.lang.Boolean

String java.lang.String

Date java.sql.Date

Time java.sql.Time

Date/time java.sql.TimeStamp

Sequence com.raqsoft.dm.Sequence

Table sequence com.raqsoft.dm.Table

In the previous examples, all the returned data are Double object, which corresponds to the floating point number in esProc. User-defined function can return results as needed. For instance, add another two user-defined functions to test.Calc01 and return results of different data types:

public static String distance4(Number loc1, Number loc2) {

double len = Math.abs(loc1.doubleValue()-loc2.doubleValue());

len = Math.round(len*1000)/1000d;

return Double.toString(len);

}

public static Sequence distance5(Number loc1, Number loc2) {

double len = Math.abs(loc1.doubleValue()-loc2.doubleValue());

len = Math.round(len*1000)/1000d;

com.raqsoft.dm.Sequence result = new com.raqsoft.dm.Sequence();

result.add(loc1);

result.add(loc2);

result.add(Double.valueOf(len));

return result;

}

These two static methods are still used to compute the distance between two points in a number axis. distance4 returns the result of string type and distance5 returns the result in a form of a sequence in which the coordinates of the two points and the distance between them are stored. invoke function is still used to invoke the two methods in a cellset:

For the convenience of comparison, A1 calls distance2 used in the previous example. After the code is executed, results of A1~A3 are as follows:

Notice that the results of A1 and A2 use different data types and are displayed differently. The return results of user-defined functions can be used for later computation.

3.Parameters of sequence type

The parameters used in a user-defined function come from esProc. The correspondence between their data types and the Java objects is the same as that mentioned above. Note that the parameters used during the invocation of user-defined functions need to be consistent with the parameters in the static methods.

A special point worth noting is that the sequence is the most frequently used data type in esProc. Besides returning a sequence as the result, user-defined functions can also use parameters of sequence type. For example:

public static Double distance6(com.raqsoft.dm.Sequence seq1, com.raqsoft.dm.Sequence seq2) {

int len1 = seq1.length();

int len2 = seq2.length();

double x1 = len1 > 0 ? ((Number) seq1.get(1)).doubleValue(): 0;

double x2 = len2 > 0 ? ((Number) seq2.get(1)).doubleValue(): 0;

double y1 = len1 > 1 ? ((Number) seq1.get(2)).doubleValue(): 0;

double y2 = len2 > 1 ? ((Number) seq2.get(2)).doubleValue(): 0;

double len = Math.sqrt((x1-x2)*(x1-x2)+(y1-y2)*(y1-y2));

len = Math.round(len*1000)/1000d;

return Double.valueOf(len);

}

The user-defined function distance6 computes the distance between two points in a rectangular coordinate system. Coordinates of the two points need to be input using parameters of sequence type during invocation. For example:

After computation, result of A3 is as follows:

1/12/2015

esProc Integration & Application: Command Line

The command line refers to using commands in the form of strings in DOS-like environment to execute programs. All operating systems support the command lines, like the Linux console and the Windows command line window.

The file, esprocx.exe, is needed when the command line is used to execute an esProc cellset file. The file can be found in esProc\bin path in esProc’s installation directory.

1. Cellset files without return results

Use the esprocx command to execute the cellset file at command line. Take the following cellset file, D:\files\output1.dfx, as an example:

Cellset files without return results are usually used to access files or databases. In the above cellset, for example, some simulation data are created and stored in a text file, Data1.txt. In using an esprocx command, enter the to-be-executed dfx file after it; during execution, prompts of start and end will be displayed on the screen, as shown below:

After the execution, the randomly generated data will be written into Data1.txt, as shown below:

If the path in which the esprocx file is located is included into the operating system’s path list, run the file directly instead of running it in esProc\bin path of the installation directory. But the to-be-called dfx file must be entered in its full path, or should be located in the current path. For example:

If the running cellset file uses parameters, enter their values, separated by spaces, after the dfx file when executing esprocx. Take the following cellset file D:\files\output2.dfx as an example:

The file uses two parameters: IDs and Value, as shown below:

The cellset is to import the Data1.txt file just created, modify some of the records with specified sequence numbers so as to make their Amount fields are equal to the specified Value and store them in the file again. The prompts displayed on the screen during execution are as follows:

Two parameters, one is a sequence, [2,3,5,7,11] and the other is an integer, 99999, are input while the cellset file, output2.dfx, is called. Similar to a cross-cellset call, the input parameters will assign value to the cellset parameters according to their orders instead of their names during the command line call. In this case [2,3,5,7,11] assigns value to the first parameter, IDs and 99999 assigns value to the second parameter, Value. After the command line is executed, data in Data1.txt will have been modified:

2. Cellset files with return results

If the esprocx command is used directly at the command line, the result won’t be displayed on the screen by default. If the return result of the dfx file is to be displayed, -R option needs to be used in execution. Similar to the cellset file called by the call function, the dfx file must return the result using result statement. Take the following cellset file, D:\files\calculate1.dfx, as an example:

This cellset will return the total number of days of the month in which the specified date occurs and compute what day the specified date is. It uses Day as one of its parameters:

Let’s first look at the execution with -R option:

Here no parameters have been set during the execution and a default parameter, 01/01/2000, is used for the computation. You can see that A2 and B2 respectively return a result using result statement. The results will be listed respectively after the computation is finished.

If a parameter is to be set, its data type should be date. As a parameter of date or time type should be in some kind of format, you need to set its format in the configuration file, config.xml, which is located in esProc\config path in esProc’s installation directory:

The execution is what the following shows if a parameter is set for the call:

At this point, you get the total number of days of the month in which February 22, 2012 occurs and what day this date is.

Different from the previous cellset file, the following one, D:\files\calculate2.dfx, returns the results as a sequence:

The execution is as follows:

It can be seen that members of the resulting sequence are listed respectively, each having a row.

Here comes the last sample of the cellset file - D:\files\calculate3.dfx:

The cellset file uses a parameter - Letter:

This cellset uses the database data. In this case the database connection needs to be configured in config.xml in the esProc\config path in the esProc installation directory:

</DB>

</DBList >

Please refer to related documents for detailed configuration method.

When the program is executed, the result is as follows:

To set a parameter of string type, it’s not necessary to surround it with double quotation marks. Cities whose names start with B are listed in the above result. For the result of the type of a table sequence or a record sequence, each record is a row and fields are separated from each other by tabs.

The above configuration file, config.xml, is also used by the Integration Development Environment (IDE), so information, like date format and datasource configuration, can be edited on the IDE interface. Click Tool->Option on the menu bar and configure the information on the Environment of Option window:

Click Tool->Datasource connection and configure information, like the database connection parameter, in the datasource manager. For detailed configuration method, please refer to esProcAccesses Databases: Database Configuration.