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. 

A Code Example of Computing Link Relative Ratio and Year-on-year Comparison with esProc

Link relative ratio refers to comparison between the current data and data of the previous period. The interval is usually one month. For example, divide sales amount of April by that of March, and you get the link relative ratio of April. Hour, day, week and quarter can also be used as the time interval. Year-on-year comparison is the comparison between the current data and data of the corresponding period of the previous year. For example, divide sales amount of April 2014 by that of April 2013. In business, data of multiple periods is usually computed to find the variation trend.
Seeking link relative ratio and year-on-year comparison is common inter-row and inter-group computations, which are easy to be performed with esProc. The following example is used to illustrate the computations.

Case description

Compute link relative ratio and year-on-year comparison of each month’s sales amount within the designated period. The data comes from table order. Some of the data is shown below:

esProc:
   A1=esProc.query("select * from sales3 where OrderDate>=? and       OrderDate<=?",begin,end)
   A2=A1.groups(year(OrderDate):y,month(OrderDate):m;sum(Amount):mAmount)
   A3=A2.derive(mAmount/mAmount[-1]:lrr)
   A4=A3.sort(m)
   A5=A4.derive(if(m==m[-1],mAmount/mAmount[-1],null):yoy)

Code interpretation:
A1: Query in the database according to periods. begin and end are external parameters. Such as, begin="2011-01-01 00:00:00", end="2014-07-08 00:00:00"(i.e. the date of today which can be obtained through now() function). Some of the query results are as follows:
 

A2: Group orders by year and month, then summarize and seek each month’s sales amount. Some of the computed results are as follows:
 

A3: Add a new field Irr, i.e, the link relative ratio on a month-on-month basis. The code is mAmount/mAmount[-1], in which mAmount represents sales amount of the current month, and mAmount[-1] represents that of the previous month. Note that the initial month’s link relative ratio is empty (i.e. January 2011). Computed results are:
 

A4: Sort A3 by month and year to compute year-on-year comparison. Complete code should be: =A3.sort(m,y). Since A3 is originally sorted by the year, so we just need to sort by the month, the code is: A3.sort(m), which has a higher performance. Some of the computed results are:
 

A5: A5: Add a new field yoy, i.e., theyear-on-year comparison of monthly sales amount. The code is: if(m==m[-1],mAmount/mAmount[-1],null), meaning that the computation of year-on-year comparison is only performed over the corresponding months. Note that the year-on-year comparison for months of the initial year (i.e. the year 2011) is always. Some of the computed results are:  
 

A row of code, A6=A5.sort(y:-1,m), can be added to make observation easier. That is, sort A5 in descending year order and ascending month order. Note that the data comes to an end in July 2014. Results are shown below:
 

Code Examples of Calling Database Stored Procedures in esProc

esProc can call database stored procedures conveniently. This article will illustrate in detail the program writing with examples.

A.Call stored procedures without return values

 Take oracle stored procedure as an example, the stored procedure has only one input parameter, and no output parameter: 

    create or replace procedure pro1

    (pid IN VARCHAR)

    as

    begin

    insert into emp values(pid,'mike');

    update emp set name='rose' where id=pid;

    commit;

    end;

execute function or proc function can be used in esProc to call this stored procedure:

A1：Connect to the database.

A2：Call stored procedure, input parameter value is 4.

proc function is mainly used to call stored procedures which return values and result sets, or to call stored procedure pro1 which does not return parameters. 

A1：Connect to the database.

A2：Call stored procedure pro1. The code 4:0:"i": after comma describes and defines an input parameter, in which 4 is the input parameter value, 0 represents that the type of input parameter is automatically identified by esProc, and "i" represents input type. If the parameter type need to be designated manually, the code may be written as 4:1:"i":, in which the 1 in the middle represents Integer(int). For more information about parameter types supported by esProc, please see appendix: Definition of Parameter Types.

B. Call stored procedures which return a single value

The stored procedure below will return one parameter value, so proc function, instead of execute function, will be used.

    create or replace procedure testb

    (para1 in varchar2,para2 out varchar2)  

    as

    begin

    select name into para2 from emp where id= para1;

    end testb;

