Examples of esProc as used in set Operation

Set operations are frequently used in statistical analysis with structured data, For example, listing all students who has published papers; listing all staff who has participated in all previous training; selecting qualified students in examination for re-exam and so on. Within esProc, application of set is everywhere. The most commonly used sequence and sequence table data types are all sets. Therefore, better understanding and using of set helps to complete data computation in a more reasonable and faster way.

For example, the table below contains some sales data:

Now we need to select customers who entered Top 20 revenue contributors (Top 20 customers) in every month of 2013. To solve this problem we can first select all sales data for 2013, group them and to get the statistics for each month. Then we can do a loop to select the Top 20 customers for each month. The intersection of the Top 20 lists for all 12 months will contain the name of customers we wanted. Such complex problems are too difficult to be handled by SQL or stored procedures.

With esProc, we can split complex problems into different steps, and do the computations step by step to get the final result. First, from the sales data we can retrieve those for 2013, and group them by month:

esProc's grouping of data is real grouping, which actually separates data into different groups according to the criterion. This is different from SQL, in which the "group by" command can only return the aggregated result of a grouping. After grouping, the data in A3 is as following:

Before grouping, all data will be sorted automatically. Each group is a set of sales records. For example, the data for March is as following:

To know the total sales revenue for each customer in every month, we need to further split the data by customers. In esProc, we only need to do loop on data for each month, and group them by customers respectively. We can use A.(x) to to do loop on set members, without the need to code for loop.

After further grouping, the monthly data in A4 is a set of sets：

Now, the data for March is as following:

We can see that each group in data for March is the transaction data for certain customer.

The set used in esProc is different from that in mathematical concepts. They are ordered setsand therefore can meet the statistical needs of sorting and selection by position, etc. Then we can find the Top 20 customers for each month:

In A5, do loop on the data for each month to get the Top 20 customers for each month. And in A6, list the names and monthly revenues of these customers. The computation result in A6 is as following:

Finally, we can further solve the problem:

Generate the name lists of Top 20 customers in A7 for each month. And finally in A8 we can find the intersection of the Top 20 lists for each month as following:

Code Examples of Processing json Data with esProc

esProc can process json data. Here we'll introduce some of the applications through examples. 1. Analyzing and generating json data with esProc; 2. Data-interchange between esProc and application program through json; 3.Reading json file data in esProc.

A. Analyzing and generating json data with esProc

Generally speaking, json is a format used by webpage js program and Java's server-side program (such as servlet)to interchange data. While data access between Java's server-side program and the databases adopts SQL result set format. esProc can act as an intermediary in the data computation and data-interchange between the two formats.

In this example, we use esProc to query detailed information of a group of designated employees. Both data input and output will adopt json format. Table employee of database demo contains all information of the employees:

esProc receives an EID list of json format and returns corresponding detailed information of employees in json format. The code is as follows:

1. esProc program test.dfx receives a parameter: jsonEID.

2. esProc completes json analysis, data processing and generates results in json format:

A1: Connect to database demo.

A2: Retrieve data from table employee.

A3: Use import@j function to parse the inputting jsonEID parameter (EID list in json format) and generate a table sequence containing only one field EID.

A4: Use align function to get from users data the employee information designated by A3.

A5: Convert employee information into json strings. 

A6: Return employee information of json format.

B.Interchanging json data between esProc and Java application

In the above example, esProc program is saved as test.dfx file to be called by Java application. Steps for calling the file are as follows:

1.Deploy esProc in Java application.
See esProc Tutorial for detail.

2.Call test.dfx in Java application.

Code example is as follows:

