esProc JDBC’s Integration and Application Method

esProc JDBC is like an incomplete database JDBC driver without physical tables. It regards esProc as a database only having stored procedures (strong computing power and weak storage mechanism). Similar to the use of database JDBC, esProc JDBC calls esProc program as it calls stored procedures. Their difference is that esProc JDBC is a completely embedded computing engine. All computations are completed in the embedded package rather than by an independent server like in the databases.

1. Loading driver

Jars need by esProc JDBC include dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar. The five jarscan be obtained in\esProc\lib in esProc’s IDE installation directory. Load these jars when starting java application; they can be put in WEB-INF/lib directory for a web application.
Note that esProc JDBC requires JDK1.6 or higher versions.

2 . Modifying configuration files config.xml and dfxConfig.xml

Prepare file config.xml which contains esProc’s basic configuration information, such as registration code, addressing path, main pathand data source configuration. The file can be found in the directory esProc\config in esProc's installation directory, and its configuration can be modified before deployment (for detailed explanation of configuration information, please see Appendix).

Configuring authorized information

Configure as follows in file config.xml:

<regCode> license</regCode>

license represents authorization code. Now esProc provides users with free distributions for integration. Free authorization codes are available in the official website.

Save config.xml and dfxConfig.xml in classpath of application projects.

Here we should note that names of the configuration files must beconfig.xml and dfxConfig.ximl, and cannot be changed;reentering is forbidden and esProc JDBC itself cannot be used as a data source and configured as a database connectionduring configuring database connection information.

3.  Deploying esProc program

Put the pre-edited esProc script (dfx file) in classpath of the application project, or put it in the path designated by <paths/> node of dfxConfig.xml file.

4.  Java's calling of esProc program

We'll look at situations when esProc program returns a single result set and when it returns multiple result sets.

4.1 =Single result set

esProc script

Java's calling

public class CallSingleRS {

    publicvoidtestDataServer(){

        Connection con = null;

    com.esproc.jdbc.InternalCStatementst;

    try{

    //create a connection

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

    con= (Connection) DriverManager.getConnection("jdbc:esproc:local://");

    //call the stored procedure,dfxfile’s name is singleRs

    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call singleRs(?)");

    //set parameters

    st.setObject(1,"5");

    //execute the stored procedure

    st.execute();

    //get result set

    ResultSetrs = (ResultSet) st.getResultSet();

    }

    catch(Exception e){

    System.out.println(e);

        }

    finally{

    //close the connection

    if (con!=null) {

    try {

    con.close();

            }

    catch(Exception e) {

    System.out.println(e);

            }

          }

        }

    }

}

4.2  Multiple result sets

esProc script

Java's calling

Here the main code will be provided and the rest is the same as that in the previous example.

//call the stored procedure

st =( com. esproc.jdbc.InternalCStatement)con.prepareCall("call MutipleRs()");

//execute the stored procedure

boolean hasResult = st.execute();

//if the execution returns result sets

if (hasResult) {

    //get multiple result sets

ResultSet set = st.getResultSet();

intcsize = set.getMetaData().getColumnCount();

    // the multiple result sets are data rows of one column, csize is 1

while (set.next()) {

      Object o = set.getObject(1);

      //in this example, one table sequence can be read out each time and retrieve table sequences respectively in A2 and A3

    }

} 

【Appendix】 Explanation of Configuration Information

config.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<ConfigVersion="1">

    <regCode>W2r@7-8cLMJ-GVU33-BhTIB3E</regCode>

    <charSet>ISO-8859-1</charSet>

    <!--Configure addressing path of dfx file, which is an absolute path. We can set multiple paths and separate them from each other by semicolons. The path of dfx file can also be put in classpath of application projects, and the classpath takes precedence over addressing path in loading files -->   

<dfxPathList>

        <dfxPath>D:\files\dfx</dfxPath>

    </dfxPathList>

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

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

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

    <!--Configuration method one: configure connection pool in the application server and designate data source name here

