esProc Helps Process Structured Texts in Java –Non-Single Row Records

esProc can help Java deal with various computations in processing structured texts. But in the case of non-single row records, it is necessary to preprocess the data before esProc can perform computations on it.

Let's look at this through an example. The text file Social.txt is the access records of a website, in which every three rows corresponds to a record. The records should be rearranged first before other computations can be performed. They should be imported in the form of（UserID, Time, IP, URL and Location）for future use or for storing in files. Note that the column separator should be tab and row separator should be the carriage return. The first rows of data are as follows:

For each record of three rows in the text file, the first row of data of IP, URL and Time, and the third row of data of UserIDand Location are useful, but the second row of data is useless. Thus the first record, for example, should be (UserID, Time, IP, URL, Location)=(47356, 2013-04-01 21:14:44, 10.10.10.143, /p/pt301/index.jsp, Chicago). The steps of pre-process of the records are as follows:

Code in esProc:

Code interpretation:

A1：file("E:\\Social.txt").import@t()

This line of code is to import the whole text file to the table sequence object, as shown below:

As can be seen from above, A1 has only one column with "_1" being the default name. Each row of the text file corresponds to a piece of data in "_1".

A2：A1.select(#%3==1)

This line of code is to select the first of the three rows by the row numbers, such as the 1^st, 4^th, 7^th and 10^th rows. The sign # represents row number and the sign % represents getting the remainder. select function can make query on the table sequence by field name or by row numbers. The result after the code is executed is as follows:

B2：A1.select(#%3==0)

Similarly, this line of code is to select the third of the three rows, such as the 3^rd, 6^th, 9^th and 12^th rows. The result is as follows:

After the three steps, the first and third rows of each record have been stored respectively in table sequence A2 and B2. The two sequences have the same number of rows with corresponding row numbers. But at this point, the records haven't been split yet.

L1=A2.(_1.array("\t"))

This is to split the first row of each record to form a string sequence. The sequence of string sequences will be named L1. "\t" means thattab is used as the separator. The result is as follows:

As shown in the above figure, each member of L1 corresponds to a string sequence. Click the hyperlinks in blue and members of each string sequence will be shown. The third row of each record will be processed in the same way. The code for this is =L3=B2.(_1.array("\t"))and the resulting sequence of string sequences will be named L3, as shown below:

Now we'll join the needed fields in L1 and L3 together to form a new table sequence:

pjoin(L1,L3).new(_2(1):UserID, _1(2):Time, _1(1):IP, _1(4):URL, _2(3):Location)

The result is as follows:

pjoin function is used to concatenate L1 and L3 according to the row numbers. After the concatenation L1's name is _1 and L3's name is _2 by default. new function is used to generate a new table sequence. _2(1):UserID represents getting the first member of each member in L3, joining them together and naming the new sequence and field "UserID". In the same way, we can know the meaning of other parameters of the new function.

A4 shows the selected useful records. To store these records in a file, we can use the following code:=file("E: \\result.txt").export@t(A4), in which option @t means storing the field name in the first row of the file.

        

Or we can perform structured data computing on A4 as we did before. That is, grouping and summarizing the data by regions, computing the page views of each region, selecting those regions where the page views are above a certain number (say a million) and then passing the result to JDBC. The code for the computation is as follows:

=A4.groups(Location;count(~):pv)

This line of code is to group and summarize the data by regions and compute the number of page views of each region.

=A5.select(pv>=@arg)    //@arg is an input parameter，like 1000000。

This line of code is to filter data by the number of page views and get the regions where the page views are above a certain number.

Tips: groups function can group and summarize multiple fields; select function can perform filtering according to multiple conditions.

result A5，

This line of code is to pass A5 to JDBC to be called by Java program.

In the following code, the esProc script is called by Java through JDBC.

         //Create a connection using esProcjdbc

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

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

         //Call esProc script whose name is test

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

         //Set the parameters. Assume the number of page views is above 1,000,000, but actually the value should be a variable in JAVA.

         st.setObject(1,"1000000 ")//

         st.execute();//Execute esProc stored procedure

         ResultSet set = st.getResultSet();   //Get the result set

Sometimes a file featuring non-single row records has too many bytes to be wholly processed in the memory. To process this kind of files in Java, we have to import the data while computing it and storing the result in contemporary files, which makes the code rather complicated. But with the cursor object in esProc, these big files can be cleverly processed segmentally.

Big files processing in esProc:

First develop the main program main.dfx:

In the above code, pcursor calls a subprogram to return the cursor generated by the real records. A2 and A3 only execute the grouping, summarizing and filtering. Note that the computed result of A1 is cursors instead of in-memory data. Only while groups function is executed will the cursors be imported segmentally to the memory automatically and computed.

The subprogram sub.dfx is to process the file by loop. 3*N rows will be imported each time and N records are created and returned. pcursor will receive the computed result of each batch of data in order and transform it to a cursor. Note that the value of N should be kept in an appropriate range in case of a memory overflow if it is too big or a poor performance if it is too small. The detailed code is as follows:

A1: =file("E:\\Social.txt").cursor()

Similar to the usage of import function, the cursor function in the above code is used to open file cursors. As it won't really read the data to the memory, it can be used in processing big files. 

A2-C6：Process the file by loop. for A1,3*10000 represents importing 30,000 rows data to the memory at a time. As the imported data is the same as that imported from small files, the code is also written in a same way. 

esProc Helps Process Structured Text in Java – Grouping and Summarizing

Following problems will arise if you perform conditional filtering on text files in Java alone: 

1. The text file is not a database, so it cannot be accessed by SQL. The code needs to be modified if the expression of grouping and summarizing is changed. Besides, if you want a flexible expression, you have to self-program the dynamic expression parsing and evaluating, resulting in a great amount of programming work.

2. The grouping result produced during traversing will be recorded. If the result is small in size, it can be stored directly in the memory; if the size of the result is too big, an intermediate result will have to be stored as cache files which should be merged later. The process will be quite complicated.

These problems can be solved with ready-made class library by introducing esProc to the programming in Java. Now let's look at in detail how this will happen.

The text file employee.txt has the employee information. It is required to group by DEPT, count the employees andsum up the total amount of their salary in each group.

The format of text file employee.txtis as follows:

EID   NAME       SURNAME        GENDER  STATE        BIRTHDAY        HIREDATE         DEPT         SALARY

1       Rebecca   Moore      F       California 1974-11-20       2005-03-11       R&D          7000

2       Ashley      Wilson      F       New York 1980-07-19       2008-03-16       Finance    11000

3       Rachel      Johnson   F       New Mexico     1970-12-17       2010-12-01       Sales         9000

4       Emily         Smith        F       Texas        1985-03-07       2006-08-15       HR    7000

5       Ashley      Smith        F       Texas        1975-05-13       2004-07-30       R&D          16000

6       Matthew Johnson   M     California 1984-07-07       2005-07-07       Sales         11000

7       Alexis        Smith        F       Illinois       1972-08-16       2002-08-16       Sales         9000

8       Megan     Wilson      F       California 1979-04-19       1984-04-19       Marketing        11000

9       Victoria    Davis        F       Texas        1983-12-07       2009-12-07       HR    3000

10     Ryan         Johnson   M     Pennsylvania    1976-03-12       2006-03-12       R&D          13000

11     Jacob        Moore      M     Texas        1974-12-16       2004-12-16       Sales         12000

12     Jessica     Davis        F       New York 1980-09-11       2008-09-11       Sales         7000

13     Daniel       Davis        M     Florida      1982-05-14       2010-05-14       Finance    10000

…

Implementation approach: call esProc script with Java, import and compute the data, then return the result in the form of ResultSet to Java. Because esProc supports dynamic expression parsing and evaluating, it enables Java to process data from the text file as flexibly as SQL does.

For example, you are required to group by DEPT, count the employees and sum up the total amount of their salary in each group. esProc can usean input parameter "groupBy" as the dynamic grouping and summarizing condition, which is shown below: 

The value of "groupBy" is DEPT:dept;count(~):count,sum(SALARY):salary. And the code written in esProc is as follows:

A1: Define a file object and import the data, with the first row being the title. tab is used as the field separator by default. esProc's IDE can display the imported data visually, as shown in the right part of the above figure.

A2: Group and summarize according to specified fields, using macro to realize parsing the expression dynamically. The "groupBy" in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${…}, then replace ${…} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is=A1.groups(DEPT:dept;count(~):count,sum(SALARY):salary).

A3: Return the eligible result set to the external program.

You just need to modify the parameter –"groupBy" when grouping fields are changed. For example, you are required to group by DEPT and GENDER, count the employees and sum up the total amount of salary in each group. The value of "groupBy" can be written as DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary.

The simple summarizing on all data can be regarded as a special case of grouping and summarizing operation. For example, when counting the number of employees and summating the total amount of salary, the value of parameter "groupBy" can be written as ;count(~):count,sum(SALARY):salary. That the parameter part for grouping is omitted means all data is put into one group. The advantage by doing so is that multiple summarizing results of these data can be computed by traversing them once.

The code of calling this piece of code (which is saved as test.dfx) in Java with esProc JDBC is as follows:

 // create a connection using esProc JDBC

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

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

//call the program in esProc (the stored procedure); test is the name of file dfx

com.esproc.jdbc.InternalCStatementst;

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

//set the parameters

st.setObject(1,"DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary");// the parameters are the dynamic grouping and summarizing fields

//execute the esProc stored procedure

st.execute();

//get the result set

ResultSet set = st.getResultSet();

If the script is simple, the code can be written directly into the program in Java that calls the esProc JDBC. It won’t be necessary to write a special script file (test.dfx):

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

          ResultSet set=st.executeQuery("=file(\"D:/employee.txt\").cursor@t().groups(DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary)");

This piece of code in Java calls a line of code in esProc script directly, that is, get the data from the text file and return the result set to set– the object of ResultSet.

         

If the result set of grouping is still too big to be entirely loaded to the memory, groupx statement will return the grouping result using file cursor. Thus the code written in esProc will be modified like this:

groups function puts the grouping and summarizing result completely in the memory.groupx will write the result into temporary files if the grouping and summarizing result is beyond the boundary of buffer rows, redistribute the memory, and then merge the temporary files. Here the parameter 1000000 refers to buffer rows. The principle of assigning value to it is to make the best of the memory, trying to reduce the number of temporary files as far as possible. The number of temporary files is related to the size of both the physical memory and the record, and should be evaluated during programming. Generally, the recommended number is between magnitudes of several hundred thousand to a magnitude of one million.

Though cell A3 returns a cursor, instead of a result set, to Java, it is no need to modify the calling program of Java. esProc will automatically fetch the data corresponding to the cursor while Java is traversing the data with ResultSet.

This piece of program can be further improved to support filtering before and after the grouping. Now the role of the program is similar to that of where and having in SQL. For example, the statistical object becomes female employees (GENDER=="F"), and it is required to retain only the departments where the number of female employees is greater than ten after grouping and summarizing operation. The code is as follows:

Cellset parameters are made absent here for easy understanding, yet the code is the same as that in the above:A2.groupx(${groupBy}). The parameter of select function can be written as the macro which will be passed from Java program. 

esProc Helps Process Structured Texts in Java – Alignment Join

The join statements of the database can be used conveniently to perform the operation of alignment join. But sometimes the data is stored in the text files, and to compute it in Java alone we need to write a large number of loop statements. This makes the code cumbersome. Using esProc to help with programming in Java can solve the problem easily and quickly. Let’s look at how this works through an example.

The text file emp.txt contains employee information, except that in which EId is 1. Another text file sOrder.txt contains information of sales orders in which field SellerId corresponds to field EId inemp and from which the information whose SellerId is 2 is excluded. Part of the original data is listed below:

emp.txt：

sOrder.txt： 

It is required to join the three fields: Name, Dept and Gender, in emp to sOrder in alignment and output the computed result to a new file. The expected result is as follows: 

Code written in esProc: 

In cells A1 and A2 respectively, data is imported from the two text files and stored in two variables: emp and sOrder. Here import function uses tab as the column separator by default. Option @t represents the first row will be imported as the field names. Because only some of the fields in emp.txt are needed, the code in A1 uses the names of these desired fields as parameters. After execution, values of emp and sOrder are as follows: 

In the code in A3: =join@1(sOrder:s,SellerId;emp:e,EId), join function performs the operation of alignment join and changes the names of the two tables to s and e respectively. Option @1 represents the left join which is in line with the requirement of the example: join emp to sOrder in alignment. The computed result is as follows:

Click the numbers in blue and we can see the detailed information, as shown below: 

esProc can also be used to realize the right join which only requires exchanging positions of data in alignment. For example, to align sOrder according toemp, we just need to exchange their positions in the code, that is, =join@1(emp:e,EId;sOrder:s,SellerId). The computed result is as follows: 

It is also easy to realize the full join using option @f. The code is join@f(sOrder:s,SellerId;emp:e,EId). The computed result is as follows: 

There are altogether four operations of alignment join: left join, right join, full join and inner join. By default, join function is used to execute the inner join, the code is =join(sOrder:s,SellerId;emp:e,EId). The computed result is as follows: 

Let's get back to the example. The code in A4: =A3.new(s.OrderID, s.Client, s.SellerId, s.Amount, s.OrderDate,e.Name, e.Dept, e.Gender), is for getting the desired fields from table e and creating a new structured two-dimensional table. The computed result is as follows: 

Now the alignment is done and data needs to be exported to a new file. The code for this is =file("E: \\result.txt").export@t(A4). In export function, tab is by default the column separator and option @t represents the field names are exported to the first row. Open result.txt and we can see information as follows: 

The script in the above has finished exporting all aligned data to the new file, what we will do next is to call the script in Java.

         //create a connection using esProcjdbc

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

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

         //call esProc script; the name of the script file is test

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

         // execute esProc stored procedure

         st.execute();

By executing the above Java code, emp will be joined to sOrderin alignment and the resultwill be output to file result.txt.

If the requirement is changed to this: query data in sOrder according to dynamic periods of time, execute the same operation of alignment join and return the result directly to Java. To complete the task esProc needs to define two parameters: begin and end, to represent starting time and ending time respectively. The esProc code is as follows: 

The code in red has been modified.

        

A2：Filter sOrder again using select function according to the starting and ending time passed from Java, that is, @begin and @end.

A5：Output the computed result in A4 to JDBC interface.

And Java code should be modified too to pass parameters to esProc code and get thefinal result. The modified code is as follows:

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

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

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

         st.setObject(1,startTime);

         st.setObject(2,endTime);

         st.execute();

         ResultSet set = st.getResultSet();

esProc Helps Process Structured Text in Java–Conditional Filtering

Following problems will arise if you perform conditional filtering on text files in Java alone: 

1. The text file is not a database,soit cannot be accessed by SQL. The code needs to be modified if filtering conditions are changed. Besides, if you want a flexible conditional filtering as that in SQL, you have to self-program the dynamic expression parsing and evaluating, resulting in a great amount of programming work.

2. Stepwise loading is required for the big files that cannot be loaded into the memory all at once. If the performance must be taken into account, you have to deal with some complicated programming like the management of file buffer and line-splitting computing.

But if esProc is used to help with Java programming, these problems can be solved without self-programmed code. The following example will teach you how to do this in detail.

The text file employee.txt has the employee information. You are required to fetch the data and select from them the female employees who were born on and after January 1, 1981.

The text fileemployee.txtis in a format as follows:

EID   NAME       SURNAME        GENDER  STATE        BIRTHDAY        HIREDATE         DEPT         SALARY

1       Rebecca   Moore      F       California 1974-11-20       2005-03-11       R&D          7000

2       Ashley      Wilson      F       New York 1980-07-19       2008-03-16       Finance    11000

3       Rachel      Johnson   F       New Mexico     1970-12-17       2010-12-01       Sales         9000

4       Emily         Smith        F       Texas        1985-03-07       2006-08-15       HR    7000

5       Ashley      Smith        F       Texas        1975-05-13       2004-07-30       R&D          16000

6       Matthew Johnson   M     California 1984-07-07       2005-07-07       Sales         11000

7       Alexis        Smith        F       Illinois       1972-08-16       2002-08-16       Sales         9000

8       Megan     Wilson      F       California 1979-04-19       1984-04-19       Marketing        11000

9       Victoria    Davis        F       Texas        1983-12-07       2009-12-07       HR    3000

10     Ryan         Johnson   M     Pennsylvania    1976-03-12       2006-03-12       R&D          13000

11     Jacob        Moore      M     Texas        1974-12-16       2004-12-16       Sales         12000

12     Jessica     Davis        F       New York 1980-09-11       2008-09-11       Sales         7000

13     Daniel       Davis        M     Florida      1982-05-14       2010-05-14       Finance    10000

…

Implementation approach: call esProc script with Java, import and compute the data, then return the result in the form of ResultSet to Java. Because esProc supports dynamic expression parsing and evaluating, it enables Java to perform the conditional filtering as flexibly as SQL does.

For example, it is required to query the information of female employees who were born on and after January 1, 1981. In this case, esProc can use an input parameter "where" as the condition, which is shown below: 

 "where" is a string, its values is BIRTHDAY>=date(1981,1,1) && GENDER=="F".

The code written in esProc is as follows:

A1: Define a file object and import the data, with the first row being the title. tab is used as the field separator by default. esProc's IDE can display the imported data visually, as shown in the right part of the above figure.

A2: Perform the conditional filtering, using macro to realize parsing the expression dynamically. The "where" in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${…}, then replace ${…} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3: Return the eligible result set to the external program.

In esProc, when the filtering condition is changed, you just need to modify "where"– the parameter. For example, it is required to query the information of female employees who were born on and after January 1, 1981, or employees whose NAME+SURNAME is equivalent to "Rebecca Moore". The value of “where” can be written as BIRTHDAY>=date(1981,1,1) && GENDER=="F" || NAME+SURNAME=="Rebecca Moore". After the code is executed, the result set in A2 is as follows:

Call this piece of code in Java with esProc JDBC and get the result. Detailed code is as follows (save the above program in esProc as test.dfx):

          //create a connection withesProc JDBC

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

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

//call the program in esProc (the stored procedure); test is the name of file dfx

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

//set the parameters

st.setObject(1," BIRTHDAY>=date(1981,1,1) && GENDER==\"F\" ||NAME+SURNAME==\"RebeccaMoore\"");//the parameters are the dynamic filtering conditions

//execute the esProcstored procedure

st.execute();

//get the result set, which is the eligible set of employees

ResultSet set = st.getResultSet();

If the script is simple, the code can be written directly into the program in Java that calls the esProc JDBC. It won’t benecessary to write a special script file (test.dfx):

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

ResultSet set=st.executeQuery("=file(\"D:/employee.txt\").import@t().select(BIRTHDAY>=date(1981,1,1)&&GENDER==\"F\" || NAME+SURNAME==\"RebeccaMoore\")");

This piece of code in Java calls a line of code in esProc script directly, that is, get the data from the text file, perform conditional filtering and return the result set to set– the object of ResultSet.

It is assumed, in the above approach, that the file is small enough to be loaded to the memory all together. In reality, there may be huge files that cannot be loaded all together or the situation where it is believed that it is unnecessary to increase memory usage even if the file is not huge. In these occasions, file cursor can be used to handle the operation, thus the program in esProc can be modified in this way:

A1: Define a file cursor object, with the first row being the title and tab being the field separator by default.

A2: Perform conditional filtering on the cursor, using macro to realize parsing the expression dynamically. The "where" in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${…}, then replace ${…} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3: Return the cursor.

Despite a cursor returned to Java by esProc, it is no need to modify the calling program of Java. esProc will automatically fetch the data corresponding to the cursor while Java is traversing the data with ResultSet.

If it is needed to write the filtered data to another file, instead of returning them to the main program, you just modify the expression in A3 into =file("D:/employee_group.txt").export@t(A2). esProc will write out the data of the cursor to a file. 

esProc Helps Process Structured Texts in Java –Expression Computing

As Java doesn't directly support dynamically parsing expressions in the text files, the computation can only be realized by splitting strings manually and then writing a recursive program. The whole process requires writing a great amount of code, is complicated and the code is difficult to maintain. With the assistance of esProc, we can develop program for the computation in Java without writing code manually. Let's look at how esProc works through an example.

Here is a text file formula.txt with tab being the separator and the first row being the column names. It has three columns: No, type and exp. Column exp consists of formulas. It is required to parse the formulas in column exp dynamically, append the results to the column and rename it as value. The first rows of data of formula.txt are as follows:

The esProc script is as follows:

A1：=file("E:\\ formula.txt").import@t(). import function is used to import the text file. Function option @t means importing the first row as the column names. The imported data will be stored in cell A1 as follows:

A2：=A1.derive(eval(exp):value).derive function is used to add a new column - value - to A1. The value of the column is eval(exp). eval function is to parse the strings dynamically. For example, the computed result of eval(“1+1”)is 2. Since column exp consists of multiple strings, the computed result of eval(exp)will be multiple too, as shown below:

Now that the dynamic expression has been parsed, A2 will be written to a text file. The code is: A3：=file("E:\\ result.txt").export@t(A2).

In the above script, export function is used to write the data in A2 to the file result.txt. Function option @t means writing the column names to the first row. The following content will be shown when the file is opened:

A4：result A2. This line of script is to return the data in A2 to Java. To get the result, Java will only call the esProc script through JDBC. The code for this last step is as follows:

         //create a connection using esProcjdbc

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

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

         //call esProc file script, whose name is test

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

         st.execute();//execute esProc stored procedure

         ResultSet set = st.getResultSet();   // get the result set

esProc Helps Process Structured Texts in Java – Handle Big Files in Groups

There is a type of text files that they are too big to be entirely loaded into the memory, yet as the data have been sorted by a certain column and if they are imported in groups according to this column, they can be all put into the memory for computing. These text files include the call detail record of a telecom company, statistics of visitors on a website, information of members of a shopping mall, etc.

A great deal of complicated code, which is difficult to maintain, is required if Java is used to realize the operation. But with the assistance of esProc in programming, it is easier for Java to deal with this kind of problems. Let's look at how esProc works through an example.

The text file sOrder.txt has a lot of information of orders, with tab being the separator and the first row being the column names. The data have been sorted by SellerID. Now it is required to import a group of data with the same SellerID at a time, and process each group of data in the same way.

Part of the data of the file sOrder.txt is as follows:

The esProc script is as follows:

A1: cursor function opens the file as a cursor. tab is used as the separator by default and function option @t means the first row will be imported as the column names. If only the first four columns are imported and comma is used as the separator, the code should be written as cursor@t(OrderID, Client, SellerId, Amount; ",").

A2: for A1 ;SellerId. The code is to fetch data by loop from cursor A1. By fetching a group of data with the same SellerID each time, all the data will be stored in the memory. Please note the for statement here. for cs,n - a way of code writing in esProc – means fetching n records from cursor cs at a time. for cs;x means fetching a group of records from cursor cs at a time, with the same x field in each group and the data having been sorted by x in advance. In this example the data have been sorted, otherwise sortx function can be used to sort them.

Besides being a field, the x in the statement for cs;x can also be an expression. This means rows of data would be fetched each time until expression x changes, such as the line of code for A14 ;floor(SellerId/10), which groups together the data whose SellerID is from 0 to 9 and those whose SellerID is from 10 to 19, and in which floor function means getting the integer part. If each SellerID hasn't many corresponding records, the above statement can fetch more data at once. Thus the computational performance will be increased.

B3-C3: This is the for statement's loop body which performs the same operation of data processing on each group. The process of data processing is not our focus here. We'll design a case like this: Compute the sales amount of each salesperson (SellerID), and append the sales record of the salesperson to the file result.txt if the sales amount is greater than 10,000.

One thing worth noting is that the working scope of for statements can be shown only by the indentation without being marked by braces or begin/end. Moreover, a loop variable can be represented by the name of the cell where for is placed. In this example, that means A2 is the records corresponding to the current SellerID, and A2.sum(Amount) represents performing summing up on the Amount field of this group of records. export function is used to export a group of data to a file and function option @a means the exportation will be realized by appending.

The computed results are saved in file result.txt. Some of the data are as follows:

The above esProc script has done all the data processing. Then the rest of the work is to integrate the script with Java program through JDBC. Detailed code in Java is as follows:

         // create a connection using eProc JDBC

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

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

         // call esProc script, whose name is test

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

         st.execute();//execute the esProc stored procedure

Note: This case doesn't require returning the computed result to Java program. But sometimes we’ll append the computed result to a cell (say cell B2) and return it to Java program for further processing. In that case, a line of code should be added to the esProc script, like entering result B2 in cell A4, which means outputting the data in B2 to JDBC interface.

Thus the following Java program needs another line of code too to receive the returned result. The code will be written as ResultSet set = st.getResultSet(); after the execute.