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. Servletaccessesemployee table in the database and saves employee information 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

…

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 ServletException, 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"); 

}

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 httpfile 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.

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's Name 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)