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