The code for calling this stored procedure by esProc is:

A1：Connect to the database.
A2：proc function is used to call stored procedure testb. Here two parameters are used: one is 1:0:"i" representing that the value is 1 and the input parameter is automatically identified; the other is 11:"o":name in which 11 represents string type (see appendix Definition of Parameter Types for more), "o" represents type of output parameter and name defines an esProc variable to receive return values for the output parameter.
A3：Assign output value of stored procedure in A2 to cell A3 through name variable.

C. Call stored procedures which return a single result set
    Stored procedure RQ_TEST_CUR returns a single result set
    CREATE OR REPLACE PROCEDURE RQ_TEST_CUR
    (
V_TEMP OUT TYPE.RQ_REF_CURSOR,
PID IN VARCHAR
    )
    AS
    BEGIN
OPEN V_TEMP FOR SELECT * FROM TEST WHERE ID =PID;
    END RQ_TEST_CUR;

The stored procedure has an input parameter and returns a result set. The code for calling the stored procedure in esProc is as follows:

Cell A2 uses proc function to call the stored procedure: proc("{call RQ_TEST_CUR(?,?)}",:101:"o":table1,1:0:"i":). The following will explain the input parameters of proc function.
1） SQL strings
"{call RQ_TEST_CUR(?,?)}" represents calling the stored procedure’s name. The question marks represent SQL's parameters.
2） Output parameters
:101:"o":table1 defines an output parameter, in which 101 represents that its data type is cursor, and ”o” represents that it is an output parameter. Table1 defines a variable which is used to reference returned results. 1:0:"i": defines an input parameter, in which 1 is value of the input parameter, 0 represents that the parameter type is automatically identified by esProc.
Cell A3 uses output variable in A1 to reference the execution results of the stored procedure. Computed result is a table sequence containing two fields: id and name, which is the same as that in A2.

D. Call stored procedures which return multiple result sets
The code will begin with an oracle stored procedure which returns two result sets:
    create or replace procedure proAA
    (
out_var out sys_refcursor,
out_var2 out sys_refcursor
    )
    as
    begin
openout_var for select * from emp;
open out_var2 for select * from test;
    end;
The stored procedure returns result sets of two tables: emp and test. Call the stored procedure in esProc and the program for receiving the two result sets is as follows:

Cell A2 uses proc function to call the stored procedure: orac.proc("{call proAA(?,?)}",:101:"o":a,:101:"o":b), which returns two result sets (table sequences) to form a sequence, i.e., a set of table sequence. The sequence assigns value to A2. The following will explain the input parameters ofproc function.
1）SQL strings
"{call proAA(?,?)}" represents calling the stored procedure’s name. The question marks represent SQL's parameters.
2）Output parameter 1
:101:"o":a defines an output parameter, in which 101 represents that its data type is cursor, and "o" represents it is an output parameter. a defines a variable which is used to reference returned results.
3）Output parameter 2
:101:"o":b defines an output parameter, in which 101 represents that its data type is cursor, and "o" represents it is an output parameter. b defines a variable which is used to reference returned results.