--> <DBList>

        <!--The data source name must in consistent with that in dfx file -->

        <DBname="demo">

            <propertyname="url"value="jdbc:hsqldb:hsql://127.0.0.1/demo"/>

            <propertyname="driver"value="org.hsqldb.jdbcDriver"/>

            <propertyname="type"value="HSQL"/>

            <propertyname="user"value="sa"/>

            <propertyname="password"value=""/>

            <propertyname="batchSize"value="1000"/>

            <!--Automatically connect or not. If the setting is true, db.query() function can be directly used to access the database; if it is false,  the connection won’t be automatic and connect(db) statement must be used first for the connection-->

            <propertyname="autoConnect"value="true"/>

            <propertyname="useSchema"value="false"/>

            <propertyname="addTilde"value="false"/>

        </DB>

    </DBList>

    <mainPath>D:\tools\raqsoft\main</mainPath>

    <tempPath>D:\tools\raqsoft\main\temp</tempPath>

    <bufSize>65536</bufSize>

    <localHost>192.168.0.86</localHost>

    <localPort>8282</localPort>

</Config>

dfxConfig.xml

<?xmlversion="1.0"encoding=" UTF-8"?>

<dfxConfig>

    <!--Maximum concurrent jobs.Set the maximum jobs allowed to concur; jobsoperating simultaneously in the same connection should also be dealt with as concurrent jobs. The maximum concurrent jobs should be within the authorized limit.-->

    <maxConcurrents>10</maxConcurrents>

    <!--The longest wait time.If tasks in operation are more thanthe maximum concurrent, the extra tasks will be in a waiting state; if the wait time reaches its limit, abnormitymessage will be shown.-->

    <maxWaitTime>5000

</maxWaitTime>

    <!--Logs configure property files-->

    <log>logger.properties

</log>

    <!--Configure connection pool in the application server and designate data source name here -->

    <jndi-ds-configs>

        <!—jndi-prefix-->

        <jndi-prefix>java:comp/env</jndi-prefix>

        <!--Data source name must be in consistent with that in dfx files -->

        <jndi-ds-config>

            <name>olap</name>

            <dbType>ACCESS</dbType>

            <dbCharset>ISO-8859-1</dbCharset>

            <clientCharset>ISO-8859-1</clientCharset>

            <needTranContent>false</needTranContent>

            <needTranSentence>false</needTranSentence>

            <!--Automatically connect or not. If the setting is true, db.query() function can be directly used to access the database; if it is false,  the connection won’t be automatic and connect(db) statement must be used first for the connection-->

            <autoConnect>true</autoConnect>

        </jndi-ds-config>

    </jndi-ds-configs>

</dfxConfig>

Code Examples of Accessing HTTP Data in esProc

esProc can access data conveniently in http data source for processing. Now we'll look at some functions through an example.
In this example, a servlet provides outward query of employee information in json format. Servlet accesses employee table in the database and saves employee information as follows:

doGet function of servlet receives employee id strings of json format, queries corresponding employee information through the database and generates employee information list in json format and then returns it. Process of reading the database and generating employee information is omitted in the following code: protected void doGet(HttpServletRequestreq, HttpServletResponseresp) throws Servlet Exception, IOException {
// TODO Auto-generated method stub
String inputString=(String) req.getParameter("input");
//inputString  input value is："[{EID:8},{EID:32},{EID:44}]";
if (inputString==null) inputString="";
String outputString ="";

{...}//code for querying the database through inputString and generating outputSring is omitted here
  // the generated outputString
//"[{EID:8,NAME:"Megan",SURNAME:"Wilson",GENDER:"F",STATE:\...";
resp.getOutputStream().println(outputString);
resp.setContentType("text/json; charset=GBK");
}

The following code can be used for esProc to access this http servlet:

A1: Define the input parameter to be submitted to servlet, i.e. the employeeid list in json format.

A2: Define httpfile objects, URL is http://localhost:8080/demo/testServlet?input=[{EID:8},{EID:32},{EID:44}].

A3: Import A2, the result returned by http file objects.

A4: Parse by rows the json format information of each employee, and create a sequence.

A5: Compute on the table sequence in A4 and combine SURNAME and NAME into FULLNAME.

A6: Export results of A5 to a text file. 

Parallelism of esProc enhances Oracle Data Import Speed

Recently, we skillfully handled an industry project to import a great amount of data from file to Oracle in comparatively short time.

At the beginning, we tried to import the data with the sqlldr of Oracle, only to find it is surprisingly time-consuming to load a great amount of data: Too slow if 2.5 hours have to be spent to import a table containing 80 million records.
Later, by adopting the parallel import of sqlldr, the time is shortened to 0.8 hour. Here are the full details:

Train of thoughts
Split the data file to import into 10 shares. Then, with the multi-task parallelism, execute the sqlldr command for the corresponding shares. Needless to say, prepare the control files of the same amount. Then, multiple clients will start to import data to database all at the same time.