public void testDataServer(){

                   Connection con = null;

                   com.esproc.jdbc.InternalCStatementst;

                   try{

                            //Usersidlist injson format can be transmitted from browser-side to the program and converted into strings for use. Here process of receiving json data is omitted and value is assigned directly

                            String jsonEid="[{EID:8},{EID:32},{EID:44}]";

                            //Create a connection

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

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

                            //Call stored procedure. test is the file name of dfx

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

                            //Set parameters

                            st.setObject(1,jsonEid);

                            //Execute stored procedure

                            st.execute();

                            //Get result set

                            ResultSet set = st.getResultSet();

                            String jsonEmployee=null;

                            if (set.next()) jsonEmployee=set.getString(1);

                            //After getting detailed user information injson format, convert it into json objects and return them to browser-side. How to usejsonEmployee is omitted here

                   }

                   catch(Exception e){

                            System.out.println(e);

                   }

                   finally{

                            //Close the connection

                            if (con!=null) {

                                     try {

                                               con.close();

                                     }

                                     catch(Exception e) {

                                               System.out.println(e);

                                     }

                            }

                   }

}

C. Reading and processing jsonfile data inesProc

JSON file test.json contains information including class, serial number, names, subjects,scores, etc. Format is as follows:

[

    {

        "class": "Class one",

        "id": 1,

        "name": "Emily",

        "subject": "English",

        "score": 84

    },

    {

        "class": "Class one",

        "id": 1,

        "name": "Emily",

        "subject": "Math",

        "score": 77

    },

    ......

    {

        "class": "Class one",

        "id": 7,

        "name": "Nicholas",

        "subject": "PE",

        "score": 60

    }

]

It is convenient for esProc to perform the reading and computation of JSON data. After that the result is submitted to Java application in the format of JDBC result set. Steps are as follows:

1. Developing esProc script

Use esProc editor to develop script (fromJSON.dfx), read the jsonfile, analyze it and complete the computation:

A1: Use read() to read json file in string format;

A2: Use import@j() function to parse the json file into a table sequence;

A3: Group students ID and summarize the total scores in A4;

A5: Sort by total scores in descending order and return result set through result in A7

2. Java application callsfromJSON.dfx to present result.

Steps are omitted here for they are almost the same as those in the above example.

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. 

Examples of Database Transaction Management with esProc

esProc can write to databases and manage database transactions. Here we'll look at the programming method of rollbacks and controlling transaction submission, etc. 

A．Submit transactions automatically

esProc can conveniently execute operations like insert, delete and update. The simplest code is:

 

In the above figure, insert, update and delete are respectively executed from A2 to A4. Execution of each SQL statement will be submitted automatically. Note that:

1. That three SQL statements are submitted three times is too frequent operations for a database. 

2. There exists no transaction relation between the three SQL statements. So, if the execution of one SQL statement fails, the previous SQL statement will remain unaffected. 

The following examples are to introduce how to submit transactions in batches and how to compose a transaction with multiple SQL statements in a table sequence. 

B．Submit transactions in batches

Import students' information from students.txt to update table students1 in the database. Since there are a lot of records to be modified, using method of submitting transactions in batches is more reasonable. 

 

A1：Define a file object in which students’ information is stored. 

A2：Import file content. 

A3：Use students’ information in A2 to update table students1in batches. Submitting SQL in batches can avoid accessing the database too frequently. Meanwhile, this can ensure consistency of the data for the submission could succeed or fail simultaneously. 

C．Program control transactions

Now we’ll add a new student. The student’s id should be modified to 9 after data are inserted. In order to ensure consistency of the data, submission must be executed after the insertion and modification are proved to be successful. Otherwise rollback should be executed.

 

A1：Connect to the database. Note that connect function uses option @e and the subsequent code will return error message when something wrong happens. If the option is not used, the database will terminate esProc program directly when errors occur.  

A2：Execute the insert SQL statement. Note that execute function uses option @k, meaning the transaction will not automatically submitted after it is executed. If the option is not used, the insert SQL statement will be submitted immediately. 

A3：Get the result of last operation in the database, i.e., the insertstatement. If err variable is zero, the execution is successful; otherwise, err is the error code. 

A4：Judging whether err variable, the execution result, is zero. If the answer is yes, the last operation of the insert statement is successful and modification in B4 can be executed.

C4：Get execution result of the update SQL. 

A5：Make judgment over err variable. If it is zero, submit the database; otherwise execute rollback.  

A6：Close database connection.