Cell A3 returns cell A2’s first table sequence (table emp's result set)
Cell A4 and cell A5 use respectively the output variables a and b in A2 to obtain execution results of corresponding stored procedure. a corresponds to data in table emp and assigns value to A4; b corresponds to data in table test and assigns value to A5.

Appendix: Definition of Parameter Types
Values of type are:
public final static byte DT_DEFAULT = (byte) 0; //default, automatically identify
public final static byte DT_INT = (byte) 1;
public final static byte DT_LONG = (byte) 2;
public final static byte DT_SHORT = (byte) 3;
public final static byte DT_BIGINT = (byte) 4;
public final static byte DT_FLOAT = (byte) 5;
public final static byte DT_DOUBLE = (byte) 6;
public final static byte DT_DECIMAL = (byte) 7;
        public final static byte DT_DATE = (byte) 8;
public final static byte DT_TIME = (byte) 9;
public final static byte DT_DATETIME = (byte) 10;
public final static byte DT_STRING = (byte) 11;
public final static byte DT_BOOLEAN = (byte) 12;

public final static byte DT_INT_ARR = (byte) 51;
public final static byte DT_LONG_ARR = (byte) 52;
public final static byte DT_SHORT_ARR = (byte) 53;
public final static byte DT_BIGINT_ARR = (byte) 54;
public final static byte DT_FLOAT_ARR = (byte) 55;
public final static byte DT_DOUBLE_ARR = (byte) 56;
public final static byte DT_DECIMAL_ARR = (byte) 57;

public final static byte DT_DATE_ARR = (byte) 58;
public final static byte DT_TIME_ARR = (byte) 59;
public final static byte DT_DATETIME_ARR = (byte) 60;
public final static byte DT_STRING_ARR = (byte) 61;
public final static byte DT_BYTE_ARR = (byte) 62;
public final static byte DT_CURSOR = (byte) 101;
public final static byte DT_AUTOINCREMENT = (byte) 102;

Code Examples of Foreign Key Function in esProc

If field (or combination of fields) k is table M’s primary key, and k, at the same time, exists in table B, then k is regarded as the foreign key of B. The foreign key maintains a relationship between two tables and is one of the most important concepts in structured data computing. Through object references, esProc makes foreign key easy to function. The following examples aim to illustrate the operation.

Example 1 Create relationship between a referenced table and a referencing table 
order is the referencing table, emp is the referenced table, connect emp to order. Display emp'sName field, Gender field and Salary field and order's OrderID field and Amount field.
Note: Only tables emp and order are used here. Later, table dep is to be used in other examples. The relationship between the three tables over foreign key is shown as follows:

Data may originate from databases or texts. For example:
order=esProc.query("select OrderID,SellerId,Amount,OrderDate from sales")
emp=esProc.query("select EId,Name,Gender,Birthday,Dept,Salary from emp")
dep=esProc.query("select * from department")

esProc code:
A3=order.switch(SellerId, emp:EId)
A4=order.new(OrderID,Amount,SellerId.Name,SellerId.Gender,SellerId.Salary)
Computed results:
 

Code interpretation:
 A3: Replace records of SellerID in order with their counterparts in emp, create foreign key relationship between the two tables.
 A4: Get OrderID field and Amount field in order, and get Name, Gender and Salary field in emp through related references. We can see that, with object references, fields in emp can be accessed to directly from order. Thus complex and difficult join statements can be skipped.

Example 2: Query referencing table according to condition of referenced table 

Find orders signed by female sellers whose salary is greater than 10,000.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.select(SellerId.Salary>10000 && SellerId.Gender=="F")

Computed results:
 

Click the above blue hyperlinks and corresponding employee information will be shown:
 


Example 3: Group according to referenced table 
Compute sales amount of each department.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.groups(SellerId.Dept;sum(Amount))
Computed results:
 

Field names can be changed, like order.groups(SellerId.Dept:dt;sum(Amount):amt). The effect of name change is shown below:
 

Example 4: Complex association between multiple tables

For departments whose sales amount is greater than 50,000, find their managers’ names.
esProc code:
A3=order.switch(SellerId, emp:EId)
A4=dep.switch(Manager,emp:EId)
A5=emp.switch(Dept,dep:DeptNo)
A6=order.groups(SellerId.Dept:dt;sum(Amount):amt)
A7=A6.select(amt<=50000).(dt).(Manager).(Name)
Computed results:
 
Code interpretation:
A3, A4, A5：Create complete foreign key relationship.
A6: Compute sales amount of each department (See the above example). See below:
 
A7：Use object references to solve the question intuitionally. Expression A6.select(amt<=50000).(dt).(Manager).(Name) can be divided into four steps according to full stops. See below:
1. Find records whose sales amount is greater than 50,000 in A6.
2. Get records corresponding to dt field (in table dep)
3. Get records corresponding to Manager field (in table emp)
4. Get Name field.

Details are shown below:
A6.select(amt<=50000)  

.(dt)  
.(Manager)  

.(Name)