Please note these two things: 1. The way to generate multiple sqlldr commands and corresponding number of control files - a bit tried if writing them one by one; 2, The way to perform parallelly - ever more tired if performing one by one.
In this case, we use the tool, esProc by name, to generate the commands and control files automatically and then run parallelly.

Implementation steps

Main program
Responsible for task control, task distribution, and calling sub-program.

Subprogram

Generate the specific control file and sqlldr command, and execute the import command to complete the data loading

Note: In this case, the parallelism feature of esProc is used to execute multiple sqlldr commands; The function system is used to call the system commands.

Actual result

Because of the programmable-controlled parallel tasks, the number of parallel tasks can be set as necessary to tap into the machine’s full performance potential.

The below figure illustrates the sqlldr import speed for different degree of parallelism - linear increasing on the whole - the more parallel tasks, the faster the import would be.

How to Integrate esProc with BIRT

esProc provides the standard JDBC to integrate with reporting tool. Let's take the BIRT for example to demonstrate the integration procedure.

1.  Integrate in IDE1.1 Create profile

Create two configuration files of config.xml and dfxConfig.xml required by 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 the addressing path for dfx file. This path is the absolute path and multiple paths can be set and separated with “;”. The path to the dfx file can also be put on the class path of application project. The file in this path will be loaded prior to that in the addressing path -->

<dfxPathList>

<dfxPath>D:/dfxs</dfxPath>

</dfxPathList>

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

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

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

         <!-- Data source configuration method 1: Configure the connection pool on the application server. Specify the data source name here -->

<DBList>

         <!-- Data source name, which must be consistent with the data source name in the dfx file. The hsql database is connected here -->

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

Create a new report. Under the DataSources, create a new data source of JDBC Data Source type, and proceed to the next step.

In the new data source window, click Manage Drivers and add the driver for esProc JDBC.

 We need to add five jar packages of dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar, and dom4j-1.6.1.jar. These jar can be obtained from the esProc IDE[installation directory]\esProc\lib directory. If the esProc program also takes other database as the data source, then the corresponding database driver needs to be added. As an example, hsqldb.jar is added in this case.

The two configuration files config.xml and dfxConfig.xml created in the first step must be put under the root path of classpath. For esProc, no function is available to set classpath witha directory. The configuration files can be compressed to the root directory of any above-mentioned jar, such asdm.jar, and the name must be left unchanged.

After the connection test, select the driver class name com.esproc.jdbc.InternalDriver (v1.0) in the Driver Class. Fill the database URL jdbc:esproc:local://, and leave the user name and password blank.

esProc JDBC is a completely embedded computing engine. All computing are completed in the embedded computing engine. So the URL is local, and it is not the complete database because there is no user name and password.

Once filled, click Test Connection till the data source connection test succeed.

1.3  Deploy esProc scripts

Copy the esProc script (dfx) to dfxPath configured in the config.xml. Take the stocks.dfx for example, query the closing price of stock in a certain month. 

1.4  BIRT calling

Create a new Data Sets, select the just configured data source, and then select the stored procedure as data set type. 

In the next step, fill in the query statement {call stocks(?)}. For the esProc scripts, the stored-procedure-like calling method is adopted. In this case, the "stocks" is the dfx name, and "?" is the parameter to pass, which is the month of query in this example. 

Set parameters: 

 The calling succeeds if the query result can be viewed in the data set preview.

2. Deloy on Web side

Let's take Tomcat6.0 as an example to demonstrate the application.

2.1 Prepare Web release package

Download BIRT4.3.0 release package birt-runtime-4_3_0.zip at: 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 the webappsdirectory of Tomcat, and rename it as birt.

2.2  Create configuration file

Prepare two configuration files of config.xml and dfxConfig.xml, as introduced in the above-mentioned section “Integrate in IDE”.

2.3  Copy the relevant jar package

Copy the required files mentioned in above section “Integrate in IDE”: dm.jar, poi-3.7-20101029.jar,log4j_128.jar,icu4j_3_4_5.jar and dom4j-1.6.1.jarto the WEB-INF\lib of application. Compress the two configuration files to dm.jar.

2.4 Publish report

Copy stocks.rptdesign or other developed resulting reports to the root directory of application. Start Tomcat and visit http://localhost:8080/birt/frameset?report=stocks.rptdesign. The deployment succeed if the below contents appear.