How to Use esProc as the Class Library for Java

Encapsulated lots of functions for handling structured file computing, esProc can import text files with complex formats, implement cursor-style processing with big files and simplify multithreaded parallel processing. A Java application can pass in parameters, execute an esProc script the same way as it executes a database stored procedure and gets the result set through JDBC.

Below is the structure of integrating an esProc script and a Java application:

An example will tell you how a Java application integrates an esProc script.

The sOrder.txt file, separated by tabs, holds a set of order information. You need to do a conditional query using Java based on this file and return orders in a specified time period.

Below is a selection of sOrder.txt:

Step 1: Implement the algorithm in esProc IDE

A1: Import the file, using the default separator tab. @t means importing the first row as the column names.

A2: Execute the conditional query. startDate and endDate are parameters transferred from Java. Their values are dates, like 2010-01-01and 2010-12-31.

Step 2: Check computing result in esProc IDE

You can check the computing result by clicking A2.

Step 3: Integrate esProc script into Java

The Java main program can call the esProc script via JDBC, using the following code:

                    Class.forName(“com.esproc.jdbc.InternalDriver”);

                    con= DriverManager.getConnection(“jdbc:esproc:local://”);

                    // call esProc script (which is similar to the stored procedure); orderQuery is the name of the dfx file

                    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call orderQuery (?,?)”);

                    st.setObject(1,”2010-01-01″);

                    st.setObject(2,”2010-12-31″);

                    // Execute the script

                    st.execute();

                    // Get the result set

                    ResultSet rs = st.getResultSet();

                            ……

esProc will return the last calculation cell by default, or it will return a certain cell using the return statement. The returned result is a ResultSet object in accordance with JDBC standards. The method of calling an esProc script is the same as that of accessing a database. Programmers who are familiar with JDBC can master it quickly.

The above example illustrates the normal way of integrating an esProc script by Java. Now let’s move on to look at the special cases:

Simple script without a file

A simple esProc script can be written directly in the Java application, without creating a script file. Thus the code in the preceding example can be like this:

                   st = (com. esproc.jdbc.InternalCStatement)con.createStatement();

                   ResultSet rs1 = st.executeQuery(“=file(\”D:\\sOrder.txt\”).import@t()\n” + “=A1.select(OrderDate>=date(\”2010-01-01\”) && OrderDate<=date(\”2010-12-31\”))”);

You just need to use the carriage return “\n” to separate lines (and “\t” to separate columns).

You can also use the prepareStatementment object to execute the script, forcing a parameter type conversion. The SQL prepareStatementment object uses the quotation mark as the parameter placeholder. But as the quotation mark is a part of an esProc expression, you need to use the form of “arg1,arg2,arg3” to hold places sequentially. Here’s the code:

                   st= (com. esproc.jdbc.InternalCStatement)con.prepareStatement(“=file(\”D:\\sOrder.txt\”).import@t()\n” + “=A1.select(OrderDate>=arg1 && OrderDate<=arg2)”);

                   java.util.Date  dateBegin  =  new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-01-01”);    

                   java.sql.Date  sqlDateBegin  =  new java.sql.Date(dateBegin.getTime());

                   java.util.Date  dateEnd  =  new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-12-31”);    

                   java.sql.Date  sqlDateEnd  =  new java.sql.Date(dateEnd  .getTime());

                   st.setDate(1, sqlDateBegin); 

                   st.setDate(2, sqlDateEnd ); 

                   ResultSet rs1 = st.executeQuery(); 

Big result set

Sometimes the computing result is too big to be held in the memory. In that case you can use an esProc cursor function to return it, and use JDBC flow to access Java. For example, to query the big file sOrderBig.txt by a certain time period, you could use the following esProc code:

cursor function opens the file with cursor and select function returns the query result as a cursor.

The following is the code of integrating esProc script by Java:

           st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call orderBigQuery (?,?)”);

           st.setObject(1,”2010-01-01″);

           st.setObject(2,”2010-12-31″);

           st.setFetchSize(1000);// Set the number of records retrieved in each batch

           st.execute();

           ResultSet rs = st.getResultSet();

           while (rs.next()) {

                                     ……

                            }

Database computing

You can handle the database computing involved in the computation in esProc and return the result all at once through esProc JDBC, without having to integrating the database in Java. For example, you can use the following code to align the database table emp with sOrder.txt:

See details about accessing database in esProc from How esProc Assists Writing SQL Queries.

And learn more about the integration of esProc script by Java in esProc Integration & Application: Java Invocation.